Hi there,

I'm usually pretty good with excel but I have a file that looks like:

-A- -H-
AMW1 130065
AMW1 130062
AMW1 130063
AMW1 130066
AMW1 130067
AMW1 130110
AMW1 210038
AMW1 2100621
AMW1 2100622
AMW1 130065
AMW1 210063
AMW1 210065
AMW1 210110
AMW1 220038
AMW1 220062
AMW1 210067
AMW1 220063
AMW1 220066
AMW1 220110
AP1 110109, 130109, 190109, 240109
AP1 130018, 230018
AP1 130019, 190019
AP1 130033, 190033
AP1 130034, 240034
AP1 130035
AP1 130041
AP1 130102, 190102
AP1 130103, 180103, 190103
AP1 130107, 190107
AP1 130108, 200108
AP1 130110, 150110, 210110, 220110
AP1 130111, 180111, 1901111, 1901112
AP1 130114, 190114, 200114
AP1 130116, 190116
AP1 130118, 190118
AP1 130147, 200147
AP1 130148
AP1 190021
AP1 190036

That I want to turn into:

-A- -I-
AMW1 #, #, #, #, #, #, #, #, .....
AP1 #, #, #, #, #, #, #, #, .....

Where the first column (A) has several lines of repeating names (could
repeat 1X, 11X, 20X, 40X) that I have to concatenate the corresponding
value (H) with the second column (I). Make sense?

I'd appreciate any help. It's a HUGE 179,398 line xls--that I'd really
rather not convert by hand.

I would run a macro that lookes something like this:
(Please note that I haven't looked it through thoroughly. There might be
errors or things that could have been done in a better way. But it worked
when I tested it on your material.)

Sub conc()
Dim a, b As Integer
Dim c, d As String
a = 2
b = 2
c = ""
d = Range("A2")
While Range("A" & a) < ""
While Range("A" & a) = d
c = c & Range("B" & a)
a = a + 1
If Range("A" & a) = d Then c = c & ", "
Wend
Range("C" & b) = d
Range("D" & b) = c
d = Range("A" & a)
b = b + 1
c = ""
Wend
End Sub


/Marcus


"sandy_eggo" wrote:

Hi there,

I'm usually pretty good with excel but I have a file that looks like:

-A- -H-
AMW1 130065
AMW1 130062
AMW1 130063
AMW1 130066
AMW1 130067
AMW1 130110
AMW1 210038
AMW1 2100621
AMW1 2100622
AMW1 130065
AMW1 210063
AMW1 210065
AMW1 210110
AMW1 220038
AMW1 220062
AMW1 210067
AMW1 220063
AMW1 220066
AMW1 220110
AP1 110109, 130109, 190109, 240109
AP1 130018, 230018
AP1 130019, 190019
AP1 130033, 190033
AP1 130034, 240034
AP1 130035
AP1 130041
AP1 130102, 190102
AP1 130103, 180103, 190103
AP1 130107, 190107
AP1 130108, 200108
AP1 130110, 150110, 210110, 220110
AP1 130111, 180111, 1901111, 1901112
AP1 130114, 190114, 200114
AP1 130116, 190116
AP1 130118, 190118
AP1 130147, 200147
AP1 130148
AP1 190021
AP1 190036

That I want to turn into:

-A- -I-
AMW1 #, #, #, #, #, #, #, #, .....
AP1 #, #, #, #, #, #, #, #, .....

Where the first column (A) has several lines of repeating names (could
repeat 1X, 11X, 20X, 40X) that I have to concatenate the corresponding
value (H) with the second column (I). Make sense?

I'd appreciate any help. It's a HUGE 179,398 line xls--that I'd really
rather not convert by hand.