split wrapped cell serial numbers into new lines and allocate description

cr7

New Member
Joined
Jan 7, 2013
Messages
23
Hi , have a major problem here with some badly formatted data.

So I have a description of an item and then the item has multiple references against it i.e order reference - these are all on separate lines in the same row!! wrap texted!! really annoying and bad formatting

example table
description
blue
1003
1005
1006
yellow
2550
3440
4444

<tbody>
</tbody>


So the issue I have is I cant do vlookup as I want to put this data in the following format

blue
1003
blue
1005
blue
1006

<tbody>
</tbody>

so each line item has 1 cell, one item reference and 1 description

Is this even possible in excel?

Many thanks in advance this is doing my head in!

cheers
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi,

I think that I might have a solution for this as I was trying to do something similar.

Use a 3rd column with this formula starting from the C1.

=LOOKUP("zzzzz",A$1:A1)

I have not idea what the "zzzzz" does, but it works. Drag that formula down to the bottom of the 3rd column and it will return the first non-blank cell from Column A to each cell, so you will end up with this:

blue1003 blue
1005 blue
1006 blue
red1008 red
1009 red
1011 red
1012 red
1014 red
1015 red

<tbody>
</tbody>

Hope this helps, I have tested it and its working for me, so let me know if you have any issues.

Cheers,

Cameron
 
  • Like
Reactions: cr7
Upvote 0
cheers mate,

unfortunately this is not quite what I was looking for as I am dealing with multiple lines in 1 cell and they are wrapped.

So essentially the lookup I want to do is lookup all of the serial numbers that are wrapped and apply the same description.

blue1003
1005
1006
yellow2550
3440
4444


<tbody>
</tbody>
 
Upvote 0
so basically looking at above example I want all of those numbers in there own cell, and the description put against each one

e.g blue 10003
blue 10005
blue 10006
 
Upvote 0
Ok, so I think I understand now, sorry I did not read it correctly the first time.

My solution is not elegant but I think you might be able to clean it up either by some macros or modifying the VB code which is beyond me.

Step 1.

Selection Column B and do text to columns, deliminated, other and enter Control J, finish.

this will give you this:

Blue, 1003, 1005, 1006
Red, 2550, 3440, 4444

Step 2.

give each column a header, does not water what it is we can delete it later.

Step 3.

Grab the macro from here:

https://stackoverflow.com/questions...-into-column-with-multiple-rows-in-excel-2007

<code>Sub NormalizeSheet()
Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant

Set wsOriginal = ThisWorkbook.Worksheets("Original") 'This is the name of your original worksheet'
Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
Set clnHeader = New Collection

wsNormalized.Cells.ClearContents 'This deletes the contents of the destination worksheet'

lngColumnCounter = 2
lngRowCounterOriginal = 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

' We'll loop through just the headers to get a collection of header names'
Do Until IsEmpty(rngCurrent.Value)
clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
lngColumnCounter = lngColumnCounter + 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
Loop

'Here we'll reset our Row Counter and loop through the entire data set'
lngRowCounterOriginal = 2
lngRowCounterNormalized = 1
lngColumnCounter = 1

Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))

Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
strKey = rngCurrent.Value ' Get the key value from the current cell'
lngColumnCounter = 2

'This next loop parses the denormalized values for each row'
Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

'We're going to check to see if the current value'
'is equal to NULL. If it is, we won't add it to'
'the Normalized Table.'
If rngCurrent.Value = "NULL" Then
'Skip it'
Else
'Add this item to the normalized sheet'
wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
lngRowCounterNormalized = lngRowCounterNormalized + 1
End If

lngColumnCounter = lngColumnCounter + 1
Loop
lngRowCounterOriginal = lngRowCounterOriginal + 1
lngColumnCounter = 1 'We reset the column counter here because we're on a new row'
Loop



End Sub

Make sure that you either update the macro to your sheetnames or update your sheet names. The macro will fail if the data does not have headers (I have not tried to fix this). Also make sure you have added a 2nd sheet where the data will be copied to.
Run the Macro and you will end up with this:

</code>
Blueheader11
Blueheader22
Blueheader33
redheader14
redheader25
redheader36
redheader47

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

You could update the macro to auto delete the unessary column if needed.

