Processing and transposing data from columns (Excel 2007)

M

MrEngineerUK

New Member
Joined
Jun 2, 2011
Messages
8
I have attached a short example below which I did "manually" but I would appreciate some assistance into making this process more automated as I have worksheets with 60000 rows which I need to process and it is taking me a long time. Basically I want to turn columns A to D into columns E to J. I want to ignore zeros. Column A is transposed into columns E and F. Please note how the numbers are ordered and by row basis. Same operation for Column C into columns H and I. Column G and J are basically columns B and D without zeros. Any advice would be greatly appreciated. Many thanks in advance!!

<table style="font-family:Arial,Arial; font-size:10pt; 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:84px;"><col style="width:55px;"><col style="width:84px;"><col style="width:55px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></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><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Data 1a</td><td>Data 1b</td><td>Data 2a</td><td>Data 2b</td><td style="font-weight:bold; ">Data 1a</td><td style="font-weight:bold; ">Data 1a</td><td style="font-weight:bold; ">Data 1b</td><td style="font-weight:bold; ">Data 2a</td><td style="font-weight:bold; ">Data 2a</td><td style="font-weight:bold; ">Data 2b</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.081159091</td><td style="text-align:right; ">0</td><td style="font-weight:bold; text-align:right; ">0.397434</td><td style="font-weight:bold; text-align:right; ">0.438038</td><td style="font-weight:bold; text-align:right; ">0.3396</td><td style="font-weight:bold; text-align:right; ">0.081159</td><td style="font-weight:bold; text-align:right; ">0.192458</td><td style="font-weight:bold; text-align:right; ">0.4955</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="font-weight:bold; text-align:right; ">1.117493</td><td style="font-weight:bold; text-align:right; ">1.232545</td><td style="font-weight:bold; text-align:right; ">0.5102</td><td style="font-weight:bold; text-align:right; ">0.523158</td><td style="font-weight:bold; text-align:right; ">0.588076</td><td style="font-weight:bold; text-align:right; ">0.3898</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.4955</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.192457944</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:right; ">0.397433735</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.3396</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="text-align:right; ">0.438038462</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.523158163</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.3898</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.588076397</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">32</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">33</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">34</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">35</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">36</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">37</td><td style="text-align:right; ">1.11749345</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">38</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">39</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">40</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.5102</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">41</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">42</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">43</td><td style="text-align:right; ">1.232545151</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
MrEngineerUK,

Welcome to the MrExcel forum.


Sample raw data before the macro:


Excel Workbook
ABCDE
1Data 1aData 1bData 2aData 2b
2000.0811590910
30000
40000.4955
50000
60000
7000.1924579440
80000
90000
100000
110000
120000
130000
140000
150000
160000
170.397433735000
1800.339600
190.438038462000
200000
210000
220000
230000
24000.5231581630
250000.3898
260000
27000.5880763970
280000
290000
300000
310000
320000
330000
340000
350000
360000
371.11749345000
380000
390000
4000.510200
410000
420000
431.232545151000
44
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJ
1Data 1aData 1bData 2aData 2bData 1aData 1aData 1bData 2aData 2aData 2b
2000.08115909100.3974337350.4380384620.33960.0811590910.1924579440.4955
300001.117493451.2325451510.51020.5231581630.5880763970.3898
40000.4955
50000
60000
7000.1924579440
80000
90000
100000
110000
120000
130000
140000
150000
160000
170.397433735000
1800.339600
190.438038462000
200000
210000
220000
230000
24000.5231581630
250000.3898
260000
27000.5880763970
280000
290000
300000
310000
320000
330000
340000
350000
360000
371.11749345000
380000
390000
4000.510200
410000
420000
431.232545151000
44
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Option Base 1
Sub ReorgData()
' hiker95, 06/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=554526
Dim LR As Long, LC As Long, NC As Long, NR As Long
Dim a As Long, aa As Long, b As Long
Dim D
Application.ScreenUpdating = False
With ActiveSheet
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  For a = 1 To LC Step 1
    LR = .Cells(Rows.Count, a).End(xlUp).Row
    aa = Application.CountIf(.Range(.Cells(2, a), .Cells(LR, a)), "<>0")
    If aa > 0 Then
      ReDim D(1 To aa)
      aa = 0
      For b = 2 To LR Step 1
        If .Cells(b, a) <> 0 Then
          aa = aa + 1
          D(aa) = .Cells(b, a).Value
        End If
      Next b
      Select Case UBound(D)
        Case 1
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Value = .Cells(1, a).Value
          .Cells(2, NC).Value = D(1).Value
        Case 2
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Value = .Cells(1, a).Value
          .Cells(2, NC).Value = D(1)
          .Cells(3, NC).Value = D(2)
        Case Else
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Resize(, 2).Value = .Cells(1, a).Value
          NR = 1
          On Error Resume Next
          For b = 1 To UBound(D) Step 2
            NR = NR + 1
            .Cells(NR, NC).Value = D(b)
            .Cells(NR, NC + 1).Value = D(b + 1)
          Next b
          On Error GoTo 0
      End Select
      Erase D
    End If
  Next a
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub


Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.
 
