General discussion


Breaking columns into a List on Excel.

By ajleap ·
How do I make a simpler list of a columnar report?

Take a table with column headings:
Date Cust Beer Chips Soda Cake
1/29 Jerry 6 1 2
1/30 Terry 3 4

And build a table with column headings:
Date Cust Product Qty
1/29 Jerry Beer 6
1/29 Jerry Chips 1
1/29 Jerry Cake 2
1/30 Terry Chips 3
1/30 Terry Soda 4

I am looking for a way to do this on a 700 row, by 30 column table. Accurately.
I can muscle it, Hav not found a way via pivot table

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by kees.valkenswaard In reply to Breaking columns into a L ...

This is typically to be done with VBA. However it can be done with standard Excel features and simple formulas. It is some work but probably still faster if you are no pro in VBA.

Check how long your list is. If you have 1000 lines and 50 columns it would still fit on one Excel sheet. Keep a few lines below your original list empty. Suppose you can start at line 1002.

In A1002 put =A2, idem in B1002 =B2, in C1002 =C$1 [for the heading title], in D1002 =C2. Copy these 4 cells.

Suppose line 998 was the last one used.
In the name box left in the formula bar type A1002:D1998 and press enter and paste.
Use CTRL down-arrow to go below and do the same trick, now for column D of your original list. [heading in C in the new list and numbers in D] You may have to calculate the end line number. Do the same tricks as many times as needed. Always heading to C and numbers to D.

Select the whole new prepared list, copy it. In may be safe to go to sheet 2, do a paste special, only values. Sort this list on date as primary sort key and second key customer. [Sort is under Data]

Apply auto-filter on this list. Use customize on column D with not equal 0, or >0.
Only the relevant lines will be shown. Copy and paste on sheet 3. This is your list.

Some work, but probably done within half an hour, even if you are not a pro.

NOTE In some versions of Excel it may be needed to select visible cells only under edit, go to, special? for the filtered list before performing the copy and paste. Special.. has also the possibility to select current region which may be useful for selecting the area to be copied.
Good luck.

Collapse -

by ajleap In reply to

I had the big peices, Name box, and auto filter shortcuts worked fine. I did not know these before.

Collapse -

by ajleap In reply to Breaking columns into a L ...

This question was closed by the author

Related Discussions

Related Forums