need help concatenating, or maybe a macro would be better.

[Lakewoodsale]

Have a part number listed in A column, it refers a new updated part number in
column C. I would like to concatenate it to a line of text. (or maybe a
macro would be better)

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 in a line of Text as:
01-261-0340-01 replaces 01-261-0340 , 95-0885 , 95-0885-01

Thanks for looking, and any help would be appreciated.

 

[joel]

Since you have mutiple replacements it is bestt to use a simple macr
than complicated macros. the code assume the following

1) the orginal data is on sheet 1 in columns A - C
2) The original data has a header row. Data starts on row 2
3) The code sorts the original data by colum C and then column A
4) the results are put on sheet 2 starting in row 1



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 Su

 

[Patrick Molloy]

In D1

= C1 & " replaces " & A1

In D2
= C1 & ", " & A2

then replicate D2 down