+ Reply to Thread
Results 1 to 4 of 4

Determine if combination of data in Excel B referencing A, is correct

  1. #1
    Registered User
    Join Date
    07-02-2020
    Location
    Singapore
    MS-Off Ver
    2019
    Posts
    2

    Determine if combination of data in Excel B referencing A, is correct

    I am trying to determine if data Excel file B is correct relative to excel file A. Both files have the same headers except for File B, there is a Unique ID & Sub ID Group attached. There can be several rows of B that corresponds to a single row of A. Meaning to say A contains unique rows and B can contain multiple entries of A with a Unique ID for each row. Is there any way to automate the checking process using Excel formulas or VBA? I need to highlight the rows in B that do not correctly match its respective row in A.


    I need a formula to search file A for the combination and not just one row...E.g row 23 on File A may contain the combination I want the formula to see that row 3 on 'sheet 2' contains the same combination and return the yes or formula to see that the combination does not exist in rows 3 through 200 and return a NO and highlight it

    TLDR:
    Is there a way for me to automate checking? If the data combination in each row cannot be found in reference File A, I need to highlight it. This is a HR file, Drilling down from Macro to Micro organization breakdown.
    Attached Files Attached Files
    Last edited by okijuh123; 07-02-2020 at 11:46 PM.

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Determine if combination of data in Excel B referencing A, is correct

    You may use this formula at J17

    =COUNTIFS($A$4:$A$7,C17,$B$4:$B$7,D17,$C$4:$C$7,E17,$D$4:$D$7,F17,$E$4:$E$7,G17,$F$4:$F$7,H17,$G$4:$G$7,I17)

    Note: need to enter ' in I17 , countifs not support true blank cell in criteria.

    Regards.

  3. #3
    Registered User
    Join Date
    07-02-2020
    Location
    Singapore
    MS-Off Ver
    2019
    Posts
    2

    Re: Determine if combination of data in Excel B referencing A, is correct

    Not sure why it is not working on my large main file. The error that comes up is : "You've entered too many arguments for this function." I have placed the two files into one excel file on different sheets. Is my range too large to be handled? I am refercing 30,500 rows.
    Last edited by AliGW; 07-03-2020 at 02:40 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Determine if combination of data in Excel B referencing A, is correct

    Perhaps the following will work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I'm not sure if I need Index, Match or a combination... cross referencing data points
    By deniseb27 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2020, 03:34 PM
  2. Replies: 9
    Last Post: 11-19-2019, 05:28 AM
  3. Replies: 8
    Last Post: 09-10-2015, 03:43 PM
  4. [SOLVED] Determine the best combination of products
    By dec540 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2013, 05:35 AM
  5. Determine combination of cells that equal certain value
    By Mick S in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2013, 01:41 PM
  6. The correct way of referencing the data source for a pivot table... error
    By lgjmac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2013, 03:51 PM
  7. Replies: 19
    Last Post: 01-12-2012, 05:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1