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
but false for if doesn't work. Note instead of sheet1 & sheet2 I have sheet2 & sheet3 in formula