Concatenate Multiple Ranges

[tenneps199]

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

 

[schielrn]

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]

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]

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]

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]

This assumes your data is in column A and B and the result is in column C. Change as necessary:

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]

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.

 

[tenneps199]

This works beautifully! Thanks for the immediate response!