Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Excel formula

hello,

having an issue... have two different spreadsheets.  I need to compare the two spreadsheets to filter only same columns.  In other words, which rows are the same in both spreadsheets.  is there a simple formula that could be used or a free program that would do that?

Thanks!
0
odddball
Asked:
odddball
  • 3
  • 3
2 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
1) Are these two sheets in the same workbook or in different workbooks?
2) Do you only want to see/highlight data where the entire row is identical in both workbooks?
3) Where do you want to see the common data?  In another workbook or highlighting one or both rows in its source sheet?

As always, an example is very helpful here.

Regards,
-Glenn
0
 
odddballAuthor Commented:
Hi, thank you for your reply.  they are in the same workbook.  Highlighting would be great.  If possible in another workbook would be ideal!!
0
 
Glenn RayExcel VBA DeveloperCommented:
Here's a sort-of quick-and-dirty solution; anything more elaborate will probably require VBA.

This example has two source sheets - Sheet1 and Sheet2 - and the data ranges from columns A through H.

1) Create a new sheet with the same header/column layout as your source sheets.  For example, I'm naming it "Common"
2) Insert the following formula in cell A2 and copy across and down as many rows as in Sheet1.
=IFERROR(VLOOKUP(Sheet1!A2,Sheet2!A:$H,1,FALSE),"")
You will see all the common values in both sheets, regardless of record location.

3) Add a new column to the right, label the top cell "Duplicate" and add this formula in row 2 and copy down:
=IF(SUMPRODUCT(--((A2:H2)=""))=0,"Yes","No")
This will show "Yes" or "No" if the row has full matches and is therefore a duplicate in both Sheet1 and Sheet2

4) Turn on data filtering and filter on the "Yes" values to simplify the sheet.

I've attached an example file that demonstrates this.  Let me know if you think this method will suffice or if you need an automated (VBA) solution.

Regards,
-Glenn
EE-DuplicateRowsAcrossSheets.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Glenn RayExcel VBA DeveloperCommented:
Hi,

Have you had a chance to review my solution provided above?  If so and you still have questions, please let me know.  Otherwise, can you please properly close this question by clicking the "Accept this solution" link above my post above?

With Thanks,
Glenn
0
 
odddballAuthor Commented:
thank you.
0
 
odddballAuthor Commented:
Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now