Consolidate of data using formula in Excel

[ims]

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

 

[Ron Rosenfeld]

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

 

[ims]

Thanks Ron, if possible, i prefer using formula rather than pivot table,
which seems much more complicated.

Turks

 

[Guest]

I think Ron is right and you might find that Pivot Tables are easier than you
think.....
Try this:

Select your data range
Data>Pivot Table>Source: 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

 

[Herbert Seidenberg]

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)

 

[ims]

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

I think Ron is right and you might find that Pivot Tables are easier than you
think.....
Try this:

Select your data range
Data>Pivot Table>Source: 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

Thanks Ron, if possible, i prefer using formula rather than pivot table,
which seems much more complicated.

Turks

 

[ims]

Dear Herbert,

How do the type1 type2 come from?

Turks

 

[Herbert Seidenberg]

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.