Macros to change cross tab to flat list table

**jtd84** · 08-15-2011, 01:57 AM

Hi,

Every week I get a data extract that shows the data in a cross tab format. What I need is to get it into a flat list so that I can pivot it. I'd like one column for the "Week Commencing" and one column for the "forecast" (see attached file). I've tried to run a macros but it get really messy (I'm new to vba). It's quite a large dataset so cutting and pasting won't work. I've attached the file for reference. Appreciate any help.

Cheers,
John

**MarvinP** · 08-15-2011, 03:26 AM

Hi jtd84,

Run this macro on your crosstab table (only once) and see if it puts it in a "flat" table format for you.

Please Login or Register  to view this content.

**watersev** · 08-15-2011, 09:45 AM

hi, jtd84, please check attachment, run code "test"

**jtd84** · 08-15-2011, 08:22 PM

thanks for your help guys, worked just how the way i wanted.

Cheers,
John

**mdubay** · 10-05-2012, 06:12 PM

Hello guys, I see that John was trying to gain help on the layout of the file.

I wondered if someone can review this code and let me know how I can get the output to include all of my dimensions (4).

See attached file! I gained a great working code for a single dimension, but need to be able to copy multiple dimensions.

I am trying to get the data to look like so;

unit acco dim1 pdef sotramount
11011 500000 101 1301 1.00
11011 500000 101 1302 2.00
11011 500000 101 1303 3.00
11011 500000 101 1304 4.00
11011 500000 101 1305 5.00
11011 500000 101 1306 6.00
11011 500000 101 1307 7.00
11011 500000 101 1308 8.00
11011 500000 101 1309 9.00
11011 500000 101 1310 10.00
11011 500000 101 1311 11.00
11011 500000 101 1312 12.00
11012 500001 205 1301 0.00
11012 500001 205 1302 0.10
11012 500001 205 1303 0.00
11012 500001 205 1304 0.00
11012 500001 205 1305 0.00
11012 500001 205 1306 0.00
11012 500001 205 1307 0.00
11012 500001 205 1308 0.00
11012 500001 205 1309 0.00
11012 500001 205 1310 0.00
11012 500001 205 1311 0.00
11012 500001 205 1312 0.00

I greatly appreciate your help!

Mark