Remapping Columns

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
Hi, I am importing data from a text file into excel.

Unfortunately the text file has stacked columns, so what would be the best way to convert the data from this:
NameAddress
NumberZip
John1234 St.
111111144444
Mary5678 St.
222222255555
Jane8910 St.
333333366666

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

To this:
NameNumberAddressZip
John11111111234 St.44444
Mary22222225678 St.55555
Jane33333338910 St.66666

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

Wondering if I need to use some VBA or if this can be down with Power Query?

Thanks.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
Try:
Code:
Sub NothnLess()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range("A1:B1").Value = srcWS.Range("A1:B1").Value
    desWS.Range("C1:D1").Value = srcWS.Range("A2:B2").Value
    For x = 3 To LastRow Step 2
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 2) = WorksheetFunction.Transpose(Cells(x, 1).Resize(2, 1))
    Next x
    For x = 3 To LastRow Step 2
        desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 2) = WorksheetFunction.Transpose(Cells(x, 2).Resize(2, 1))
    Next x
    Application.ScreenUpdating = True
End Sub
The result will be on Sheet2.
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
One small modification:
Code:
Sub NothnLess()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range("A1:B1").Value = srcWS.Range("A1:B1").Value
    desWS.Range("C1:D1").Value = srcWS.Range("A2:B2").Value
    For x = 3 To LastRow Step 2
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 2) = WorksheetFunction.Transpose(srcWS.Cells(x, 1).Resize(2, 1))
    Next x
    For x = 3 To LastRow Step 2
        desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 2) = WorksheetFunction.Transpose(srcWS.Cells(x, 2).Resize(2, 1))
    Next x
    Application.ScreenUpdating = True
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
Try this. I assume the data starts in row 1

Code:
Sub Macro1()
    Dim u As Long
    Application.ScreenUpdating = False
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    u = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1:B" & u & ",D1:D" & u).FormulaR1C1 = "=R[1]C[-1]"
    Columns("B:D").Copy
    Range("B1").PasteSpecial xlPasteValues
    For i = u To 1 Step -2
        Rows(i).Delete
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,163
Members
416,959
Latest member
Mohzein

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
Top