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.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145286

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?

--
Rick (MVP - Excel)


"Lakewoodsale" wrote in message
...
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.

Was this resolved on another forum for you?

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Lakewoodsale" wrote:

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.

I replied to the same question earlier

"JBeaucaire" wrote:

Was this resolved on another forum for you?

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Lakewoodsale" wrote:

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.