Excel – Combination of multiple lists using formula

microsoft-excel-2010

I have already asked a very similar question here, but I now want to do it automatically (using functions etc).

I'm an accountant and have two different sheets of different and/or the same accounts. I need from all those combine one total list (some are in both but some accounts only in one). But I want to do it with vlookup and if functions (combine both). currently they look like this:

sheet 1:                                         sheet2:  
51100 Employee Salaries                          41000 Purchase of Services 
51100 Employee Salaries Total                    41000 Purchase of Services Total
51110 Employee Salaries, Accrual                 51100 Employee Salaries 
51110 Employee Salaries, Accrual Total           51110 Employee Salaries, Accrual Total
52100 Overtime Pay
52100 Overtime Pay Total

I would like to combine them but not have duplicates. So, to join then 2 together would give:

41000 Purchase of Services 
41000 Purchase of Services Total
51100 Employee Salaries 
51100 Employee Salaries //should not exist as it's a duplicate                         
51100 Employee Salaries Total                    
51110 Employee Salaries, Accrual                 
51110 Employee Salaries, Accrual Total           
51110 Employee Salaries, Accrual Total //should not exist as it's a duplicate
52100 Overtime Pay
52100 Overtime Pay Total

can I somehow combine "vlookup" for this and "if" functions-say, do vlookup in each sheet (next column);appears #N/A (means that in sheet1 such record/account doesn't exist) and afterwards insert if funtion in sheet1 where determine: if in cell is #N/A, write account from column in front, if vlokup found something – write vlookup data. And how afterwards can I combine this or do the some solution to have what I want but using formulas?

currently I use

=IF((VLOOKUP(A2;Sheet2!$A$2:$B$49;1;FALSE))=FALSE;A2;(VLOOKUP(A2;Sheet2!$A$2:$B$49;1;FALSE))) 

but false for if doesn't work. Note instead of sheet1 & sheet2 I have sheet2 & sheet3 in formula

Best Answer

The formula you've provided could be written as follows:

=IFERROR(VLOOKUP(A2;Sheet2!$A$2:$B$49;1;FALSE);A2)


Or like this for 2003 and earlier users (as IFERROR was introduced in 2007):

=IF(ISERROR(VLOOKUP(A2;Sheet2!$A$2:$B$49;1;FALSE));A2;
    VLOOKUP(A2;Sheet2!$A$2:$B$49;1;FALSE))


EDIT
The easiest way to create a list containing all unique items from sheets 1 and 2 is to add all the rows from sheet 2 underneath the rows from sheet 1 and then use Remove Duplicates in the Data tab to stip out the duplicate rows.

If you record a macro that does this you can get some starter code to build a solid macro to help make this dynamic moving forward.