Upvote 0
Dear Hiker
First of all thanks very much for your efforts in what is my first post on this great website. I've been sitting here for hours pondering over my problem...and reading through previous posts. I've just attempted to run your macro, but it generated extra columns (H,L) ...please see below. Why is it doing that? In your output, you didn't show extra columns. Otherwise, your code is replicating the data correctly. So would appreciate your feedback on the extra columns.
Thanks again for your help.

Sheet2

<table style="font-family:Arial,Arial; font-size:10pt; 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:84px;"><col style="width:52px;"><col style="width:84px;"><col style="width:52px;"><col style="width:84px;"><col style="width:84px;"><col style="width:52px;"><col style="width:52px;"><col style="width:84px;"><col style="width:84px;"><col style="width:52px;"><col style="width:52px;"></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><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Data 1a</td><td>Data 1b</td><td>Data 2a</td><td>Data 2b</td><td>Data 1a</td><td>Data 1a</td><td>Data 1b</td><td>Data 1b</td><td>Data 2a</td><td>Data 2a</td><td>Data 2b</td><td>Data 2b</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.081159091</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.397433735</td><td style="text-align:right; ">0.438038462</td><td style="text-align:right; ">0.3396</td><td style="text-align:right; ">0.5102</td><td style="text-align:right; ">0.081159091</td><td style="text-align:right; ">0.192457944</td><td style="text-align:right; ">0.4955</td><td style="text-align:right; ">0.3898</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1.11749345</td><td style="text-align:right; ">1.232545151</td><td style="text-align:right; ">0.4751</td><td style="text-align:right; ">0.4241</td><td style="text-align:right; ">0.523158163</td><td style="text-align:right; ">0.588076397</td><td style="text-align:right; ">0.4487</td><td style="text-align:right; ">0.489</td></tr></tbody></table>
 
Upvote 0
MrEngineerUK,

I will need a screenshot of your raw data sheet that the macro did not work correctly on.
 
Upvote 0
Dear Hiker
Here it is below. I tried your macro in both 2003 and 2007, still creating extra column. It is possible the problem occurs when the cell count goes over 100? Appreciate your help.
Thanks!
MrEngineer

Excel Workbook
ABCD
1Data 1aData 1bData 2aData 2b
2000.0811590910
30000
40000.4955
50000
60000
7000.1924579440
80000
90000
100000
110000
120000
130000
140000
150000
160000
170.397433735000
1800.339600
190.438038462000
200000
210000
220000
230000
24000.5231581630
250000.3898
260000
27000.5880763970
280000
290000
300000
310000
320000
330000
340000
350000
360000
370000
380000
390000
400000
410000
420000
430000
440000
450000
460000
470000
480000
490000
500000
510000
520000
531.11749345000
540000
550000
5600.510200
570000
580000
591.232545151000
600000
61001.2678163270
620000
630000.4487
640000
650000
66001.3689047620
670000
680000
690000
700000
710000
720000
730000
740000
750000
760000
770000
780000
790000
800000
810000
820000
830000
840000
850000
860000
870000
880000
890000
901.842471698000
910000
9200.475100
930000
940000
951.945165414000
96001.9704778860
970000
980000
990000.489
1000000
1010000
1020000
103002.1104337570
Sheet3
 
Upvote 0
MrEngineerUK,


After the updated macro (with one slight change) on your latest screenshot:


