Compare 2 excel sheet and combine uniq data

rina - Jun 11, 2009 at 02:14 AM
 kumar - Aug 31, 2011 at 07:51 AM

I have 2 excel sheets , A and B. There are redundant data in both sheets as well as uniq.
All I want is to combine both with no redundant records. (A+B-Redundant= Uniq complete data)


3 responses

Without going into a lot of details, there are several ways to combine specific data from numerous sources of RAW DATA. The easiest way (once you understand how) would be to use Microsoft Access. Here you will need to use the 'help options' for the specific process; but what your will be doing is "mapping" two or more documents together which share 1 categories of redundant information (i.e. NAME, PART NUMBER, etc.).

For example, if each worksheet has a persons NAME, along with another columns or information. you would map the NAME in each worksheet, then select the additional fields you want captured in your output. Besides the Access Help engine, you can repost your help request here under 'mapping & Linking).

If you want to stay in Excel, try using a Index Match statement.


Type this formula in a blank cell within the row/entry you are comparing. This formula will look in SheetA through all data within column A. If data is matched to the identifier ($A2), it will then copy the data entered in column C (number or text). #N/A is reported if no match is found.


Part1 $2 A xxx
Part2 $3 B yyy
Part2 $4 V zzz

Sheet B

Part1 50 MN type your formula
Part2 60 WI type your formula
Part2 70 MO type your formula

The cell with the formula will collect "A","B" and ,'V" from the first sheet.
Copy and change the formula to collect data from which columns you want (skip the redundant/superfluous ones).

For best results, lock in your ‘identifier’ cell (in this case $A2)

To utilize a formula solution, data within sheets needs to be organized the same way because you are defining which 'columns' to match and pull information from.
icicici Posts 3 Registration date Monday July 19, 2010 Status Member Last seen July 20, 2010
Jul 20, 2010 at 04:50 AM
Thanks a lot
Many, many thanks! Excellent solution to a difficult problem.
FINALLY!!! Thank you so much! I have been searching all morning, and this is the easiest thing that has actually worked and been easy to understand! You made my day!
Thanks, I can use it to reconcile my bank account, let me try it.
by using VLOOKUP also you can do it.
How can I match data across two excel sheets. I have two excel sheets containing similar data but in different order.

I want to match all the data in sheet 2 with sheet 1 and return the row values of sheet 2 next to the row values of sheet 1.

Sheet 1 contains 19 columns of data and Sheet 2 contains more then 19 columns.
With 100s of row of data.

The formula must meet 3 conditions before it returns an entry.

1. If column B2 of sheet 1 = B2 (or matching any row down of sheet 2 then check
2. If column R2 of sheet 1 = R2 (or matching any row down of sheet 2 then check
3. If column J2 of sheet 1 = J2 of sheet 2 (or matching any row down of sheet 2).

Column B will have duplicate values in both sheets.

If all these conditions are matched then the formula must execute the value in the entire row of sheet 2 next to the matching row of sheet 1 in cell U2:AM2 for each matching row down.
Forgot to add in, I cant copy and paste and then filter 'uniq' coz the total data is more 68000, which is more than 65K rows per sheet.
If can I need help of using the IF(...) command..
thanks.. :-)
how to compare alpha numeric data with numeric data, in order to find similer numeric data