Concatenate Multiple Ranges

tenneps199

New Member
Joined
Jul 18, 2006
Messages
12
All,
I am considered my company's Excel and Access expert, but this problem has me stumped. I have 2 columns of data, as below:
Show Name Suffix
american idol
america's best dance crew series
america's got talent show
america's prom queen shows
bolt television
dance fever television series
dance war television show
dancelife television shows

Note: Cell A2 is blank intentionally.
The intent is to concatenate each of the show names (column A) with each of the suffixes. Thus, the resulting formula would provide the following:
Result
american idol
american idol series
american idol show
american idol shows
etc.
america's best dance crew
america's best dance crew series
america's best dance crew show

and so on.
This can be done quickly in Access, but we only have a few users that know anything about access. Thus, can this concatenation of multiple ranges be handled with VB scripting? If so, can anyone provide some guidance on how to write such a script?

Thanks in advance for your help.
MD
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
I'm not seeing where show name and suffix are split in the first example?

You can copy and past cells directly from Excel to the board, I just suggest putting borders, so we know when it is a different column? Like this:

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=195 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=67 height=20>American</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Idol</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>America's</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Best</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Dance</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>etc.</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">etc.</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">etc.</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
Hope that helps.
 

tenneps199

New Member
Joined
Jul 18, 2006
Messages
12
Right. Sorry about the confusion there. Here is a better visual of the 2 columns of data (pipe delimited columns):
Show Name | Suffix
american idol |
america's best dance crew | series
america's got talent | show
america's prom queen | shows
bolt | television
dance fever | television series
dance war | television show
dancelife | television shows
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
So does the suffix appear more than once in the 2nd column? I just want to make sure all the facts are straight.

You want every show name in the first column to contain each suffix in the second column?
 

tenneps199

New Member
Joined
Jul 18, 2006
Messages
12

ADVERTISEMENT

That is correct. The resulting set should have 220 total rows (20 show names * 11 Suffixes). Each show name should be concatenated with each suffix.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
This assumes your data is in column A and B and the result is in column C. Change as necessary:

Code:
Sub concatenateMany()
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    For j = 1 To Cells(Rows.Count, "B").End(xlUp).Row
            Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = Cells(i, "A").Value & " " & Cells(j, "B").Value
    Next j
Next i
End Sub
Hope that helps.
 

tenneps199

New Member
Joined
Jul 18, 2006
Messages
12
My aplogies. I miscounted. It should be 240 total rows as the result. I forgot to include the blank cell in B2, which brings the total number of suffixes to 12 instead of 11.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,211
Messages
5,600,344
Members
414,380
Latest member
p1electric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top