Are you trying to determine the last occurrence of a SKU in a range,
irrespective of the date it is associated with?

gordo wrote:
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


Reply




2. vezerid Oct 13, 7:09 pm show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "vezerid" - Find messages by this author
Date: 13 Oct 2005 11:09:27 -0700
Local: Thurs, Oct 13 2005 7:09 pm
Subject: Excel help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

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


Reply




3. gordo Oct 15, 5:14 pm show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "gordo" - Find messages by this
author
Date: 15 Oct 2005 09:14:45 -0700
Local: Sat, Oct 15 2005 5:14 pm
Subject: Excel help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

thanks mate this worked a treat


Rgds
Gordon


Reply




4. gordo Oct 16, 4:34 pm show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "gordo" - Find messages by this
author
Date: 16 Oct 2005 08:34:39 -0700
Local: Sun, Oct 16 2005 4:34 pm
Subject: Excel help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

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


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.