[Help] Formula to move numbers from one column to multiple rows

[colbyclay]

Hi all,
I have 1400 Serial number that have a series of codes associated with them. There are too many for a pivot table so I cut/pasted them to a new spreadsheet. I would like to move the codes from under the serial numbers in column A to the rows next to the Serial Number. Can anyone help?

[TABLE="width: 500"]
<tbody>[TR]
[TD]CURRENT[/TD]
[TD][/TD]
[TD]DESIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15090044[/TD]
[TD][/TD]
[TD]15090044[/TD]
[TD]693[/TD]
[TD]701[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]693[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15090045[/TD]
[TD][/TD]
[TD]15090045[/TD]
[TD]191[/TD]
[TD]315[/TD]
[TD]567[/TD]
[/TR]
[TR]
[TD]191[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15090136[/TD]
[TD][/TD]
[TD]15090136[/TD]
[TD]812[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]812[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Regards!

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub MoveSmallNumbersNextToLargeNumbers()
  Dim Ar As Range, SmallNums As Range
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Cells = Evaluate(Replace("IF(LEN(@)=8,""=""&@,@)", "@", .Address))
    Set SmallNums = .SpecialCells(xlConstants)
    For Each Ar In SmallNums.Areas
      Ar(1).Offset(-1, 1).Resize(, Ar.Count) = Application.Transpose(Ar)
    Next
    SmallNums.ClearContents
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]

 

[colbyclay]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MoveSmallNumbersNextToLargeNumbers()
  Dim Ar As Range, SmallNums As Range
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Cells = Evaluate(Replace("IF(LEN(@)=8,""=""&@,@)", "@", .Address))
    Set SmallNums = .SpecialCells(xlConstants)
    For Each Ar In SmallNums.Areas
      Ar(1).Offset(-1, 1).Resize(, Ar.Count) = Application.Transpose(Ar)
    Next
    SmallNums.ClearContents
    .Value = .Value
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,
I am gettting an OBJECT REQUIRED error on the first line. ("Sub MoveSmallNumbersNextToLargeNumbers()")

 

[Rick Rothstein]

Hi Rick,
I am gettting an OBJECT REQUIRED error on the first line. ("Sub MoveSmallNumbersNextToLargeNumbers()")

I am not sure how that is possible. Where did you put the code and exactly how did you execute it?

 

[colbyclay]

I am not sure how that is possible. Where did you put the code and exactly how did you execute it?

Hi Rick,
With the sheet I am working off of active, I went to Developer -> Visual Basic

I then opened the VBA project for active page and cut/past the macro.

Then I hit
Run -> Continue and recieved a Run-time error '424': Object required

 

[Rick Rothstein]

Hi Rick,
With the sheet I am working off of active, I went to Developer -> Visual Basic

I then opened the VBA project for active page and cut/past the macro.

That is not where macros should be installed; rather, they should be put in a General Module. Once in the VB editor, click the "Insert" item on the menu bar and then click the item labeled "Module" (do not select the one labeled "Class Module"... that is for something completely different)... copy/paste my code into that and then run it.

 

[colbyclay]

That is not where macros should be installed; rather, they should be put in a General Module. Once in the VB editor, click the "Insert" item on the menu bar and then click the item labeled "Module" (do not select the one labeled "Class Module"... that is for something completely different)... copy/paste my code into that and then run it.

Good Morning Rick,
I am now getting a 1004 error. Running the debug, it is highlighting the following line.

Ar(1).Offset(-1, 1).Resize(, Ar.Count) = Application.Transpose(Ar)


Any idea why this would occur?

Thanks
Colby

 

[Rick Rothstein]

Good Morning Rick,
I am now getting a 1004 error. Running the debug, it is highlighting the following line.

Ar(1).Offset(-1, 1).Resize(, Ar.Count) = Application.Transpose(Ar)

Any idea why this would occur?

Some questions...

1) What cells are your numbers in?

2) What are in those cells numeric constants or formulas returning numbers?

3) Are there any blank cells within the data?

4) Are your long numbers always eight digits long?

5) Are you running the macro when the sheet with your numbers is active?

6) Are you using a PC or a Mac?

 

[colbyclay]

1) What cells are your numbers in? A

2) What are in those cells numeric constants or formulas returning numbers? Numeric Constants

3) Are there any blank cells within the data? No

4) Are your long numbers always eight digits long? No, they range from 7-10 digits long. To take that into account, I changed the "IF(LEN(@)=8" to "IF(LEN(@)>7" but I still get the Ar(1) line 1004 Application defined or object defined error.
5) Are you running the macro when the sheet with your numbers is active? Yes

6) Are you using a PC or a Mac?, PC, Excel 365

 

[Rick Rothstein]

What character do you use to separate arguments in a formula... a comma (,) or a semi-colon (?