Stack Columns

[Axial]

Hi, this may have been covered previously but I am really struggling.

I have multiple spreadsheets with 20 columns and 10,000 rows.

I would like to simply stack the rows, ignoring the column or row labels. I have tried to use pivot table and tried to use complex string commands I have found on the web but fail to execute these as they are just posted without explanation of the components so I can adjust them to my spreadsheet. I was hoping someone might be able to help and also (if possible) explain the string/solution so I can adjust to other situations and help my colleagues also.

Many thanks in anticipation.

Axial

 

[CWatts]

I'm having difficulty picturing what you're specifically trying to do. I have some code (albeit ugly) that takes data from this format:

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:36px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td> </td><td style="text-align:center; ">1-Mar</td><td style="text-align:center; ">2-Mar</td><td style="text-align:center; ">3-Mar</td><td style="text-align:center; ">4-Mar</td><td style="text-align:center; ">5-Mar</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Joe</td><td style="text-align:center; ">40</td><td style="text-align:center; ">30</td><td style="text-align:center; ">44</td><td style="text-align:center; ">12</td><td style="text-align:center; ">39</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Sally</td><td style="text-align:center; ">1</td><td style="text-align:center; ">4</td><td style="text-align:center; ">23</td><td style="text-align:center; ">15</td><td style="text-align:center; ">34</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Sue</td><td style="text-align:center; ">33</td><td style="text-align:center; ">32</td><td style="text-align:center; ">49</td><td style="text-align:center; ">28</td><td style="text-align:center; ">41</td></tr></tbody></table>

And converts it to this style so I can use it in a pivot.

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:36px;"><col style="width:54px;"><col style="width:43px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Joe</td><td style="text-align:center; ">1-Mar</td><td style="text-align:center; ">40</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Sally</td><td style="text-align:center; ">1-Mar</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Sue</td><td style="text-align:center; ">1-Mar</td><td style="text-align:center; ">33</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Joe</td><td style="text-align:center; ">2-Mar</td><td style="text-align:center; ">30</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Sally</td><td style="text-align:center; ">2-Mar</td><td style="text-align:center; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Sue</td><td style="text-align:center; ">2-Mar</td><td style="text-align:center; ">32</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Joe</td><td style="text-align:center; ">3-Mar</td><td style="text-align:center; ">44</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Sally</td><td style="text-align:center; ">3-Mar</td><td style="text-align:center; ">23</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>Sue</td><td style="text-align:center; ">3-Mar</td><td style="text-align:center; ">49</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>Joe</td><td style="text-align:center; ">4-Mar</td><td style="text-align:center; ">12</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>Sally</td><td style="text-align:center; ">4-Mar</td><td style="text-align:center; ">15</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>Sue</td><td style="text-align:center; ">4-Mar</td><td style="text-align:center; ">28</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>Joe</td><td style="text-align:center; ">5-Mar</td><td style="text-align:center; ">39</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td>Sally</td><td style="text-align:center; ">5-Mar</td><td style="text-align:center; ">34</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td>Sue</td><td style="text-align:center; ">5-Mar</td><td style="text-align:center; ">41</td></tr></tbody></table>

Is that what you're trying to do?

 

[Axial]

Sort of...I have 20 columns with codes in them, each code has been assigned to a person

Person 1: D45 F45, H67, H89
Person 2: G56, U89, G58, P89
Person 3: H56, Y76, Y89, H67

I want to stack all the codes (regardless of the patient), so I just want 1 column with

D45
F45
H67
H89
G56
U89
G58
P89
H56
Y76
Y89
H67

then I can count them using access or a pivot table. because I eventually want for all 10,000 people a total list showing the codes across the entire patient population

H45 occurs 566 times
D56 occurs 788 times
H67 occurs 7643 times

Sorry if I wasn't clearer before. Thank you

 

[CWatts]

Mr. Excel himself has your solution.

http://www.youtube.com/watch?v=DXunn1lgElc

 

[Axial]

it is brilliant, thank you very much, I am so happy people do this. I have been trying for hours to do this.

 

[Nalani]

Sort of...I have 20 columns with codes in them, each code has been assigned to a person

Person 1: D45 F45, H67, H89
Person 2: G56, U89, G58, P89
Person 3: H56, Y76, Y89, H67

Another option that you could try based off of your data layout above:

Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 57px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Person 1</TD><TD>D45</TD><TD>F45</TD><TD>G58</TD><TD>H89</TD><TD>P58</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Person 2</TD><TD>G56</TD><TD>U89</TD><TD>G58</TD><TD>P89</TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Person 3</TD><TD>F45</TD><TD>D45</TD><TD>G58</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Add a couple more lines at the top, so in my interpretation it would look like so:
Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 57px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">TOTALS</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">2</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">0</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00; FONT-WEIGHT: bold">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-WEIGHT: bold">1</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold">D45</TD><TD style="FONT-WEIGHT: bold">F45</TD><TD style="FONT-WEIGHT: bold">H67</TD><TD style="FONT-WEIGHT: bold">H89</TD><TD style="FONT-WEIGHT: bold">G56</TD><TD style="FONT-WEIGHT: bold">U89</TD><TD style="FONT-WEIGHT: bold">G58</TD><TD style="FONT-WEIGHT: bold">P58</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Person 1</TD><TD style="BACKGROUND-COLOR: #ffff00">D45</TD><TD style="BACKGROUND-COLOR: #99cc00">F45</TD><TD style="BACKGROUND-COLOR: #ffcc00">G58</TD><TD>H89</TD><TD style="BACKGROUND-COLOR: #99ccff">P58</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Person 2</TD><TD>G56</TD><TD>U89</TD><TD style="BACKGROUND-COLOR: #ffcc00">G58</TD><TD>P89</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Person 3</TD><TD style="BACKGROUND-COLOR: #99cc00">F45</TD><TD style="BACKGROUND-COLOR: #ffff00">D45</TD><TD style="BACKGROUND-COLOR: #ffcc00">G58</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

With the FORMULA in B1 copied across:
=COUNTIF($B$4:$I$6,B2) Change the ranges to suit.

I color coded the cells to show the Counts for each code in row 2

 

[Axial]

great this does it all, no 2 step process, brilliant, thank you very much.

 

[drbillc]

I realize some time has passed since you posted this, but this is exactly what I'm trying to do. I think of it as reversing the action of creating a pivot table. JMP has a function that allows stacking columns, but I've not seen it anywhere else.

Thanks

 

[drbillc]

Sorry. This post is directed at the answer proposed by CWatts.