Crosstable to list
 

Hi,
to all excel freaks, can anybody help me with the VBA code to transfer
a table to a list of this form:
A B C ...
aa 0 9 5
bb 5 7 4
cc 6 8 2
...


aa A 0
bb B 9
cc C 5
aa B 9
bb B 7
...

Thanks in advance
simon


--
simon_nnn
------------------------------------------------------------------------
simon_nnn's Profile: http://www.excelforum.com/member.php...o&userid=26375
View this thread: http://www.excelforum.com/showthread...hreadid=396484

Does it have to be a macro?

You could add a header (Item) to column A (aa, bb, cc column) and then use:
Select your range
Data|pivottable
drag the item to the row field
drag the A header to the data field
drag the B header to the data field
drag the C header to the data field

Each of those should say "sum of...".

If any don't, just doubleclick on it and choose Sum.

Finish up the wizard.

You'll end up with a table that looks like this:

item Data Total
aa Sum of A 0
Sum of B 9
Sum of C 5
bb Sum of A 5
Sum of B 7
Sum of C 4
cc Sum of A 6
Sum of B 8
Sum of C 2
Total Sum of A 11
Total Sum of B 24
Total Sum of C 11


Hit ctrl-a (twice in xl2003) to select all the cells.
edit|copy
followed by: Edit|paste special|Values

Select column B and
edit|replace
what: Sum_of_ (underscore represents a space)
with: (leave blank)
replace all

Then delete those subtotals at the bottom.

Select the top aa (cell A3??) through the bottom of the data (cell A12 in my
sample).

Then hit Edit|goto|special|click blanks.
type = (equal sign)
hit the up arrow
and then hit ctrl-enter

You've just filled the blank cells with their item code.

Now select column A and edit|copy, followed by Edit|paste special|values.

Debra Dalgleish has instructions (with pictures) on how to fill a range at:
http://www.contextures.com/xlDataEntry02.html





simon_nnn wrote:

Hi,
to all excel freaks, can anybody help me with the VBA code to transfer
a table to a list of this form:
A B C ...
aa 0 9 5
bb 5 7 4
cc 6 8 2
..

aa A 0
bb B 9
cc C 5
aa B 9
bb B 7
..

Thanks in advance
simon

--
simon_nnn
------------------------------------------------------------------------
simon_nnn's Profile: http://www.excelforum.com/member.php...o&userid=26375
View this thread: http://www.excelforum.com/showthread...hreadid=396484

--

Dave Peterson

Mmm,
i don't really think this was wat i intended: in fact i want to VB code
to perform the operation you can find at:
http://www.digdb.com/excel_add_ins/t...crosstab_list/

I have a very large dataset which i need to transpose in that way, i
would be very greatfull

Simon


--
simon_nnn
------------------------------------------------------------------------
simon_nnn's Profile: http://www.excelforum.com/member.php...o&userid=26375
View this thread: http://www.excelforum.com/showthread...hreadid=396484