+ Reply to Thread
Results 1 to 3 of 3

Comparing rows (not columns)

  1. 02-27-2024, 02:52 PM #1
    wildkid
    wildkid is offline
    Registered User
    Join Date
    12-01-2023
    Location
    San Francisco, CA
    MS-Off Ver
    16.79
    Posts
    10

    Comparing rows (not columns)

    Hi! I am trying to do an efficient inter-related reliability check. I have two coders who each examined the same documents and what I'm trying to do is compare their coding. I have 1000's of documents (and variables) to check so would love to find a way to efficiently compare rows. A mock-up is attached.

    As seen on the Raw tab - I have 2 coders who each examined 3 documents. For the 5 variables, they are each supposed to put a code. My goal is to see where they coded something the same, and where they coded something differently so that I can go back and check the documents myself, and agree on a final code.

    As seen on the Final tab - what I want is a single line for each document, and using a formula find the two documents of the same name in the Raw tab (I assume using a helper column), and if the two answers for each variable (can also just be a drag across the columns) match return a TRUE or ideally the 'matched' value; or it will return a FALSE. For the FALSE, those would be the only ones I would then need to check.

    Again, given the number of variables and documents to review it would be great to find an efficient way to check codes. Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-27-2024, 03:22 PM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Comparing rows (not columns)

    not sure if you have 365 version i suspect so , as i think 16.xx is the version maybe wrong
    then
    =IF(COUNTA(UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),2)))=1, UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),2)),FALSE)

    you cannot copy across - need to change the 2 for 3 then 4 etc - to choose the correct column to show in the filter set

    =IF(COUNTA(UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),3)))=1, UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),3)),FALSE)

    although if a load of variables - then we could probably use COLUMN() -xxx
    =IF(COUNTA(UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),COLUMN()-4)))=1, UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),COLUMN()-4)),FALSE)

    you can see in row10



    see column M,N,O,P,Q

    I used your helper column - BUT could be done without and a much longer formula

    also not sure how it will work on a large amount of data

    you could make the formula samller using a LET()

    =LET(VarUsed,UNIQUE(CHOOSECOLS(FILTER(Raw!$F$2:$K$7,Raw!$F$2:$F$7=Final!$E9),COLUMN()-4)),IF(COUNTA(VarUsed)=1,VarUsed,FALSE))
    Attached Files Attached Files
    Last edited by etaf; 02-27-2024 at 03:38 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 02-27-2024, 04:00 PM #3
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Comparing rows (not columns)

    Why not use a table to filter your variables as you wish?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Comparing individual rows across columns
    By marciokoko in forum Excel General
    Replies: 3
    Last Post: 10-01-2019, 09:02 PM
  2. Need help with CountIF when counting rows of 2 columns and comparing a 3rd columns value
    By neoncorey in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2013, 02:03 PM
  3. Comparing two columns-multiple rows
    By chs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-31-2012, 05:56 PM
  4. Comparing two columns data and inserting rows
    By scyllanbay in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-11-2012, 11:56 AM
  5. Comparing Duplicate Columns/Rows
    By mindstate in forum Excel General
    Replies: 3
    Last Post: 10-25-2011, 03:12 PM
  6. Rows deletion by comparing three columns
    By rajsaro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2011, 08:42 AM
  7. comparing rows and columns name
    By ananthakumar.e in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-07-2008, 04:31 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1