Splitting excel cells based on content

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chrishunt - 17 Jan 2006 10:56 GMT

I have an excel workbook with a worksheet created by a dump from a
database (DOORS in this case). The first column is unique, the second
not. The second column may contain 0 to n identifiers that I want to
look up on a separate sheet. The lookup is easy when the cell in the
2nd column has either 0 or 1 identifiers in it but when there are 2 or
more (separated by a carriage return I think) my lookup fails as lookup
is using the enitre contents of the cell. What i'd like to be able to
do is to automatically (there's some 900+ rows in the worksheet) is to
automatically insert additional rows where the 2nd column has more than
one entry with with all other column values being replicated in the new
rows apart from the 2nd column where I'd like the multiple values to be
provide one per row. Then ideally removing the duplication in the
source cell. As an example the following would become

Col 1 Col2 Col3
1234 REF1 BLAH
REF 2

Col 1 Col2 Col3
1234 REF1 BLAH
1234 REF2 BLAH

Can anyone help?

Signature

chrishunt

Reply to this Message

Bernie Deitrick - 17 Jan 2006 15:47 GMT

Chris,

Try the macro below, which is written based on a CR (character 10) being used as the separator. You
can always change that if the separator isn't what you thought....

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
Dim myVals As Variant
Dim Delim As String

Delim = Chr(10)
For Each myCell In Range("B:B").SpecialCells(xlCellTypeConstants)
If InStr(1, myCell.Value, Delim) > 0 Then
myVals = Split(myCell.Value, Delim)
myCell.EntireRow.Copy
myCell.Resize(UBound(myVals)).EntireRow.Insert
myCell(0, 1).Resize(UBound(myVals) + 1).Value = _
Application.Transpose(myVals)
End If
Next myCell
Application.CutCopyMode = False
End Sub

> I have an excel workbook with a worksheet created by a dump from a
> database (DOORS in this case). The first column is unique, the second
[quoted text clipped - 19 lines]
>
> Can anyone help?

Reply to this Message