Like I said its not pretty, but once the macro is working for you it should work nicely (you could even build a macro to do the test columns and insert the header row once its all working for you so everything can be done in 1 click.

HTH

Cameron
 
  • Like
Reactions: cr7
Upvote 0
WOW great response much appreciated.

I also found this after some extensive googling

Sub SplitByRowsAndFillBlanks()
'process the whole sheet, could be
'Intersect(Range("B:D"), ActiveSheet.UsedRange)
'if you just want those columns
Dim rng_all_data As Range
Set rng_all_data = Range("A1").CurrentRegion
Dim int_row As Integer
int_row = 0
'create new sheet for output
Dim sht_out As Worksheet
Set sht_out = Worksheets.Add
Dim rng_row As Range
For Each rng_row In rng_all_data.Rows
Dim int_col As Integer
int_col = 0
Dim int_max_splits As Integer
int_max_splits = 0
Dim rng_col As Range
For Each rng_col In rng_row.Columns
'splits for current column
Dim col_parts As Variant
col_parts = split(rng_col, vbLf)
'check if new max row count
If UBound(col_parts) > int_max_splits Then
int_max_splits = UBound(col_parts)
End If
'fill the data into the new sheet, tranpose row array to columns
sht_out.Range("A1").Offset(int_row, int_col).Resize(UBound(col_parts) + 1) = Application.Transpose(col_parts)
int_col = int_col + 1
Next
'max sure new rows added for total length
int_row = int_row + int_max_splits + 1
Next
'go through all blank cells and fill with value from above
Dim rng_blank As Range
For Each rng_blank In sht_out.Cells.SpecialCells(xlCellTypeBlanks)
rng_blank = rng_blank.End(xlUp)
Next
End Sub


This is working good, only issue is that it is causing some duplicates but they are easy to remove

will try yours shortly :)
 
Upvote 0
Give this a try:

Code:
Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
        x = Split(c, Chr(10))
            For i = 0 To UBound(x)
                Cells(2 + y + i, 4) = x(i)
                Cells(2 + y + i, 3) = Cells(2 + k, 1)
            Next
            y = y + i: k = k + 1
    Next
End Sub


Excel Workbook
ABCD
1description
2blue100310041005blue1003
3yellow255034404444blue1004
4blue1005
5yellow2550
6yellow3440
7yellow4444
Sheet1
 
  • Like
Reactions: cr7
Upvote 0
Give this a try:

Code:
Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
        x = Split(c, Chr(10))
            For i = 0 To UBound(x)
                Cells(2 + y + i, 4) = x(i)
                Cells(2 + y + i, 3) = Cells(2 + k, 1)
            Next
            y = y + i: k = k + 1
    Next
End Sub


Sheet1

A
B
C
D
1
description
2
blue
1003
1004
1005
blue
1003
3
yellow
2550
3440
4444
blue
1004
4
blue
1005
5
yellow
2550
6
yellow
3440
7
yellow
4444

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


woah both solutions work!!

the bottom one I love due to the code being so small :)

right only 1 more issue I am facing here which is really frustrating

so in the wrapped column there is a empty line break at the bottom of each!!

so for example:-

1003
1004
1005
blank here

is giving me

10003 blue
10004 blue
10005 blue
blue

so when it fixes the rows I am getting a blank against the description

is there any way to eliminate the bottom empty line break but keep the line breaks above

I can delete it manually but this is not sustainable with the size of data :(
 
Last edited:
Upvote 0
Then give this a try:

Code:
Sub Sep()
Dim c As Range
    For Each c In Range("b2", Range("b" & Rows.Count).End(xlUp))
        c = Application.Trim(Replace(c, Chr(10), " "))
        x = Split(c)
            For i = 0 To UBound(x)
                Cells(2 + y + i, 4) = x(i)
                Cells(2 + y + i, 3) = Cells(2 + k, 1)
            Next
            y = y + i: k = k + 1
    Next
End Sub
 
Last edited:
  • Like
Reactions: cr7
Upvote 0
AWESOME!!

only issue I see now is that I have 4 columns actually not just A and B therefore the script is overwriting my clumns c and d?

can we get the data to populate in columns e and f?

so I would want all 4 columns to populate rather than the 2?

sorry probably a rookie question tried tweaking the code got errors :(
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top