Need formula to display info across cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 reports from 3 different time periods (April, May, June)
They all have Part Numbers, $$s, Units Sold, & there are some duplicates in the part numbers and some not (because if there weren't sales for a particular month, then its not in the report)

I made a master list of all 3 (combined them identifying their time periods) and then I sorted by part number, listing all the duplicate part numbers below eachother. But, I don't want them listed one after the other as shown below but in a string of info (see below) so that the P/N only lists once.

Is there a formula that would help me? I have been reading the formulas related to INDEX. I am fairly sure that is what I need but I don't know how to type it in properly

Thanks
V

Month Part No $$ Units sol
April 6245 29000 1
May 6245 42,000 2
June 6245 53,000 3
May 6305 28000 1
June 6305 35000 2
June 6495 14000 1

April April May May June Jun
Part No $$ Units $$ Units $$ Unit
6245 29,000 17 42,000 25 53,000 3
6305 0 0 28,000 19 35,000 2
6495 0 0 0 0 14,000 1
 
Hi,

Assuming the following:

1) The second table starts on row 10
2) B10 contains April, C10 contains April, D10 contains May....
3) B11 contains $$, C11 contains Units, D11 contains $$...
4) Part Nos. are listed in column A, starting at A12

Enter this formula in cell B12, copy it to D12 and F12, and copy them
down as far as your need:

=SUMPRODUCT(--($A$2:$A$7=B$10),--($B$2:$B$7=$A12),$C$2:$C$7)

Enter this formula in cell C12, copy it to E12 and G12, and copy them
down as far as you need:

=SUMPRODUCT(--($A$2:$A$7=C$10),--($B$2:$B$7=$A12),$D$2:$D$7)

Note, this is assuming that the dates you're using are not real dates.
If in fact they are real dates, then the formula would have to change.
Let me know if this is the case and I will gladly make the necessary
changes.

Hope this helps!
 
Back
Top