Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Refresh a Validation List? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |