Combining two tables to produce new table with unique records

Hi there

I am new to the forum but hope someone can help.

I have two tables which share a single column field. I need to merge or combine the tables so every unique combination of each of the two original tables is a new row.
I haved attached an example I have done manually, whereby table 1 and table 2 both share the 'Trip' field and the combined table gives one record for each of the unique combinations.test.xls

I am thinking the only way to do this would be with a FOR EACH loop in VBA but I am unsure how to go about it (I am assuming there are no inbuilt features in excel that will do it.

Any suggestions of help would be most appreciated

cheers

as an option

Please Login or Register  to view this content.

Nilem,
Very nice Scripting Dictionary example.

Thank you so much Nilem and WHER, both work well, I will probably use the VBA solution, but it is very interesting to see how it is done with formulas.
I was really stuck on this so a great help.

Nilem,
this code works beautifully for examples where the variable x is less than about 22000 cells in length, (y is at 6320 cells), anything greater than that and I am getting a Run-time error '1004': Application-defined or object-defined error. I am assuming this is a problem with array size? does excel have a maximum array size that I am hitting? I have modified the code slightly to resize the array as it does not need to factor the x and y sizes, rather just y * the longest repetition of x (which is calculated in cell a1)

code:

Please Login or Register  to view this content.

I think I may have got to the bottom of this. I was running the code in excel 2003 which appears like array formulas have a limit of 65000. When I put the code into excel 2007 it worked fine as I guess there are much larger limits on array formulas?

please let me know if this looks right?

Hi Thales
Probably number of rows in the output array more than 65,536. Therefore, an error occurs when an array is transferred to the worksheet in Excel2003 on this line

Please Login or Register  to view this content.

Use this code in Excel2007(2010). Or we can change the code so that the array will be transferred in two columns (if necessary).