[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!

 

[colbyclay]

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

A comma

Here is what I have:

Sub MoveSmallNumbersNextToLargeNumbers()
Dim Ar As Range, SmallNums As Range
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
.Cells = Evaluate(Replace("IF(LEN(@)>7,""=""&@,@)", "@", .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

 

[Rick Rothstein]

A comma

Here is what I have:

Sub MoveSmallNumbersNextToLargeNumbers()
Dim Ar As Range, SmallNums As Range
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
.Cells = Evaluate(Replace("IF(LEN(@)>7,""=""&@,@)", "@", .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

Change the highlighted 7 to a 6 (if you have a 7 digit number as your first number, the code will fail in the way you describe).

 

[colbyclay]

Change the highlighted 7 to a 6 (if you have a 7 digit number as your first number, the code will fail in the way you describe).

I am still getting the 1004 error at

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

I cant see where this is off either...

 

[Rick Rothstein]

I am still getting the 1004 error at

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

I cant see where this is off either...

Can you post a copy of the workbook to DropBox so that we can download it and be able to debug our code using your actual data?

 

[colbyclay]

Here you go...
https://www.dropbox.com/s/2w81y4g8fs4flbc/example.xlsx?dl=0

 

[Rick Rothstein]

Here you go...
https://www.dropbox.com/s/2w81y4g8fs4flbc/example.xlsx?dl=0

Okay, there are (at least) three problems with your data. First, you have a 6-digit number as your first value... you said your large numbers were 7 to 10 digits long. Second, not all of your long numbers are numbers as your original post seemed to indicate they would be, several contain letters... I found 42 with the letter "R" embedded in them (for example, Row 1539). Third, something is drastically wrong with the value in cell A3628. I can correct for the first problem without any difficulty and I assume the value in cell A3628 is an error that you will correct. My main problem is with the cells containing letters... that makes them text values, not numerical values and screws up the technique I used. Please confirm there really are cells with letters (in which case I would have to modify my code).

 

[colbyclay]

Okay, there are (at least) three problems with your data. First, you have a 6-digit number as your first value... you said your large numbers were 7 to 10 digits long. Second, not all of your long numbers are numbers as your original post seemed to indicate they would be, several contain letters... I found 42 with the letter "R" embedded in them (for example, Row 1539). Third, something is drastically wrong with the value in cell A3628. I can correct for the first problem without any difficulty and I assume the value in cell A3628 is an error that you will correct. My main problem is with the cells containing letters... that makes them text values, not numerical values and screws up the technique I used. Please confirm there really are cells with letters (in which case I would have to modify my code).

I removed all the letters from the fields, but am still getting the 1004 error. Any thoughts to why?

 

[Rick Rothstein]

I removed all the letters from the fields, but am still getting the 1004 error. Any thoughts to why?

1) Do you still have 6-digit numbers?

2) Did you fix whatever was wrong with cell A3528?

If your answer is "yes" to the first question, then I have to make a minor change to the code I posted previously in order for it to work.

If your answer is "yes" to the second question, then you must change it in order for my code to work (there is no work-around for a "number" that is not a real number).

Finally, did you remove the letters as a test or can you really move forward without having those kind of values in Column A?

 

[colbyclay]

Rick...I modified the SN based on your feedback and ran the Macro. It worked SWIMMINGLY!!!! Thank you for help and your patience.

Regards,
Colby

 

[Rick Rothstein]

Rick...I modified the SN based on your feedback and ran the Macro. It worked SWIMMINGLY!!!! Thank you for help and your patience.

So you do not me to modify my code to handle 6-digit SN's or SN's that contain letters then, correct?