Merge two excel files??

[jewilson2]

Good morning all...

I have two excel files that I need to merge, creating a 3rd file with all the possible combinations from file one and file two.

IE. File one has 100 records and file two has 500 records. I need to create a file three that repeats the records in file one for every record in file two. So I will have 500 entries for file 1/record 1, 500 entries for file 1/record 2, etc etc. Finally ending up with 50000 records.

Is there an easy way to do this without all the copy and pasting?

Thanks in advance,
JW

 

[NilsBevaart]

This sounds more like a database problem. The easiest way I know is to import both sheets in an Access database, link the tables (in neccesary, create a dummy field to link them) and export the resulting query back to Excel.

Remember that there are limits to the number of rows in an Excel sheet.

Nils Bevaart

 

[jewilson2]

I thought of Access too, but couldn't think of a way to make a "dummy" common field to accomplish what I'm after.

Essentially, I would have to repeat every record from file 1 for every record in file 2?

I am having total brain fade this morning apparently.

Thanks again,
JW

 

[jewilson2]

OK...was finally able to get this to work using Access.

All i needed to do was import the two spreadsheets into two separate tables in Access.
Then write a make table query selecting all the fields from table one and table two that I wanted. Then run the query and it repeated each record from file one for each record in file two.

Worked like a champ so far.

Thanks,
JW