Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel help
I wonder if someone can help me.
I have a list in excel that contains the same sku more than once on a workbook in no paticular order, however i want to bring back the last time that this sku appeared on the workbook for example: SKU 271562 date counted 11 september SKU 275555 date counted 12 september SKU 271562 date counted 12 september sku 255212 date counted 13 september & so on SKU 271562 date counted 11 october The date range is only applicable to the date counted therefore i cannot search via a date Can someone please help Rgds Gordon |
#2
|
|||
|
|||
Gordon,
If I have understood correctly: 1. Each line in your sample occupies a single cell. 2. All lines are of the format: SKU xxxxxx date counted dd mmmm Therefore, the number of characters until the date starts is fixed. So is the length of every SKU code. With these assumptions, and assuming data are in column A:A, you will use some auxiliary columns B1: =left(A1, 10) -- this extracts the SKU C1: =value(mid(A1, 24, 20)) -- this extracts the date in number format. You can format column C:C to a date format if you want. Now, given an SKU in a cell, say K1 contains "SKU 271562": =max(C1:C100*if(B1:B100=K1, 1, 0)) Supply the necessary ranges in the formula and enter it as an array formula, Shift+Ctrl+Enter. HTH Kostis Vezerides |
#3
|
|||
|
|||
thanks mate this worked a treat
Rgds Gordon |
#4
|
|||
|
|||
Excel help
hi mate,
Can you help me again , i need to bring back the total qty and total counted. Same as above so when the date is brought back i would like the total qty and counted qty. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |