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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
191
Alex Blakenburg
A
J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
147
alansidman
A
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
303
hagia_sofia
hagia_sofia
I
  • Solved
Irregular Data Set
2
Replies
19
Views
333
IdabaMalouki
I
D
  • Solved
Dynamic AutoFill
2
Replies
18
Views
205
MARK858
MARK858

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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