|
Find Duplicates & Concatenate (cpm)
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. |
| All times are GMT +1. The time now is 06:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com