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

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

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

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

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)

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

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)

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.