Cleaning up consolidated data in a list

[Guest]

Please, can anyone tell me a better way to clean up a spreadsheet that looks like this? The data comes in 3 columns, but the user puts several vendors in one cell instead of creating a new row for each vendor.

Part Vend Price
123 abc, def 100.00
234 abc 75.00
345 def, ghi 70.00

I need it to look like this:
Part Vend Price
123 abc 100.00
123 def 100.00
234 abc 75.00
345 def 70.00
345 ghi 70.00

The problem is I'm getting data from outside sources who don't always put it in the right format. I spend a lot of time cleaning it up. Currently, I do a text to columns on the vendor, then paste the appropriate rows to the end and delete any excess data. I tend to get duplication because there are so many steps (especially when there are several vendors in a single cell.) that I don't always clean up before pasting again (not sure if that makes sense.) Anyway, if anyone has a better idea let me know. Thanks!

 

[jeff]

Hi, Kelly,

You might play with this macro - it assumes that
a comma separates your vendors and that there are
only two vendors in col B. (you can run it twice
if there are three, etc). NOt the most efficient
code, but it may do you OK.

Sub spreadData()
Dim r As Range
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A" & lastrow)
For j = lastrow To 2 Step -1
Range("A" & j).Select
xx = InStr(1, ActiveCell.Offset(0, 1), ",")
If xx Then
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(-1, 1).Value = Trim$(Left
(ActiveCell.Offset(0, 1), xx - 1))
ActiveCell.Offset(0, 1).Value = Trim$(Right
(ActiveCell.Offset(0, 1), xx))
End If
Next j
End Sub

jeff

-----Original Message-----
Please, can anyone tell me a better way to clean up a

spreadsheet that looks like this? The data comes in 3
columns, but the user puts several vendors in one cell
instead of creating a new row for each vendor.

Part Vend Price
123 abc, def 100.00
234 abc 75.00
345 def, ghi 70.00

I need it to look like this:
Part Vend Price
123 abc 100.00
123 def 100.00
234 abc 75.00
345 def 70.00
345 ghi 70.00

The problem is I'm getting data from outside sources who

don't always put it in the right format. I spend a lot
of time cleaning it up. Currently, I do a text to
columns on the vendor, then paste the appropriate rows to
the end and delete any excess data. I tend to get
duplication because there are so many steps (especially
when there are several vendors in a single cell.) that I
don't always clean up before pasting again (not sure if
that makes sense.) Anyway, if anyone has a better idea
let me know. Thanks!

 

[Bernie Deitrick]

Kelly,

Assuming your vendor codes are in the second column of your table, select a
single cell in your table, and run the macro below. Also assumed is that the
vendor list is always separated by a comma and a space.

HTH,
Bernie
MS Excel MVP

Sub KellyCleanUp()
Dim myCell As Range
Dim comCount As Integer

For Each myCell In ActiveCell.CurrentRegion.Columns(2).Cells
comCount = Len(myCell.Value) - Len(Replace(myCell.Value, ",", ""))
If comCount > 0 Then
myCell.EntireRow.Copy
myCell(2).Resize(comCount).EntireRow.Insert
myCell.Resize(comCount + 1, 1).Value = _
Application.Transpose(Split(myCell.Value, ", "))
End If
Next myCell

Application.CutCopyMode = False

End Sub

Please, can anyone tell me a better way to clean up a spreadsheet that

looks like this? The data comes in 3 columns, but the user puts several
vendors in one cell instead of creating a new row for each vendor.

Part Vend Price
123 abc, def 100.00
234 abc 75.00
345 def, ghi 70.00

I need it to look like this:
Part Vend Price
123 abc 100.00
123 def 100.00
234 abc 75.00
345 def 70.00
345 ghi 70.00

The problem is I'm getting data from outside sources who don't always put

it in the right format. I spend a lot of time cleaning it up. Currently, I
do a text to columns on the vendor, then paste the appropriate rows to the
end and delete any excess data. I tend to get duplication because there are
so many steps (especially when there are several vendors in a single cell.)
that I don't always clean up before pasting again (not sure if that makes
sense.) Anyway, if anyone has a better idea let me know. Thanks!

 

[Guest]

I'm so-so with macros. But is there a way to access a macro in many different documents. Not just the one you create it in?

 

[Frank Kabel]

Hi
store the macro in your personal.xls file. See:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

I'm so-so with macros. But is there a way to access a macro in many

different documents. Not just the one you create it in?

 

[Guest]

Thank you all for your help. What a great time-saver!