Is there an Excel Macro to combine (concatenate) all items in two lists?
- Thread starter RJD247
- Start date
SirJB7
Excel Rōnin
Hi, RJD247!
Assuming you have three named ranges of the same size and one column each (List1, List2, List3), try adding this code to a module (Alt-F11, Insert, Module, copy from below and paste in the right panel):
-----
[pre]
[/pre]
-----
Add the proper conversion instructions if needed (CStr, CInt, Clng, ...).
Regards!
Assuming you have three named ranges of the same size and one column each (List1, List2, List3), try adding this code to a module (Alt-F11, Insert, Module, copy from below and paste in the right panel):
-----
[pre]
Code:
Option Explicit
Sub ListJoining()
' constants
Const ksList1 = "List1"
Const ksList2 = "List2"
Const ksList3 = "List3"
' declarations
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim J As Long
' start
Set rng1 = Range(ksList1)
Set rng2 = Range(ksList2)
Set rng3 = Range(ksList3)
' process
For J = 1 To rng1.Rows.Count 'from 2 if you have headers within the range
rng3.Cells(J, 1).Value = rng1.Cells(J, 1).Value & " - " & rng2.Cells(J, 2).Value
Next J
' end
End Sub-----
Add the proper conversion instructions if needed (CStr, CInt, Clng, ...).
Regards!
Hi, SirJB7 ~
What I am trying to do is combine everything in List A with everything in List B to end up with the data in List C. (If you select the "# Edit" it will make it much easier to see the desired results, instead of all the columns running together). In reality, Column A would have several thousand in the list, while Column B would have much less.
Any information or assistance is very helpful and appreciated!
What I am trying to do is combine everything in List A with everything in List B to end up with the data in List C. (If you select the "# Edit" it will make it much easier to see the desired results, instead of all the columns running together). In reality, Column A would have several thousand in the list, while Column B would have much less.
Any information or assistance is very helpful and appreciated!
SirJB7
Excel Rōnin
Hi, RJD247!
In the text you copied in the first post, it isn't very clear which column has what value.
Let me see if I understood:
If List1 has the values A, B, C, List2 D, E, List3 should have AD, AE, BD, BE, CD, CE?
Confirm this, please. Otherwise, write down the List3 values with this data.
Regards!
In the text you copied in the first post, it isn't very clear which column has what value.
Let me see if I understood:
If List1 has the values A, B, C, List2 D, E, List3 should have AD, AE, BD, BE, CD, CE?
Confirm this, please. Otherwise, write down the List3 values with this data.
Regards!
Hi ~
Yes, this is correct. Sorry that my sample data didn't convey what I was getting at. I am looking for a way to combine each item in list 1 with each item in list 2, and to somehow keep going with that function until it comes to the end of the data. Is this even a possibility?
Again, thanks so much for all your help!
Yes, this is correct. Sorry that my sample data didn't convey what I was getting at. I am looking for a way to combine each item in list 1 with each item in list 2, and to somehow keep going with that function until it comes to the end of the data. Is this even a possibility?
Again, thanks so much for all your help!
SirJB7
Excel Rōnin
Hi, RJD247!
Change code to this:
-----
[pre]
[/pre]
-----
Regards!
Change code to this:
-----
[pre]
Code:
Option Explicit
Sub ListJoining()
' constants
Const ksList1 = "List1"
Const ksList2 = "List2"
Const ksList3 = "List3"
' declarations
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim I As Long, J As Long, K As Long
' start
Set rng1 = Range(ksList1)
Set rng2 = Range(ksList2)
Set rng3 = Range(ksList3)
' process
' titles
I = 1
rng3.Cells(I, 1).Value = "All against all"
' data
For J = 1 To rng1.Rows.Count 'from 2 if you have headers within the range
For K = 1 To rng2.Rows.Count 'from 2 if you have headers within the range
I = I + 1
rng3.Cells(I, 1).Value = rng1.Cells(J, 1).Value & " - " & rng2.Cells(K, 2).Value
Next K
Next J
' end
End Sub-----
Regards!
SirJB7
Excel Rōnin
Hi, RJD247!
Yes, of course. Give a look at this, is the second sticky post (the green ones) in the main page at this forums:
http://chandoo.org/forums/topic/posting-a-sample-workbook
In the case you wanted me to upload a file with my test examples, here it is:
http://www.2shared.com/file/X2V4j8fc/Is_there_an_Excel_Macro_to_com.html
I've just found an error in the code I posted before:
where it says "... & rng2.Cells(K, 2).Value"
it should say "... & rng2.Cells(K, 1).Value"
Sorry for that, it's modified in the uploaded version.
Just advise if found any trouble.
Regards!
Yes, of course. Give a look at this, is the second sticky post (the green ones) in the main page at this forums:
http://chandoo.org/forums/topic/posting-a-sample-workbook
In the case you wanted me to upload a file with my test examples, here it is:
http://www.2shared.com/file/X2V4j8fc/Is_there_an_Excel_Macro_to_com.html
I've just found an error in the code I posted before:
where it says "... & rng2.Cells(K, 2).Value"
it should say "... & rng2.Cells(K, 1).Value"
Sorry for that, it's modified in the uploaded version.
Just advise if found any trouble.
Regards!
SirJB7
Excel Rōnin
Hi, RJD247!
The VBA code just uses 3 named ranges that are defined from the Excel worksheet (select a range, tab Formula, Assign Name -or so, mine it's in spanish, sorry-), List1, List2 and List3.
So defining correctly this ranges (note that List1 and List2 begin in row 2 and List3 in row 1 -because the code writes the title, if you want to assign the title manually, ajust slightly the code as stated below-), it will work for any List1&2 number of rows... providing that the product of both lists rows count is less than 1048576 (2^20)... for Excel 2010 and 2007... for 2003 I don't remember, was it 65536?, check it pressing End Shift-down on a empty column.
Regards!
PS: Code adjust for List3 title inputted manually
Where it says:
[pre]
The VBA code just uses 3 named ranges that are defined from the Excel worksheet (select a range, tab Formula, Assign Name -or so, mine it's in spanish, sorry-), List1, List2 and List3.
So defining correctly this ranges (note that List1 and List2 begin in row 2 and List3 in row 1 -because the code writes the title, if you want to assign the title manually, ajust slightly the code as stated below-), it will work for any List1&2 number of rows... providing that the product of both lists rows count is less than 1048576 (2^20)... for Excel 2010 and 2007... for 2003 I don't remember, was it 65536?, check it pressing End Shift-down on a empty column.
Regards!
PS: Code adjust for List3 title inputted manually
Where it says:
[pre]
Code:
I = 1
rng3.Cells(I, 1).Value = "All against all"[/pre]
it should say:
I = 0