How can I do a one to many merge in Excel?

[Guest]

Hi, I am trying to merge two sets of data into one using a one to many merge,
for example:
In column A, I have In column B, I have
100 101
200 102
300 103

my results column will look like:
100101
100102
100103
200101
200102
200103
300101
300102
300103

Any help would be appreciated.

AL

 

[Guest]

Try using MS Query:

With your lists in 2 named ranges, with column headings.
I used rngValList1 for the first list and rngValList2 for the second

List
100
200
300

List
101
102
103

Make sure the file is saved so Excel can find it, then:
1)Data>Import External Data>New Database Query

Databases: Excel Files

Browse to the file, pick the data ranges to import.
(in my case the file is called Lists)
--->Accept defaults until the next step.

At The last screen select the View data/Edit The Query option.

Click the [SQL] button

Since you'll only have 2 lists, each containing one field, combine those 2
fields with an ampersand (&) and set the new field name.

Example:
SELECT
rngValList1.List&rngValList2.List as Combo
FROM
`C:\ExcelQueries\Lists`.rngValList1 rngValList1,
`C:\ExcelQueries\Lists`.rngValList2 rngValList2

When you click "Return data to Excel" you should get the following:
Combo
100101
200101
300101
100102
200102
300102
100103
200103
300103

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

You want to "concatenate" the columns. If A1 is 100 and B1 is 101, in C1
type =A1&B1. Then copy the formula down as far as necessary. Finally, copy
the entire range of cells in column C, and Paste special, values--this will
eliminate the formula. You can then delete columns A and B.

 

[Guest]

Hi Ron,

I tried it, but it didn't work. I got an error message that says "This data
source contains no visible tables" right after I did the browse to the file
step.

AL