Join 2 lists/tables in Excel?

Skeeter · 2 Dec 2008 at 09:49

Hi

I have 2 tables in Excel, which contain different data but in the same column layout. Im looking for a way to join the 2 lists together into a single long list. I dont want any combining or calculations, just to litterally take the 2 lists and stick one onto the end of the other, for example:

Code:

Table 1

A   B   C
a1  b1 
a2      c2


Table 2

A   B   C
a5     
a6  b6  c6


And turn them into a table that looks like

A   B   C
a1  b1
a2      c2
a5
a6  b6  c6

It needs to be using standard Excel functions.

Cheers

Dolph · 2 Dec 2008 at 10:29

Does it have to be dynamic?

If it doesn't, use the collate function to merge the two tables.

if it's dynamic, it's a bit trickier as the two tables contain similar data.

Do they contain any duplicates in the A column?

You could do something with vlookup and if statements to check if the columns were populated on a third copy of the table eg if(sheet2.B1 = "", vlookup(sheet1.A1, sheet3.A1, sheet3.B1), vlookup(sheet1.A1, sheet2.A1, sheet2.B1) for column B, which would check for an empty B1 against A1, but would require you to start with all the A columns defined in both tables. (eg a1, a2, a3 there even if blank), and would then update the data based on the two other tables.

However, someone else will probably be along with a much better solution in a minute, or you could just use a macro to copy, paste and sort the data into the third table.

Skeeter · 2 Dec 2008 at 12:24

It will contain duplicates yes. Its for an audit. One spreadsheet contains everuthing someone has viewed in a month on one of our systems, and the other is everything they have changed. It needs to be combined into a single list that shows everything they have viewed and done. Ive got a template setup that takes the 2 query results, and extracts only the columns I need into 2 other sheets. Id like to combine the 2 sheets to one to allow me to then copy that to the template sheet we have that needs to be signed off by managers. For the effort involved I think im just going to copy and paste the 2 tables together and then sort by date.

londonbairn · 2 Dec 2008 at 12:29

it can be done using standard functions, not using IFs/VLOOKUPS but by using arrays. I created a sheet like this for someone before because they did not want VBA (arguably in a case like this using VBA is better). Dont have the sheet at hand (at home) so cannot check.

Why does it have to use standard functions? A situation like this I would prefer to use VBA or Access, but your example is quite simplistic, is the effort worth it for what essentially is a copy and paste?

Skeeter · 2 Dec 2008 at 12:37

Its a work machine, so Im restricted on what Im able to do. Im sure I can do VBA and other stuff as im in IT and have admin rights, but if the template gets used by someone else they need to be able to do it on systems which have even simple macros locked down.