Array Help Please
 

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

Array Help Please
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jennifer" wrote in message
...
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

Array Help Please
 

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

"Jennifer" wrote:

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

Array Help Please
 

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!) :)

"Mike H" wrote:

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

"Jennifer" wrote:

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