comparing data within

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Comparing data within 2 spreadsheets

Comparing data within 2 spreadsheets

resolvedResolved · Low Priority · Version 2003

Amit has attended:
Excel Intermediate course

Comparing data within 2 spreadsheets

If you have two sets of data and they contain some similar values, but not in any particular order, how can you find common entries within the 2 sets of data?

Edited on Wed 9 Apr 2008, 08:40

RE: Comparing data within 2 spreadsheets

Hi Amit, Thank you for the post, welcome to the forum, in answer to your question, I would consider using the =EXACT( formula, however this will only check one cell against another, if the two lists are in columns and are next to each other ie Column A and Column B, in Column C place the formula =EXACT(A1,B1) this will compare the value in A1 with the value in B1 if they are the same it will return TRUE if not FALSE, you could then copy the formula down the list to check all your entries (Double click the fill handle). Note: This will also work with text entries.
I realise that I have given an example on just one worksheet but cross worksheet (3D) formulas will work as well.
If this is not what you are looking for, please come back and we can discuss it in more detail, if this has answered your question, please click the Resolved link, best regards Pete

RE: Comparing data within 2 spreadsheets

Hi Pete,

There may be data that is in one column, but not the other and vice versa.

e.g. Column A may have:

aaab
aaad
aaae

and Column B may have:

aaaa
aaab
aaac

How can you check that aaab is in both Column A and B, and find other text that is in both Column A and B?

Thanks
Amit

RE: Comparing data within 2 spreadsheets

Hi Amit, Thanks for your response, I still stick by my original answer, it is the only way that I can think of that will sort out your problem. regards Pete


 

Excel tip:

Quickly insert a function

In Excel 97 and 2000 it was known as the Paste Function dialog box, these days it's known as the Insert Function dialog box. Regardless, one has to choose Insert|Function. or the fx button to open it up. There is, however, a non-mousey way to get hold of the Insert Function dialog box: press Shift+F3 in a blank cell to open the Insert Function dialog.

Press Shift+F3 after a function name and open bracket to open the Function Arguments dialog. For example, type =VLOOKUP( into a cell and press Shift+F3 to obtain a detailed description of VLOOKUP's arguments.

View all Excel hints and tips


Server loaded in 0.08 secs.