Lookup functions-V lookup

[Manju]

Hi,
can some one help me with below:

I have a huge data in excel work book 2003 approx 25000 rows,
For example 1:

ID CODE Balance Type Value Month
Year
789126 AXY ACB $ 20 Jan
2009
789126 Tyx ACB $ 30 Jan
2009
789126 ANC ACB $ 30 Jan
2009
789127 AXY ACB $ 30 Jan
2009
789127 AXY ACB $ 30 Jan
2009
789127 AXY ACB $ 02 Jan
2009
789127 AXY ACB $ 02 Jan
2009
789126 Tyx ACB $ 08 FEB
2009
789127 AXY ACB $ 09 FEB
2009

row are exactly like mentioned above.
Here I wanted data for IDs in the below format: as follows.

For example:2

ID CODE Balance Type Jan Feb Mar Api
May June

789126 ANC ACB $20 $08 0
0 0 0
789126 Tyx ACB $30 0 0
0 0 0
789126 ANC ACB $ 30 0

how do I get data from the example 1 to example 2 as mentioned.

Could I request some one to help me with solution?

Regards
Manju

 

[Arvi Laanemets]

Hi

Let's assume you want your result table on separate sheet, starting from
column A and having headers in 1st row. And source data are on sheet Sheet1,
also starting from column A and having headers in row 1.

In result table, into cell D2 enter the formula:
=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A2),--(Sheet1!$B$2:$B$1000=$B2),--(Sheet1!$C$2:$C$1000=$C2),--(Sheet1!$E$2:$E$1000=D$1),(Sheet1!$D$2:$D$1000))
, and copy it to fill the entire table.


Arvi Laanemets