Move Data from one Column to 3

[freaker123456]

I am scanning equipment with a WASP scanner which does it all into a txt file when imported puts all data in on column. I need to move the data in to 3 columns as there is three tags on each peice of equipment.

So want to move a list like this
111111
222222
333333
111111
222222
333333

to

111111 222222 333333
111111 222222 333333

How can this be done easily?

Thanks

 

[hiker95]

freaker123456,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[Rick Rothstein]

Give this macro a try (if you data does not start on Row 1, change the Const assignment to the actual start row)...

Sub MoveData()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow + 1 To LastRow Step 3
    Cells(X - 1, "B").Resize(, 2) = WorksheetFunction.Transpose(Cells(X, "A").Resize(2))
    Cells(X, "A").Resize(2).Clear
  Next
  Range("A" & StartRow & ":A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[freaker123456]

Thank you that works great what would I change if I wanted the data to only go to two columns?

so data like this

1111
2222
1111
2222
1111
2222

to

1111 2222
1111 2222
1111 2222

two columns instead of three. Some equipment only has two scanable tags not three.

Thanks

 

[Rick Rothstein]

Here is my code, modified to allow you to set the number of columns to distribute across. You would set the number of columns using the newly added Const statement constant named NumOfCols...

Sub MoveData()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 1
  Const NumOfCols As Long = 3
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow + 1 To LastRow Step NumOfCols
    Cells(X - 1, "B").Resize(, NumOfCols - 1) = WorksheetFunction.Transpose(Cells(X, "A").Resize(NumOfCols - 1))
    Cells(X, "A").Resize(NumOfCols - 1).Clear
  Next
  Range("A" & StartRow & ":A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Actually, given your last message, maybe you want to select the number of columns each time you run the program. Here is my code modified to ask you for the number of columns instead of making you have to manually change it each time...

Sub MoveData()
  Dim X As Long, LastRow As Long, NumOfCols As Long
  Const StartRow As Long = 1
  NumOfCols = Application.InputBox("How many columns?", Type:=1)
  If NumOfCols = 0 Then Exit Sub
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow + 1 To LastRow Step NumOfCols
    Cells(X - 1, "B").Resize(, NumOfCols - 1) = WorksheetFunction.Transpose(Cells(X, "A").Resize(NumOfCols - 1))
    Cells(X, "A").Resize(NumOfCols - 1).Clear
  Next
  Range("A" & StartRow & ":A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Just one caution... make sure you type in the correct number as you cannot Undo actions generated by a macro.

 

[freaker123456]

Thank You so much you are AWESOME!