Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Santa Fe, New Mexico, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding latest entry for adjacent cells

    I'm downloading a price file from the company's software (which is entirely under-utilized, which is why I need to download and manipulate data) to a spreadsheet. All entries for all item numbers are shown in the download. I have the item numbers in one cell, the date of entry in the adjacent cell, and the price in yet another.

    The dates are formatted as such: 1000401=4/1/00, I don't know that this will make a difference. I can't seem to coax it into a date format.

    I want to extract the most recent entry for each item number prior to using the spreadsheet in an Access query to produce a catalog. I'm extremely rusty, and cannot figure this one out.

    Help! <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding latest entry for adjacent cells

    Try this:

    =DATE(LEFT(A1,LEN(A1)-4)+1900,LEFT(RIGHT(A1,4),2),RIGHT(A1,2))

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding latest entry for adjacent cells

    CPOD's suggestion to extract date values from the date strings generated in the price file will work, but it doesn't get you all the way there.

    Assuming that you insert the appropriate formula to convert these value to "excel dates" the next step depends on how many items you are dealing with, and how often you will have to do this.

    If this is a one-off, and if you are dealing with a small number of item numbers, it will be easiest to take this data file, sort it by item number, then by decending date (in a single sorting operation). Then simply run down the column. Every time the item number changes the first price listed is the most recent price - copy that line to a new location, and you will have the information you need.

    If there are a lot of item numbers (as I suspect), then this will be a tedious (and err-prone) process. It will likely be easier to use some Excel functions to extract the data directly.

    I would suggest sorting the data in decending date sequence, ignoring the item numbers for now. In the resulting file, the first-encountered instance of each item number will represent the most-recent price. The first step is to extract all unique item-numbers - this is easiest using the Excel <font color=blue>Data|Filter|Advanced Filter</font color=blue> commmand, with the column of item numbers (including a header) selected. In the Advanced Filter dialogue, specify 'copy to another location' and 'unique records only' while leaving the criteria entry blank and supplying an approporaite location for the filtered output (I used a cell a couple of rows below the raw data, but that's your choice). This will write all the unique values into the range including and below the selected output cell.

    We can identify the first instance of each item number using the Excel "Match" function, setting it to look for an exact match. The syntax is:<font color=blue>
    =match(Lookup_value,Lookup_array,Match_type)</font color=blue> where
    Lookup_value is the value you want to lookup (in this case, each of the unique item numbers);
    Lookup_Array is the range where you want to lool up the 'lookup_value' (in this case, the range of item numbers in the raw data), and;
    Match_type indicates whether you are looking for a value greater than or equal to, less than or equal to, or exactly equal to the Lookup_value. In this case, we want an exact match, and the value should be "0."
    The match function wil return the row within the Lookup_array that contains the first instance of the lookup_value specified. As noted above, that row will represent the most recent price, since the data has been sorted in decending date order.

    Assuming that the raw data is in the range C7:C50, and the unique value we want to search for is in cell C54, the formula is:<font color=red>
    =MATCH(C54,$C$7:$C$50,0)</font color=red>
    using the absolute references as shown allows us to copy the formula down as many rows as are required to match all of the unique values.

    Knowing the row in an array that contains a value we want allows us to use the Excel "Index" function to extract a another value in the same row. There are two sytaxes for the Index function, depending on whether you are dealing with multiple areas, but in this case the syntax we want is:<font color=blue>
    =Index(Array,Row_num,[column_num])</font color=blue> where
    Array is the range we want to extract data from (in this case the list of prices, in date order, irrespective of item number;
    Row_num is the row number we want to extract a value for (in this case, it will be equal to the value returned by the Match" function above, and;
    Col-Num is an otional value, used if the Array has more than one column - in this case it is not needed.

    The formula can use a reference to a match function in another column to identify which value to extract, or we can embed that function in the "Index" function itself. Assuming we do the latter, and that the price values are in the range D750 (and other references are as noted above) the formula we woudl use is:<font color=red>
    =INDEX($D$7:$D$50,MATCH(C54,$C$7:$C$50,0))</font color=red>
    Again - the use of absolute references allows us to copy the formula as required to extract the proce coresponding to each unique value.

    I have prepared and attached a small sample s/sheet that shows these formulas, and may be of assistance in understanding this description. Note that the raw data has already been sorted by date, although it started off as a series of random dates and prices. The item numbers are alphabetic for clarity.

    Finally, if you are doing this a number of times (say monthly or more often), it may be worthwhile to automate the process completely by writing a macro to extract the date information, sort the data, and insert the required formulas...
    Attached Files Attached Files

  4. #4
    FrankR
    Guest

    Re: Finding latest entry for adjacent cells

    Katy-
    Let's assume that your Item Numbers, Dates, & Prices are in columns A, B & C and that your data starts in row 2. Sort your data on Item Number and Date (make sure that the Date is sorted Ascending). In cell D2, enter the following formula
    =IF(A2=A3,"NOT LAST","LAST")
    Copy the formula down the entire column D. Now a simple filter on column D showing only the records containing LAST will give you the most recent price for each item. (The easiest way to set up your filter is to select Data|Filter|Auto Filter then you will be able to choose LAST from the dropdown list in column D.)

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding latest entry for adjacent cells

    Try the text to columns. Pick the Year/month/day format for your input column

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •