Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
Dear all,
I have a worksheet A B C Days Name Type 5 Kent Computer 0.5 Kent Safety 3 Susan Computer 2 Peter Management 1 Peter Safety I want to consolidate the data above into the following table A B C D Name Computer Safety Management Kent 5 0.5 0 Susan 3 0 0 Peter 0 1 2 What should the formula use in the table? Thanks Turk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
On Wed, 28 Dec 2005 11:13:33 +0800, "ims" wrote:
Dear all, I have a worksheet A B C Days Name Type 5 Kent Computer 0.5 Kent Safety 3 Susan Computer 2 Peter Management 1 Peter Safety I want to consolidate the data above into the following table A B C D Name Computer Safety Management Kent 5 0.5 0 Susan 3 0 0 Peter 0 1 2 What should the formula use in the table? Thanks Turk You could use a Pivot Table and generate that summary in no time. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
Thanks Ron, if possible, i prefer using formula rather than pivot table,
which seems much more complicated. Turks "Ron Rosenfeld" ¼¶¼g©ó¶l¥ó·s»D ... On Wed, 28 Dec 2005 11:13:33 +0800, "ims" wrote: Dear all, I have a worksheet A B C Days Name Type 5 Kent Computer 0.5 Kent Safety 3 Susan Computer 2 Peter Management 1 Peter Safety I want to consolidate the data above into the following table A B C D Name Computer Safety Management Kent 5 0.5 0 Susan 3 0 0 Peter 0 1 2 What should the formula use in the table? Thanks Turk You could use a Pivot Table and generate that summary in no time. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
I think Ron is right and you might find that Pivot Tables are easier than you
think..... Try this: Select your data range DataPivot TableSource: Excel List...Click Next Range: (already selected)...Click Next Click the [Layout] button ROW: Drag the Name field here (Dbl-click it and set subtotals to None) COLUMN: Drag the Type field here DATA: Drag the Days field here (If it doesn't change to Sum of Days: Dbl-click it and set SUM) Click [OK] Select a destination then click [Finish That's all you need to do to get the kind of table you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "ims" wrote: Thanks Ron, if possible, i prefer using formula rather than pivot table, which seems much more complicated. Turks "Ron Rosenfeld" ¼¶¼g©ó¶l¥ó·s»D ... On Wed, 28 Dec 2005 11:13:33 +0800, "ims" wrote: Dear all, I have a worksheet A B C Days Name Type 5 Kent Computer 0.5 Kent Safety 3 Susan Computer 2 Peter Management 1 Peter Safety I want to consolidate the data above into the following table A B C D Name Computer Safety Management Kent 5 0.5 0 Susan 3 0 0 Peter 0 1 2 What should the formula use in the table? Thanks Turk You could use a Pivot Table and generate that summary in no time. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
But if you insist on formulas...
Attach headers to your data as shown Days Name1 Type1 5 Ke Comp 0.5 Ke Safe 3 Su Comp 2 Pe Mgm 1 Pe Safe Comp Safe Mgm Type2 Ke 5 0.5 0 Su 3 0 0 Pe 0 1 2 Name2 Insert Name Create Days, Name1, Type1 in the first array and Name2, Type2 in the second array The formula in R1C1 format is =SUMPRODUCT((Type1=Type2 C)*(Name1=Name2 R)*Days) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
Thanks Ron, I followed your steps and found Pivot Table is really user
friendly, I can learn using it within a few minutes. However I find there is one drawback by using Pivot Table. It always generates a new table after the data is changed. I'm now studying the information from Herbert, hope can find out the formula to fit my purpose. Turks "Ron Coderre" ¼¶¼g©ó¶l¥ó·s»D ... I think Ron is right and you might find that Pivot Tables are easier than you think..... Try this: Select your data range DataPivot TableSource: Excel List...Click Next Range: (already selected)...Click Next Click the [Layout] button ROW: Drag the Name field here (Dbl-click it and set subtotals to None) COLUMN: Drag the Type field here DATA: Drag the Days field here (If it doesn't change to Sum of Days: Dbl-click it and set SUM) Click [OK] Select a destination then click [Finish That's all you need to do to get the kind of table you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "ims" wrote: Thanks Ron, if possible, i prefer using formula rather than pivot table, which seems much more complicated. Turks "Ron Rosenfeld" ¼¶¼g©ó¶l¥ó·s»D ... On Wed, 28 Dec 2005 11:13:33 +0800, "ims" wrote: Dear all, I have a worksheet A B C Days Name Type 5 Kent Computer 0.5 Kent Safety 3 Susan Computer 2 Peter Management 1 Peter Safety I want to consolidate the data above into the following table A B C D Name Computer Safety Management Kent 5 0.5 0 Susan 3 0 0 Peter 0 1 2 What should the formula use in the table? Thanks Turk You could use a Pivot Table and generate that summary in no time. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
Dear Herbert,
How do the type1 type2 come from? Turks "Herbert Seidenberg" ¼¶¼g©ó¶l¥ó·s»D . com... But if you insist on formulas... Attach headers to your data as shown Days Name1 Type1 5 Ke Comp 0.5 Ke Safe 3 Su Comp 2 Pe Mgm 1 Pe Safe Comp Safe Mgm Type2 Ke 5 0.5 0 Su 3 0 0 Pe 0 1 2 Name2 Insert Name Create Days, Name1, Type1 in the first array and Name2, Type2 in the second array The formula in R1C1 format is =SUMPRODUCT((Type1=Type2 C)*(Name1=Name2 R)*Days) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate of data using formula in Excel
Days, Type1, Type2, Name1 and Name2 are arbitrary words
given to named ranges. Type1 is the name of the third data column in the first array. To define the name Type1, select this header and the five data entries below it and Insert Name Create Top Row To define the name Type2, select this header and the three cells to the left of it and Insert Name Create Right Column Before you start entering the formula, be sure to select Tools Options General R1C1 reference style You can go back to A1 reference style thereafter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Problem organizing text data into new excel page | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Append the data given in diff sheets of an Excel File to one sheet | Excel Worksheet Functions |