Moving Cells from rows to column

E

eddiegb1

New Member
Joined
Jan 31, 2017
Messages
9
Hi,

I'm looking for a code/macro to modify Data1 in such a way so that it resembles the final result as shown in Data3 below:

Data1:


[TABLE="class: grid, width: 459"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="6" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J.F0304[/TD]
[TD]J1.6111[/TD]
[TD]J1.6731[/TD]
[TD]J1.6751[/TD]
[TD]J1.5801[/TD]
[TD]J1.6991[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J.H0113[/TD]
[TD]J1.1151[/TD]
[TD]J1.521[/TD]
[TD]J1.821[/TD]
[TD]J1.831[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J.H1701[/TD]
[TD]J1.3081[/TD]
[TD]J1.2111[/TD]
[TD]J1.2121[/TD]
[TD]J1.2441[/TD]
[TD]J1.2761[/TD]
[/TR]
</tbody>[/TABLE]


Moving all "J1.xxx.." cells below the "J.xxx.." prefix.


[TABLE="class: grid, width: 139"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J.F0304[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.6111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.6731[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.6751[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.5801[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.6991[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J.H0113[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.1151[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.521[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.821[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.831[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J.H1701[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.3081[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.2111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.2121[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.2441[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]J1.2761[/TD]
[/TR]
</tbody>[/TABLE]


Data3:


[TABLE="class: grid, width: 139"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J.F0304[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J1.6111[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J1.6731[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J1.6751[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J1.5801[/TD]
[/TR]
[TR]
[TD]VDD_AC5[/TD]
[TD];[/TD]
[TD]J1.6991[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J.H0113[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J1.1151[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J1.521[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J1.821[/TD]
[/TR]
[TR]
[TD]VDD_AD4[/TD]
[TD];[/TD]
[TD]J1.831[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J.H1701[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J1.3081[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J1.2111[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J1.2121[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J1.2441[/TD]
[/TR]
[TR]
[TD]VDD_AD5[/TD]
[TD];[/TD]
[TD]J1.2761[/TD]
[/TR]
</tbody>[/TABLE]


Note:

VDD - this prefix is always on Col A
xxn - n is the number of J1 prefix present in each (Data1) row.

The code ends when all J1 cells are moved to the locations as shown in Data3 - on Col C below the "J.xxx.." cell.

Thanks in advance.
Eddie

Sample data1 attached
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
eddiegb1,

Welcome to the MrExcel forum.

I assume that worksheets Data1, and, Data3, already exist.

Here is a macro solution for you to consider, that uses two arrays in memory, that will adjust to the number of raw data rows, and, columns, and, should be very fast.

Sample worksheets:


Excel 2007
ABCDEFGHI
1VDD_AC5;J.F0304J1.6111J1.6731J1.6751J1.5801J1.6991
2VDD_AD4;J.H0113J1.1151J1.521J1.821J1.831
3VDD_AD5;J.H1701J1.3081J1.2111J1.2121J1.2441J1.2761
4
Data1



Excel 2007
ABC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Data3


And, after the macro:


Excel 2007
ABC
1VDD_AC5;J.F0304
2VDD_AC5;J1.6111
3VDD_AC5;J1.6731
4VDD_AC5;J1.6751
5VDD_AC5;J1.5801
6VDD_AC5;J1.6991
7VDD_AD4;J.H0113
8VDD_AD4;J1.1151
9VDD_AD4;J1.521
10VDD_AD4;J1.821
11VDD_AD4;J1.831
12VDD_AD5;J.H1701
13VDD_AD5;J1.3081
14VDD_AD5;J1.2111
15VDD_AD5;J1.2121
16VDD_AD5;J1.2441
17VDD_AD5;J1.2761
18
Data3


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 01/31/2017, ME988674
Dim a As Variant, i As Long, c As Long, n As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
With Sheets("Data1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.CountA(.Range(.Cells(1, 3), .Cells(lr, lc)))
  ReDim o(1 To n, 1 To 3)
End With
For i = LBound(a, 1) To UBound(a, 1)
  For c = 3 To UBound(a, 2)
    If Not a(i, c) = vbEmpty Then
      j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, c)
    End If
  Next c
Next i
With Sheets("Data3")
  .Columns(1).Resize(, 3).ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  For i = 1 To UBound(o, 1)
    n = Application.CountIf(.Columns(1), .Cells(i, 1).Value)
    .Cells(i, 1).Resize(, 3).Font.Bold = True
    i = i + n - 1
  Next i
  .Columns(1).Resize(, 3).AutoFit
  .Activate
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Upvote 0
hiker95 said:
eddiegb1,

Welcome to the MrExcel forum.

I assume that worksheets Data1, and, Data3, already exist.

Here is a macro solution for you to consider, that uses two arrays in memory, that will adjust to the number of raw data rows, and, columns, and, should be very fast.

Sample worksheets:

Excel 2007
ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]VDD_AC5[/TD]
[TD="bgcolor: #FFFFFF"];[/TD]
[TD="bgcolor: #FFFFFF"]J.F0304[/TD]
[TD="bgcolor: #FFFFFF"]J1.6111[/TD]
[TD="bgcolor: #FFFFFF"]J1.6731[/TD]
[TD="bgcolor: #FFFFFF"]J1.6751[/TD]
[TD="bgcolor: #FFFFFF"]J1.5801[/TD]
[TD="bgcolor: #FFFFFF"]J1.6991[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]VDD_AD4[/TD]
[TD="bgcolor: #FFFFFF"];[/TD]
[TD="bgcolor: #FFFFFF"]J.H0113[/TD]
[TD="bgcolor: #FFFFFF"]J1.1151[/TD]
[TD="bgcolor: #FFFFFF"]J1.521[/TD]
[TD="bgcolor: #FFFFFF"]J1.821[/TD]
[TD="bgcolor: #FFFFFF"]J1.831[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]VDD_AD5[/TD]
[TD="bgcolor: #FFFFFF"];[/TD]
[TD="bgcolor: #FFFFFF"]J.H1701[/TD]
[TD="bgcolor: #FFFFFF"]J1.3081[/TD]
[TD="bgcolor: #FFFFFF"]J1.2111[/TD]
[TD="bgcolor: #FFFFFF"]J1.2121[/TD]
[TD="bgcolor: #FFFFFF"]J1.2441[/TD]
[TD="bgcolor: #FFFFFF"]J1.2761[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Data1​



Excel 2007
ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Data3​



And, after the macro:

Excel 2007
ABC
VDD_AC5;J.F0304
VDD_AC5;J1.6111
VDD_AC5;J1.6731
VDD_AC5;J1.6751
VDD_AC5;J1.5801
VDD_AC5;J1.6991
VDD_AD4;J.H0113
VDD_AD4;J1.1151
VDD_AD4;J1.521
VDD_AD4;J1.821
VDD_AD4;J1.831
VDD_AD5;J.H1701
VDD_AD5;J1.3081
VDD_AD5;J1.2111
VDD_AD5;J1.2121
VDD_AD5;J1.2441
VDD_AD5;J1.2761

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Data3​



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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 01/31/2017, ME988674
Dim a As Variant, i As Long, c As Long, n As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
With Sheets("Data1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.CountA(.Range(.Cells(1, 3), .Cells(lr, lc)))
  ReDim o(1 To n, 1 To 3)
End With
For i = LBound(a, 1) To UBound(a, 1)
  For c = 3 To UBound(a, 2)
    If Not a(i, c) = vbEmpty Then
      j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, c)
    End If
  Next c
Next i
With Sheets("Data3")
  .Columns(1).Resize(, 3).ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  For i = 1 To UBound(o, 1)
    n = Application.CountIf(.Columns(1), .Cells(i, 1).Value)
    .Cells(i, 1).Resize(, 3).Font.Bold = True
    i = i + n - 1
  Next i
  .Columns(1).Resize(, 3).AutoFit
  .Activate
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
Click to expand...


hiker95-THANKS a lot!

Works like a charm.

It went so fast - didn't even noticed the results.

thanks again.
 
Upvote 0
eddiegb1,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help - Find value, copy range and move cells to different columns
Replies
2
Views
769
mompy76
M

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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