Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gordo
 
Posts: n/a
Default excel array formula

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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default excel array formula

=MAX(IF(A2:A20="SKU 12752",B2:B20))
an an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gordo" wrote in message
ups.com...
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



  #3   Report Post  
gordo
 
Posts: n/a
Default excel array formula

this didnt work.
what the problem is the workbook in question can have the same sku
appearing multiple times.
I have with the help of someone been able to extract the date the sku
was last counted,however i am unable to bring back total qty and qty
counted for the date.
so for example...

SKU 277252 qty 20 counted 50 date 3rd october
I then have a different SKU and another and so on
the next time i count this sku might be today
SKU 277252 qty 10 counted 40 date 10th october

i can bring back the date with an array formuala but am struggling to
bring back the qty and qty counted ..

Please help

Rgds

Gordon

  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default excel array formula

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.
  #5   Report Post  
gordo
 
Posts: n/a
Default excel array formula

THE SAME SKU CAN APPEAR MULTIPLE TIMES ON THE SPREADSHEET.i HAVE WITH
SOMEONES HELP BEEN ABLE TO DETERMINE THE LAST TIME IT WAS COUNTED.
HOWEVER I WANT TO BRING BACK THE QTY AND QTY COUNTED AS WELL.
FOR EXAMPLE:

SKU 276262 QTY 10 QTY COUNTED 12 DATE 5TH DEC
THEN ANOTHER SKU
AND ANOTHER
THEN THE SKU RE-APPEARS WITH DIFFERENT QTY AND DATE

I WANT TO BRING BACK THE QTY AND QTY COUNTED THE LAST TIME IT WAS
COUNTED


RGDS

GORDON



  #6   Report Post  
Aladin Akyurek
 
Posts: n/a
Default excel array formula

Gordon,

There is no need for capitals. Also, why don't you post the formula you
cooked up with the help someone else. That would help to determine your
layout and the conditions for the retrieval. I'll venture a guess:

X2:

=MATCH(2,1/((SKUrange=SKU)*(QualificationRange="COUNTED")))

which must be confirmed with control+shift+enter.

W2:

=INDEX(DateRange,X2)

Y2:

=INDEX(QtyRange,X2)

Z2:

=INDEX(CountedQtyRange,X2)

Is this even close?

gordo wrote:
THE SAME SKU CAN APPEAR MULTIPLE TIMES ON THE SPREADSHEET.i HAVE WITH
SOMEONES HELP BEEN ABLE TO DETERMINE THE LAST TIME IT WAS COUNTED.
HOWEVER I WANT TO BRING BACK THE QTY AND QTY COUNTED AS WELL.
FOR EXAMPLE:

SKU 276262 QTY 10 QTY COUNTED 12 DATE 5TH DEC
THEN ANOTHER SKU
AND ANOTHER
THEN THE SKU RE-APPEARS WITH DIFFERENT QTY AND DATE

I WANT TO BRING BACK THE QTY AND QTY COUNTED THE LAST TIME IT WAS
COUNTED


RGDS

GORDON

  #7   Report Post  
gordo
 
Posts: n/a
Default excel array formula

Apologies,
this is the formula that bring back the date no matter when the sku
appears
=max(C1:C100*if(B1:B100=K1, 1, 0))

C includes the date and B is the SKU.

i now want to bring back the qty and qty counted


rgds
Gordon

  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default excel array formula

=SUM((MAX(IF(B1:B100=K1,C1:C100))=C1:C100)*(B1:B10 0=K1))

again as an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gordo" wrote in message
oups.com...
Apologies,
this is the formula that bring back the date no matter when the sku
appears
=max(C1:C100*if(B1:B100=K1, 1, 0))

C includes the date and B is the SKU.

i now want to bring back the qty and qty counted


rgds
Gordon



  #9   Report Post  
Aladin Akyurek
 
Posts: n/a
Default excel array formula

Assuming that (a) Qty is in D1:D100, (b) Qty Counted in E1:E100, and (c)
the dates for the same SKU is increasing vith every new occurrence...

L1:

Either...

=MAX(IF(($B$1:$B$100=K1),ROW($B$1:$B$100)-ROW($B$1)+1))

which must be confirmed with control+shift+enter.

Or...

=LOOKUP(2,1/($B$1:$B$100=K1),ROW($B$1:$B$100)-ROW($B$1)+1)

If (c) does not hold:

=MATCH(MAX(IF($B$1:$B$100=K1,$C$1:$C$100,"")),IF($ B$1:$B$100=K1,$C$1:$C$100),0)

which must be confirmed with control+shift+enter.

M1:

=INDEX($C$1:$C$100,L1)

yields the associated date.

N1:

=INDEX($D$1:$D$100,L1)

yields the associated quantity.

=INDEX($E$1:$E$100,L1)

yields the quantity counted.

Note that the foregoing setup seeks efficiency. Determine the position
of the SKU and retrieve the associated values with a simple INDEX
formula which is fast.

gordo wrote:
Apologies,
this is the formula that bring back the date no matter when the sku
appears
=max(C1:C100*if(B1:B100=K1, 1, 0))

C includes the date and B is the SKU.

i now want to bring back the qty and qty counted


rgds
Gordon

  #10   Report Post  
gordo
 
Posts: n/a
Default excel array formula

Sorry mate,
I am completley lost with the formula's you have suggetsed. would it
be possible to send you the workbook.

PLease



  #11   Report Post  
vezerid
 
Posts: n/a
Default excel array formula

Gordon,
if you still have not figured out a way to do this, email me the
workbook at
vezerid at act dot edu

Kostis Vezerides

  #12   Report Post  
Bob Phillips
 
Posts: n/a
Default excel array formula

You can email it me if you want.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gordo" wrote in message
ups.com...
Sorry mate,
I am completley lost with the formula's you have suggetsed. would it
be possible to send you the workbook.

PLease



  #13   Report Post  
gordo
 
Posts: n/a
Default excel array formula

i have sent you both workboooks
Thanks


Gordon

  #14   Report Post  
Bob Phillips
 
Posts: n/a
Default excel array formula

Haven't received it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gordo" wrote in message
oups.com...
i have sent you both workboooks
Thanks


Gordon



  #15   Report Post  
gordo
 
Posts: n/a
Default excel array formula

I have sent the workbook in question to you


Thanks

Gordon

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula, noncontigous range Werner Rohrmoser Excel Worksheet Functions 1 June 22nd 05 12:11 PM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Help with array formula Excel Worksheet Functions 2 January 20th 05 05:17 PM
What instead of an array formula? Reg Besseling Excel Discussion (Misc queries) 3 December 6th 04 02:55 PM


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"