Excel List to Table

F

frtklau

I need to convert an Excel list to table:
for example

from _LIST_ (1D)
Date DataName Data
1/1 xx 2.0
1/2 xx 2.1
1/2 yy 41
1/2 zz 100
1/3 xx 3
..
2/27 xx 1.1
2/27 yy 40

to _TABLE_ (2D)
xx yy zz
1/1 2.0
1/2 2.1 41 100
1/3 3
..
2/27 1.1 40
2/28
(special conditions: up to 8 DataNames at one time; DataNames ca
be different in different list/tables; I would need to specify th
order of the column somehow).

That is, 1D to 2D. I know pivot table can do this. But I want
different method. (so that it can be modified - e.g. column order in m
prefered order) Help appreciated.

-Frankie
 
H

Herbert Seidenberg

Copy Date to a column with Advanced Filter>unique records only.
Name this column Date2
Copy unique Name(s) to a row and name row Name2
Arrange Date2 and Name2 to enclose a 2D Table.
Fill Table with this formula:
=SUMPRODUCT((Date=Date2 R)*(Name=Name2 C)*Data)
 
Top