Excel Workbook
ABCDEFGHIJKLM
1Data 1aData 1bData 2aData 2bData 1aData 1aData 1bData 1bData 2aData 2aData 2bData 2b
2000.08115909100.3974337350.4380384620.33960.51020.0811590910.1924579440.49550.3898
300001.117493451.2325451510.47510.5231581630.5880763970.44870.489
40000.49551.8424716981.9451654141.2678163271.368904762
500001.9704778862.110433757
60000
7000.1924579440
80000
Sheet3





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Option Base 1
Sub ReorgDataV2()
' hiker95, 06/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=554526
Dim LR As Long, LC As Long, NC As Long, NR As Long
Dim a As Long, aa As Long, b As Long
Dim D
Application.ScreenUpdating = False
With ActiveSheet
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  For a = 1 To LC Step 1
    LR = .Cells(Rows.Count, a).End(xlUp).Row
    aa = Application.CountIf(.Range(.Cells(2, a), .Cells(LR, a)), "<>0")
    If aa > 0 Then
      ReDim D(1 To aa)
      aa = 0
      For b = 2 To LR Step 1
        If .Cells(b, a) <> 0 Then
          aa = aa + 1
          D(aa) = .Cells(b, a).Value
        End If
      Next b
      Select Case UBound(D)
        Case 1
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Value = .Cells(1, a).Value
          .Cells(2, NC).Value = D(1)
        Case 2
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Value = .Cells(1, a).Value
          .Cells(2, NC).Value = D(1)
          .Cells(3, NC).Value = D(2)
        Case Else
          NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          .Cells(1, NC).Resize(, 2).Value = .Cells(1, a).Value
          NR = 1
          On Error Resume Next
          For b = 1 To UBound(D) Step 2
            NR = NR + 1
            .Cells(NR, NC).Value = D(b)
            .Cells(NR, NC + 1).Value = D(b + 1)
          Next b
          On Error GoTo 0
      End Select
      Erase D
    End If
  Next a
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.
 
Upvote 0
Thanks again for your reply and for your efforts and I am sorry for taking up a lot of your time. However, the issue here now, is that the macro is splitting the data from column B into two columns G and H, which I don't want. Doing this also with column D, splitting into two columns K and L. Below is the desired output to the last screen shot I gave you. It won't let me paste more than 500 cells but I hope it makes sense now? Appreciate your assistance. Cheers.

Excel Workbook
EFGHIJ
1Data 1aData 1aData 1bData 2aData 2aData 2b
20.3974337350.4380384620.33960.0811590910.1924579440.4955
31.117493451.2325451510.51020.5231581630.5880763970.3898
41.8424716981.9451654140.47511.2678163271.3689047620.4487
51.9704778862.1104337570.489
Sheet3
 
Upvote 0
MrEngineerUK,

However, the issue here now, is that the macro is splitting the data from column B into two columns G and H, which I don't want.
Click to expand...

Check out your original post #1.


Are you now requesting that for each raw data column A thru D, you only want columns E thru H?
 
Upvote 0
Sorry for the confusion. What I think your code is doing, it is transposing the data for each column into two columns, which is indeed what I wanted, but only for column A and C. The output for those is correct. For column B and D I don't want it to split the data into two columns and transpose the data. I don't know if you noticed, but amongst all the zeros, there are three numbers close to each other in seperate columns and I am trying to group those numbers on one row. Your first macro worked wonderfully and it was exactly what I wanted, but when I tried to apply it to a worksheet with more cells, it didn't work properly. Basically, what I am requesting from you, is to magically turn screenshot in message #5, into screenshot in message #7! That's your challenge...should you choose to accept it :-). Thanks again...and will wait for your feedback.
 
Upvote 0
MrEngineerUK,


After the latest macro:


Excel Workbook
ABCDEFGH
1Data 1aData 1bData 2aData 2bData 1aData 1bData 2aData 2b
2000.08115909100.3974337350.33960.0811590910.4955
300000.4380384620.51020.1924579440.3898
40000.49551.117493450.47510.5231581630.4487
500001.2325451510.5880763970.489
600001.8424716981.267816327
7000.19245794401.9451654141.368904762
800001.970477886
900002.110433757
100000
110000
120000
130000
140000
150000
160000
170.397433735000
1800.339600
190.438038462000
200000
210000
220000
230000
24000.5231581630
250000.3898
260000
27000.5880763970
280000
Sheet3





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub ReorgDataV4()
' hiker95, 06/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=554526
Dim LR As Long, LC As Long, NC As Long, NR As Long
Dim a As Long, aa As Long
Application.ScreenUpdating = False
With ActiveSheet
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  NC = LC
  For a = 1 To LC Step 1
    LR = .Cells(Rows.Count, a).End(xlUp).Row
    NC = NC + 1
    .Cells(1, NC).Value = .Cells(1, a).Value
    NR = 1
    For aa = 2 To LR Step 1
      If .Cells(aa, a) <> 0 Then
        NR = NR + 1
        .Cells(NR, NC).Value = .Cells(aa, a).Value
      End If
    Next aa
  Next a
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub


Then run the ReorgDataV4 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
81
MrsFraser07
M
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
73
Fluff
Fluff
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
135
Peter_SSs
Peter_SSs
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
126
Kaper
Kaper

Forum statistics

Threads
1,224,574
Messages
6,179,628
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top