Remapping Columns

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
95
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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,896
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
8,896
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
11,044
Office Version
2007
Platform
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,095,171
Messages
5,442,801
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top