# Remapping Columns

#### Nothnless

##### Board Regular
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:
 Name Address Number Zip John 1234 St. 1111111 44444 Mary 5678 St. 2222222 55555 Jane 8910 St. 3333333 66666

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

To this:
 Name Number Address Zip John 1111111 1234 St. 44444 Mary 2222222 5678 St. 55555 Jane 3333333 8910 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

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

#### mumps

##### Well-known Member
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
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
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 = "=RC[-1]"
Columns("B:D").Copy
Range("B1").PasteSpecial xlPasteValues
For i = u To 1 Step -2
Rows(i).Delete
Next
End Sub``````

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...