Excel – Comparison of accounts in Excel windows 7 between two different sheets and combining in one list

microsoft-excel-2010

'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) – with vlookup I tried – I can see what's missing – but how to combine?

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

Best Answer

Depending on how often you are doing this, and how much work is involved, you could just copy each set of data and paste them underneath each other to create a big list, full of duplicates as well.

Then, click on Data tab, highlight the entire list and select Remove Duplicates. Then, with the list still highlighted, sort it (see image below)

enter image description here

Please note, this should also work where you have data in multiple columns (in most cases)