VBA: flat table and put the results in a 2D table.

[jbesclapez]

Hello there,
Hi Mick, Fluff and Rick

I need help on a VBA code that could read a flat table and put the results in a 2D table.

The 2D Tables has in rows a list of Animals that is always in the Flat Tables. (no more no less).
The 2D Tables is NOT generated, it is the base where the flat table will put its values.
The column in the 2D Table has known attributes. (here it is legs, eyes, hairs...)
I would like the macro to read the Flat Table to put the value in the 2D Table.

The Flat Table is on one sheet and the Result table on another sheet.
The FlatTable starts on row 1 with a header
The FlatTable start on row 13 with a header

Please note that the amount of animals can change and be more or less.
Note, that the number of attributes can change but are always read from the 2D Table (now I have 4 attributes but i might add a new one)
Note I added in the table the colunms LMNOP for the flat table as it starts there and for the row it is 13 in the 2D Table

Flat Table from origin

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ANIMAL[/TD]
[TD][/TD]
[TD][/TD]
[TD]Attribute[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]dog[/TD]
[TD][/TD]
[TD][/TD]
[TD]legs[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]dog[/TD]
[TD][/TD]
[TD][/TD]
[TD]eyes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]dog[/TD]
[TD][/TD]
[TD][/TD]
[TD]hairs[/TD]
[TD]why[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]cat[/TD]
[TD][/TD]
[TD][/TD]
[TD]legs[/TD]
[TD]but[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]cat[/TD]
[TD][/TD]
[TD][/TD]
[TD]eyes[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]bird[/TD]
[TD][/TD]
[TD][/TD]
[TD]legs[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]mouse[/TD]
[TD][/TD]
[TD][/TD]
[TD]legs[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]mouse[/TD]
[TD][/TD]
[TD][/TD]
[TD]eyes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]mouse[/TD]
[TD][/TD]
[TD][/TD]
[TD]hairs[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]mouse[/TD]
[TD][/TD]
[TD][/TD]
[TD]stomach[/TD]
[TD]yes[/TD]
[/TR]
</tbody>[/TABLE]


Result in 2D table

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]ANIMAL[/TD]
[TD]legs[/TD]
[TD]eyes[/TD]
[TD]hairs[/TD]
[TD]stomach[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]dog[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]why[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]cat[/TD]
[TD]but[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]bird[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]mouse[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[/TR]
</tbody>[/TABLE]


If someone find the time to write this VBA, please comment it. I am also here to learn and I need to be able to understand it. I am not a total beginner but I have problems with complicated loops.


Thanks for your time

 

[Fazza]

What about without VBA or formulas - a query table.
Such as via menu ALT-D-D-N, though there are other ways in newer versions.
Suggest giving the data a simple defined name and then saving the file before starting.
Follow wizard to end and take option to edit in MS Query. Use SQL as below. HTH

TRANSFORM MAX(Data)
SELECT ANIMAL
FROM YourTableName
GROUP BY ANIMAL
PIVOT Attribute

 

[jbesclapez]

What about without VBA or formulas - a query table.
Such as via menu ALT-D-D-N, though there are other ways in newer versions.
Suggest giving the data a simple defined name and then saving the file before starting.
Follow wizard to end and take option to edit in MS Query. Use SQL as below. HTH

TRANSFORM MAX(Data)
SELECT ANIMAL
FROM YourTableName
GROUP BY ANIMAL
PIVOT Attribute

Hi Fazza,
Thanks for having a look at this.
I need VBA for this. I can read and usually understand it. But my level is not as high as the one of pure developers.
Thanks

 

[Fazza]

Once you've set up the query, VBA can be

ThisWorkbook.RefreshAll