Finding latest entry for adjacent cells

Topic

New Reply

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!

Reply | Quote

Replies

Try this:

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

Reply | Quote

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 Data|Filter|Advanced Filter 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:
=match(Lookup_value,Lookup_array,Match_type) 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:
=MATCH(C54,$C$7:$C$50,0)
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:
=Index(Array,Row_num,[column_num]) 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 D7:D50 (and other references are as noted above) the formula we woudl use is:
=INDEX($D$7:$D$50,MATCH(C54,$C$7:$C$50,0))
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…

Reply | Quote

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

Reply | Quote