If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup functions-V lookup
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 |
#2
|
|||
|
|||
Lookup functions-V lookup
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 "Manju" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|