|
|
Concatenation Problem / VBA ?
Thank you. This worked very well.
"Dave Peterson" wrote:
Yep. It worked fine for me.
Was your data laid out in columns A:C?
carl wrote:
Thank you Dave. I ran this code and it kind of looped (basically looked like
it tried to keep sorting).
That said, the table I am trying to build (above) has the codes concatenated
with a semicolon separator.
Was your code intended to perfprm the concatenation ?
Thanks Again.
"Dave Peterson" wrote:
Do this against a copy of your worksheet--it destroys when it merges!
Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
It assumes that your data is in columns A:C (and no place else). It sorts the
data by column A, then column C, then column B (that third key is
optional--remove it if you don't want that column sorted).
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
carl wrote:
I've been working on this problem for a while. My data table looks like this
(the actual table has approximately 5000 rows and for a given ID/Engine
Combo, can have as many as 300 codes that need to be concatenated.
ID Code Engine
BOX050 W3 1
BOX050 F3 1
BOX050 Y0 1
BOX050 A8 1
BOX050 B9 1
BOX050 K0 1
BOX050 K2 1
BOX050 T1 1
BOX050 X2 1
BOX050 AP 1
BOX050 D5 1
BOX050 F6 1
BOX050 G9 1
BOX050 GE 1
BOX050 K3 1
BOX050 L3 1
BOX050 MF 1
BOX050 N7 1
BOX050 N6 1
BOX050 U3 1
BOX050 AK 1
BOX050 BD 1
BOX050 BH 1
I am trying to create a table like this:
Partial Example of Table Trying To Create
BOX050 1 W3;F3;Y0;A8;B9;K0;K2;;.....etc
BOX050 2 E7;R0;R3;C6;Q2;A1;AJ;A3;...etc
BOX060 1 AN;G0;C2;E1;A6;B5;BE;K0;...etc
BOX060 2 M0;A7;B0;EA;L1;M2;U5;X3;...etc
BOX355 1 AN;C2;E1;I0;A6;B5;BE;J0;K0;...etc
BOX355 2 I1;M0;Q1;B0;D9;EA;K1;L1;...etc
BOX549 1 etc
etc 1 etc
etc 2 etc
Thank you in advance for all ideas on how to accomplish this.
--
Dave Peterson
--
Dave Peterson
|