Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Concatenation Problem / VBA ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default Concatenation Problem / VBA ?
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default Concatenation Problem / VBA ?
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
Reply With QuoteReply With Quote
  #3   Report Post  
carl
 
Posts: n/a
Default Concatenation Problem / VBA ?
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

Reply With QuoteReply With Quote
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default Concatenation Problem / VBA ?
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
Reply With QuoteReply With Quote
  #5   Report Post  
carl
 
Posts: n/a
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default Concatenation Problem / VBA ?
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
....

As an alternative that would allow you to use formulas, try the
user-defined function mcat from

http://groups.google.com/group/micro...456a9e326b19a6

(or http://makeashorterlink.com/?S1E33459B ). If your original table
had field names in the top row and were in, say, A1:C5000, enter ID and
Engine in, say, X1 and Y1, respectively. Select the original table and
run Data Filter Advanced Filter, opting to 'Copy to another
location', leave the Criteria range entry blank but enter the range
containing ID and Engine (X1:Y1) in the Copy to entry field, check
Unique records only, and click OK. That should generate a listing of
the distinct combinations of ID and Engine from the original table.
Then create the concatenated field you want using array formulas like

Z2 [array formula]:
=REPLACE(MCAT(IF((A$2:A$5000=X2)*(C$2:C5000=Y2),"; "&B$2:B$5000,"")),1,1,"")

Fill Z2 down as far as needed (or just double click on the fill handle).

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
have some problem with database baldamenti Excel Discussion (Misc queries) 1 October 13th 05 05:38 PM
Urgent Help Required on Excel Macro Problem Sachin Shah Excel Discussion (Misc queries) 1 August 17th 05 06:26 AM
Excel Display Problem Bill Martin -- (Remove NOSPAM from address) Excel Discussion (Misc queries) 0 April 19th 05 05:25 PM
Freeze Pane problem in shared workbooks JM Excel Discussion (Misc queries) 1 February 1st 05 12:04 AM
Row Autofit problem Excel 2003 Matthias Klaey Excel Discussion (Misc queries) 0 January 19th 05 05:33 PM


All times are GMT +1. The time now is 05:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement