How can I do a one to many merge in Excel?
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 |
How can I do a one to many merge in Excel?
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)DataImport External DataNew 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 "AL" wrote: 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 |
How can I do a one to many merge in Excel?
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. "AL" wrote: 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 |
How can I do a one to many merge in Excel?
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 "AL" wrote: 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 |
All times are GMT +1. The time now is 05:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com