Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
excel merge to create labels in word | Excel Discussion (Misc queries) | |||
how do I use mail merge in excel for word documents | Excel Discussion (Misc queries) | |||
Excel, when column A and B have same name make column C add up in mail merge | Excel Discussion (Misc queries) | |||
why can't I open my Excel data in Excel, but I can as a merge sour | Excel Discussion (Misc queries) |