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

[littlesoybean]

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

 

[Rick Rothstein]

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

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?

 

[littlesoybean]

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?

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:

name

christina

richard

luke

jon

<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>
</tbody>

second data set in column B

ref

100

200

300

400

<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>
</tbody>

i would like the result to be

name	ref
christina	100
christina	200
christina	300
christina	400
richard	100
richard	200
richard	300
richard	400
luke	100
luke	200
luke	300
luke	400
jon	100
jon	200
jon	300
jon	400

<colgroup><col style="width: 64px" span="2"></colgroup><tbody>
</tbody>

thanks again

 

[Rick Rothstein]

Okay, this macro will give you the two-column result you asked for in Message #3 (output to Columns D and E)...

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)...

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

 

[littlesoybean]

Okay, this macro will give you the two-column result you asked for in Message #3 (output to Columns D and E)...

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)...

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

thank you a million!!!! you are a genius!

 

[pbcnick]

Okay, this macro will give you the two-column result you asked for in Message #3 (output to Columns D and E)...

  Range("D2:E2").Resize(UBound(Result)) = Result

this is great for putting data from an array into the sheet. thank you