Array Help Please

[Guest]

If I have a spreadsheet with the following table:

Item Number Jul-03 Aug-03 Sep-03 Oct-03
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

How can I create an arry that will give me the following output:

Item Date Qty
ABC Jul-3 15
ABC Aug-3 20
CDE Jul-3 20
CDE Aug-3 16

I am assuming I need an array formula. Thank you in advance for any help!

Jennifer

 

[Ragdyer]

What you really want is a pivot table.

Debra Dalgleish has extensive web pages on the subject.

http://www.contextures.com/tiptech.html

Scroll down to the P's, and check out the pages on Pivot Tables.
The last item should be an intro to them.

 

[Guest]

Jennifer,

As explained in your last post you don't need an array formula. With your
data laid out as you describe then the formula:-

A10= abc
B10 = jul-03
=SUMPRODUCT(($A$2:$A$6=A10)*($B$1:$E$1=B10)*($B$2:$E$6))

looks for abc (not case sensitive) in column A. Jul-03 in row 1 and returns
the value at the intersect(s) of these 2 if it finds them.

Col A Col B Col C Col D Col E
Item Number Jul-03 Aug-03 Sep-03 Oct-03>
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

Mike

 

[Guest]

Yes, Mike, I'm sorry but I completely misunderstood your response on my last
post. I tried your suggestion and it worked. Thank you so very much for
your help (twice!)