From: BrainDead on
Hi All

I have data listed in rows that I want to display in columns which will then
be used in another program and I can't use a pivot table.

Example:
RefNo Area Item Units Price Value
1871459 Dealer2 CarA 40.560762 401.62989 16290.41438
1871459 Dealer1 CarB 0 2.934 0
1871459 Dealer4 CarY 0 1 0
1871459 Dealer1 CarD 7259.675176 3.421 24835.34878
1871459 Dealer1 CarV 0 1 0
1871459 Dealer1 CarU 0 2.8215 0
1872780 Dealer1 CarA 0 3.7035 0
1872780 Dealer1 CarB 300.706389 5.132 1543.225188
1897670 Dealer5 CarF 2801.437112 3.20225 8970.901992
1897670 Dealer5 CarH 0.143672 6.19482 0.890022179

One line for each Ref No with repeating columns for the number of items they
have (no calculations - the data has already been grouped). Won't know what
will be in any of the columns but can expect a maximum or 10 items per Ref
No. Only lines where Units are greater than zero required. Columns: Area1,
Item1, Units1, Value1, Area2, Item2, Units2, Value2 etc. I can achieve this
with code but can it be done with function or formula?

Will be grateful for any help.
From: Herbert Seidenberg on
Excel 2007 Tables
Transpose with (monster) formulas
and no helper columns.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_03_10.xlsx
Pdf preview:
http://www.mediafire.com/file/j2ciuyonvny/06_03_10.pdf