how to merge 2 columns of data and concatenate into unique values

L

littlesoybean

New Member
Joined
Jan 18, 2018
Messages
3
hi everyone.


appreciate any help on the below question


i have data set in first column : a,b,c,d,e........;. about 160 values


data set second column: 1,2,3,4,5....... about 86 value


i want to create a table that line them up and says


a 1,a 2,a 3,a 4,a 5,b 1,b 2,b 3,b 4,b 5,c 1,c 2,c 3,c 4,c 5


so that i can vlookup to other data set and spot missing items.


appreciate any help here, as i m very new to vba
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
littlesoybean said:
i have data set in first column : a,b,c,d,e........;. about 160 values

data set second column: 1,2,3,4,5....... about 86 value

i want to create a table that line them up and says

a 1,a 2,a 3,a 4,a 5,b 1,b 2,b 3,b 4,b 5,c 1,c 2,c 3,c 4,c 5
Click to expand...
Your thread title says "merge and concatenate". Does that mean the "a 1" (your first result) is the letter "a" followed by a space followed by "1" all in a single cell with "a 2" in the cell under it, and so on? Or are you looking for the comma delimited list you showed as the result you want?
 
Upvote 0
Rick Rothstein said:
Your thread title says "merge and concatenate". Does that mean the "a 1" (your first result) is the letter "a" followed by a space followed by "1" all in a single cell with "a 2" in the cell under it, and so on? Or are you looking for the comma delimited list you showed as the result you want?
Click to expand...

thanks for your reply

a b c d and 1 2 3 4 are symbols of values. i would like a1 in either single cell or separate cells (i can concatenate) and a 2 in the cell under it.

e.g.

first data set in column A:
[TABLE="width: 64"]
<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[/TR]
[TR]
[TD]christina[/TD]
[/TR]
[TR]
[TD]richard [/TD]
[/TR]
[TR]
[TD]luke [/TD]
[/TR]
[TR]
[TD]jon


[/TD]
[/TR]
</tbody>[/TABLE]
second data set in column B
[TABLE="width: 64"]
<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"]ref[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]400



[/TD]
[/TR]
</tbody>[/TABLE]
i would like the result to be
[TABLE="width: 128"]
<colgroup><col style="width: 64px" span="2"></colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]ref[/TD]
[/TR]
[TR]
[TD]christina[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]christina[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]christina[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]christina[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]richard [/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]richard [/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]richard [/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]richard [/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]luke [/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]luke [/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]luke [/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]luke [/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]400[/TD]
[/TR]
</tbody>[/TABLE]

thanks again
 
Upvote 0
Okay, this macro will give you the two-column result you asked for in Message #3 (output to Columns D and E)...
Code: 
Sub MergeConcatenate()
  Dim N As Long, R As Long, X As Long, Nme As Variant, Ref As Variant, Result As Variant
  Nme = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Ref = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Nme) * UBound(Ref), 1 To 2)
  For N = 1 To UBound(Nme)
    For R = 1 To UBound(Ref)
      X = X + 1
      Result(X, 1) = Nme(N, 1)
      Result(X, 2) = Ref(R, 1)
    Next
  Next
  Range("D2:E2").Resize(UBound(Result)) = Result
End Sub
HOWEVER, assuming a space for the delimiter (you can change this by modifying the Const statement), the following code will give you the concatenated result in a single column (output to column G)...
Code: 
Sub MergeConcatenate()
  Dim N As Long, R As Long, X As Long, Nme As Variant, Ref As Variant, Result As Variant
  Const Delimiter = " "
  Nme = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Ref = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Nme) * UBound(Ref), 1 To 1)
  For N = 1 To UBound(Nme)
    For R = 1 To UBound(Ref)
      X = X + 1
      Result(X, 1) = Nme(N, 1) & Delimiter & Ref(R, 1)
    Next
  Next
  Range("G2").Resize(UBound(Result)) = Result
End Sub
 
Upvote 0
Rick Rothstein said:
Okay, this macro will give you the two-column result you asked for in Message #3 (output to Columns D and E)...
Code: 
Sub MergeConcatenate()
  Dim N As Long, R As Long, X As Long, Nme As Variant, Ref As Variant, Result As Variant
  Nme = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Ref = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Nme) * UBound(Ref), 1 To 2)
  For N = 1 To UBound(Nme)
    For R = 1 To UBound(Ref)
      X = X + 1
      Result(X, 1) = Nme(N, 1)
      Result(X, 2) = Ref(R, 1)
    Next
  Next
  Range("D2:E2").Resize(UBound(Result)) = Result
End Sub
HOWEVER, assuming a space for the delimiter (you can change this by modifying the Const statement), the following code will give you the concatenated result in a single column (output to column G)...
Code: 
Sub MergeConcatenate()
  Dim N As Long, R As Long, X As Long, Nme As Variant, Ref As Variant, Result As Variant
  Const Delimiter = " "
  Nme = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Ref = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Nme) * UBound(Ref), 1 To 1)
  For N = 1 To UBound(Nme)
    For R = 1 To UBound(Ref)
      X = X + 1
      Result(X, 1) = Nme(N, 1) & Delimiter & Ref(R, 1)
    Next
  Next
  Range("G2").Resize(UBound(Result)) = Result
End Sub
Click to expand...

thank you a million!!!! you are a genius!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to search for duplicates, concatenate cells from another column and delete row
Replies
5
Views
153
Alex Blakenburg
A
J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
140
alansidman
A
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
281
hagia_sofia
hagia_sofia
I
  • Solved
Irregular Data Set
2
Replies
19
Views
320
IdabaMalouki
I
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
103
Ramses505
R

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

Share this page

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
Back
Top