I would like to concatenate it, or use a macro, to a line of text

L

Lakewoodsale

Have a part number listed in A column, it refers a new updated part number in
C column. I would like to concatenate it, or use a macro, to output a line
of text.

Column and numbers look like this:

Column A Column B Column C
Old number use new number New Number
01-261-0340 use new number 01-261-0340-01
95-0885 use new number 01-261-0340-01
95-0885-01 use new number 01-261-0340-01

I want to get it so it reads as:
01-261-0340-01 replaces 01-261-0340 , 95-0885 , 95-0885-01

The old part number may have one new number. Or the old number could be 1
of 42 different old part numbers that now have one common new part number.
The above example shows three old part numbers that now have one common new
part number.

Thanks for looking, and any help would be appreciated.
 
J

joel

I posted this code this morning. Notusre why you didn't get it. did
you post on the microsoft site? The microsoft site is down and not
sending e-mail when a response is posted.


Sub GetReplacement()
Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

Newrow = 1
With Sheets("sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column C thenA
..Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("C1"), _
order1:=xlAscending, _
key2:=.Range("A1"), _
order2:=xlAscending

RowCount = 2
OutputStr = ""
Do While .Range("A" & RowCount) <> ""
Original = .Range("A" & RowCount)
If OutputStr = "" Then
Replacement = .Range("C" & RowCount)
OutputStr = Replacement & " replaces " & Original
Else
OutputStr = OutputStr & " , " & Original
End If

If .Range("C" & RowCount) <> .Range("C" & (RowCount + 1)) Then
DestSht.Range("A" & Newrow) = OutputStr
Newrow = Newrow + 1
OutputStr = ""
End If
RowCount = RowCount + 1
Loop

End With

End Sub
 
R

Rick Rothstein

A couple of questions....

1) Are all of your New Numbers of the same "shape", that is, two digits,
dash, three digits, dash, four digits, dash, two digits?

2) Is your data sorted? If so, but which column?

3) Where did you want the output placed... in Column B where you show "use
new number", repeated for each Old Number or in a separate column showing
once for each New Number?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top