Transposing data with Loop

Luin29

New Member
Joined
Oct 7, 2015
Messages
45
Hello all,

I am hoping for some assistance with a code I have been working on. The purpose of the code is to transpose sets of data from a column that are separated by blank rows into a summary table in the same workbook. An example of what I am trying to accomplish is provided below:

StoreItemNumber Sold
A1Baseballs20
A1Bats5
A1Shorts35
A1Volleyballs12
A1Tennis Rackets15
A1Swim Trunks10
B16Baseballs22
B16Bats8
B16Shorts45
B16Volleyballs6
B16Tennis Rackets7
B16Swim Trunks5
B16Gloves2
Into this:
StoreBaseballsBatsShortsVolleyballsTennis RacketsSwim TrunksGloves
A120535121510--
B16228456752

<tbody>
</tbody>



Here is what I have so far:

Code:
Dim j, jtotalrows As Integer
Dim stRange As String
 Worksheets("Sheet1").Activate
jtotalrows = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
Do While j <= jtotalrows
    j = j + 1
    stRange = "A" & j
    stRange2 = "A" & j + 1
    If Range(stRange).Text <> Range(stRange2).Text Then
        Range(Range("A" & j).Offset(1, 6), Range("A" & j).End(xlDown).Offset(, 6)).Copy
        jtotalrows = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
        Worksheets("Summary Table").Range("A" & j).Offset(1, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        j = j + 1
        Worksheets("Sheet1").Activate
    End If
Loop
I would appreciate any help anyone can provide.

​Thanks
 
Last edited:

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
without VBA,

with a pivot table.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Store</td><td style=";">Item</td><td style=";">Number Sold</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">A1</td><td style=";">Baseballs</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Som van Number Sold</td><td style=";">Kolomlabels</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">A1</td><td style=";">Bats</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Rijlabels</td><td style=";">Baseballs</td><td style=";">Bats</td><td style=";">Gloves</td><td style=";">Shorts</td><td style=";">Swim Trunks</td><td style=";">Tennis Rackets</td><td style=";">Volleyballs</td><td style=";">Eindtotaal</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">A1</td><td style=";">Shorts</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A1</td><td style="text-align: right;;">20</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">35</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td><td style="text-align: right;;">12</td><td style="text-align: right;;">97</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">A1</td><td style=";">Volleyballs</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">B16</td><td style="text-align: right;;">22</td><td style="text-align: right;;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">45</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">95</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">A1</td><td style=";">Tennis Rackets</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Eindtotaal</td><td style="text-align: right;;">42</td><td style="text-align: right;;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;">80</td><td style="text-align: right;;">15</td><td style="text-align: right;;">22</td><td style="text-align: right;;">18</td><td style="text-align: right;;">192</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">A1</td><td style=";">Swim Trunks</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">B16</td><td style=";">Baseballs</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">B16</td><td style=";">Bats</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">B16</td><td style=";">Shorts</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">B16</td><td style=";">Volleyballs</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">B16</td><td style=";">Tennis Rackets</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">B16</td><td style=";">Swim Trunks</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">B16</td><td style=";">Gloves</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Blad25</p><br /><br />
 

Luin29

New Member
Joined
Oct 7, 2015
Messages
45
Thanks for the suggestion of a pivot table but I will be using text that needs to be present. Below is an actual example of data that will be summarized:

1A09/03/15BenzeneND<0.5
1A09/03/151,1-Dichloroethane3.3
1A09/03/151,2-DichloroethaneND<0.5
1A09/03/15Trichloroethene1.1
1A09/03/15Vinyl ChlorideND<0.5
3C09/06/151,1-Dichloroethane 5.9
3C09/06/151,2-DichloroethaneND<0.5
3C09/06/15Trichloroethene3.5
3C09/06/15Vinyl ChlorideND<0.5

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

Into this:

Benzene1,1-Dichloroethane1,2-DichloroethaneTrichloroetheneVinyl Chloride
1A09/03/15ND<0.53.3ND<0.51.1ND<0.5
3C09/06/15--5.9ND<0.53.5ND<0.5

<tbody>
</tbody><colgroup><col><col><col span="5"></colgroup>

Thanks.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
Maybe like this.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #B4C6E7;;">help</td><td style=";">number</td><td style=";">date</td><td style=";">name</td><td style=";">sub name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #FFFF00;;">1A42072Benzene</td><td style=";">1A</td><td style="text-align: right;;">9-3-2015</td><td style=";">Benzene</td><td style=";">ND<0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #FFFF00;;">1A420721,1-Dichloroethane</td><td style=";">1A</td><td style="text-align: right;;">9-3-2015</td><td style=";">1,1-Dichloroethane</td><td style="text-align: right;;">3.3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFF00;;">1A420721,2-Dichloroethane</td><td style=";">1A</td><td style="text-align: right;;">9-3-2015</td><td style=";">1,2-Dichloroethane</td><td style=";">ND<0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFF00;;">1A42072Trichloroethene</td><td style=";">1A</td><td style="text-align: right;;">9-3-2015</td><td style=";">Trichloroethene</td><td style="text-align: right;;">1.1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FFFF00;;">1A42072Vinyl Chloride</td><td style=";">1A</td><td style="text-align: right;;">9-3-2015</td><td style=";">Vinyl Chloride</td><td style=";">ND<0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="background-color: #FFFF00;;">3C421641,1-Dichloroethane</td><td style=";">3C</td><td style="text-align: right;;">9-6-2015</td><td style=";">1,1-Dichloroethane</td><td style="text-align: right;;">5.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="background-color: #FFFF00;;">3C421641,2-Dichloroethane</td><td style=";">3C</td><td style="text-align: right;;">9-6-2015</td><td style=";">1,2-Dichloroethane</td><td style=";">ND<0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="background-color: #FFFF00;;">3C42164Trichloroethene</td><td style=";">3C</td><td style="text-align: right;;">9-6-2015</td><td style=";">Trichloroethene</td><td style="text-align: right;;">3.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="background-color: #FFFF00;;">3C42164Vinyl Chloride</td><td style=";">3C</td><td style="text-align: right;;">9-6-2015</td><td style=";">Vinyl Chloride</td><td style=";">ND<0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style=";">Into this:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Benzene</td><td style=";">1,1-Dichloroethane</td><td style=";">1,2-Dichloroethane</td><td style=";">Trichloroethene</td><td style=";">Vinyl Chloride</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="background-color: #B4C6E7;;">1A</td><td style="text-align: right;background-color: #B4C6E7;;">9-3-2015</td><td style="background-color: #92D050;;">ND<0.5</td><td style="text-align: right;background-color: #92D050;;">3.3</td><td style="background-color: #92D050;;">ND<0.5</td><td style="text-align: right;background-color: #92D050;;">1.1</td><td style="background-color: #92D050;;">ND<0.5</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="background-color: #B4C6E7;;">3C</td><td style="text-align: right;background-color: #B4C6E7;;">9-6-2015</td><td style="background-color: #92D050;;">--</td><td style="text-align: right;background-color: #92D050;;">5.9</td><td style="background-color: #92D050;;">ND<0.5</td><td style="text-align: right;background-color: #92D050;;">3.5</td><td style="background-color: #92D050;;">ND<0.5</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Blad25</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A11</th><td style="text-align:left">=B11&C11&D11</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D25</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$B25&$C25&D$24,$A$10:$E$20,5,0</font>),"--"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E25</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$B25&$C25&E$24,$A$10:$E$20,5,0</font>),"--"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F25</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$B25&$C25&F$24,$A$10:$E$20,5,0</font>),"--"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G25</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$B25&$C25&G$24,$A$10:$E$20,5,0</font>),"--"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H25</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$B25&$C25&H$24,$A$10:$E$20,5,0</font>),"--"</font>)</td></tr></tbody></table></td></tr></table><br />
 

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
Luin29;452s[/QUOTE said:
Maybe try this.
Code:
Sub make_table()

Dim d1 As Object, d2 As Object, c()
Dim a, n, u1, u2
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
a = Range("A1").CurrentRegion
n = UBound(a, 1)
ReDim c(1 To n, 1 To n)

For i = 2 To n
    u1 = a(i, 1)
    u2 = a(i, 2)
    If Not d1.exists(u1) Then d1(u1) = d1.Count + 1
    If Not d2.exists(u2) Then d2(u2) = d2.Count + 1
    c(d1(u1), d2(u2)) = c(d1(u1), d2(u2)) & " " & a(i, 3)
Next i

[e1] = [a1]
[e2].Resize(d1.Count) = Application.Transpose(d1.keys)
[f1].Resize(1, d2.Count) = d2.keys
With [f2].Resize(d1.Count, d2.Count)
    .Value = c
    .Replace "", "--", xlWhole
End With

End Sub

EDIT: This refers only to your opening post.
I didn't see your modified later data.
It does really help to say what you really want straightoff.
 
Last edited:

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
Modified for your modified data, including the no columns headers bit.
Code:
Sub make_table2()

Dim d1 As Object, d2 As Object, c()
Dim a, n, u1, u2
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
a = Range("A1").CurrentRegion
n = UBound(a, 1)
ReDim c(1 To n, 1 To n)

For i = 1 To n
    u1 = a(i, 1) & "|" & a(1, 2)
    u2 = a(i, 3)
    If Not d1.exists(u1) Then d1(u1) = d1.Count + 1
    If Not d2.exists(u2) Then d2(u2) = d2.Count + 1
    c(d1(u1), d2(u2)) = c(d1(u1), d2(u2)) & " " & a(i, 4)
Next i

[i1].Resize(1, d2.Count) = d2.keys
With [i2].Resize(d1.Count, d2.Count)
    .Value = c
    .Replace "", "--", xlWhole
End With
With [g2].Resize(d1.Count)
    .Value = Application.Transpose(d1.keys)
   .TextToColumns Destination:=[g2], DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, OtherChar:="|", _
         FieldInfo:=Array(Array(1, 1), Array(2, 1))
 End With

End Sub
 

Luin29

New Member
Joined
Oct 7, 2015
Messages
45
Thank you kalak, this is great! This code will help me with my current project and I look forward to dissecting the code to become more literate in VBA.

One more question: If I wanted the destination of this transposed summary table to be in a different worksheet, where would I place that line of code?
 

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
Thank you kalak, this is great! This code will help me with my current project and I look forward to dissecting the code to become more literate in VBA.

One more question: If I wanted the destination of this transposed summary table to be in a different worksheet, where would I place that line of code?
Try the modifications in red
Rich (BB code):
Sub make_table3()

Dim d1 As Object, d2 As Object, c()
Dim a, n, u1, u2, x
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
a = Range("A1").CurrentRegion
n = UBound(a, 1)
ReDim c(1 To n, 1 To n)

For i = 1 To n
    u1 = a(i, 1) & "|" & a(1, 2)
    u2 = a(i, 3)
    If Not d1.exists(u1) Then d1(u1) = d1.Count + 1
    If Not d2.exists(u2) Then d2(u2) = d2.Count + 1
    c(d1(u1), d2(u2)) = c(d1(u1), d2(u2)) & " " & a(i, 4)
Next i

Sheets("sheet2").Activate   'CHANGE THIS DESTINATION TO WHEREVER YOU LIKE
[i1].Resize(1, d2.Count) = d2.keys
With [i2].Resize(d1.Count, d2.Count)
    .Value = c
    .Replace "", "--", xlWhole
End With
With [g2].Resize(d1.Count)
    .Value = Application.Transpose(d1.keys)
    For Each x In .Cells
        x.Resize(, 2) = Split(x, "|")
    Next x
End With

End Sub
 
Last edited:

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top