Excel formula

Posted on 2014-08-03
Last Modified: 2014-09-17

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?

Question by:odddball
    LVL 27

    Assisted Solution

    by:Glenn Ray
    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.


    Author Comment

    Hi, thank you for your reply.  they are in the same workbook.  Highlighting would be great.  If possible in another workbook would be ideal!!
    LVL 27

    Accepted Solution

    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.
    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:
    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.

    LVL 27

    Expert Comment

    by:Glenn Ray

    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,

    Author Comment

    thank you.

    Author Comment

    Thank you

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now