Friday, November 11, 2011

yogi_Highlight Rows That Have Duplicate Content In A Specified Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Ryan-JaguarComm said:
Now I've got a new mission.
Make the sheet tell me if Duplicate data exists and if it does, highlight both entries in some way so it stands out.
That way we know if duplicate entries on devices are being made. 
Till building the structure of these Functions click, I'm going to have a heck of a time. 

Unfortunately I can't share the data. I can however try to better explain.
There is 8 columns of data. A-H
Row Data is to be kept together, each row is a form submitted entry.
The data is being collected from 4 different towns. Which leaves me currently with 5 sheets.
1 Master (Form Entry)
4 Cities sheets, simply pulling  filtered data from the master (as described above)
To take the Cities sheets a step further, I want to look at all the Filtered data, and have it highlight the cell, or the row of data based on data in one column that matches.  
ex: If Row 5 and row 7 have the same number in column D, show them both in red. Don't matter if its Colored Text or background as long as it stands out.

----------------------------------------------
In the following proposed solution to the problem, I highlight the duplicates by coloring the background of duplicated rows in an adjacent column






16 comments:

  1. User Ryan-JaguarComm had a comment
    http://www.google.com/support/forum/p/Google+Docs/thread?tid=0c3b930bd6281061&hl=en
    If column D has Rows 1,2,3 all containing 33 and rows 4,5,6,7 all have 44, Column I shows Red next to each row (all 7) and then has a 3 in rows 1,2,3 for "3 33's" and rows 4-7 show 4 in red, for "4 44's" in Column D. Following me?


    I have added a sheet DuplicatesInColDWithNoOfDups to do that.
    Is this what you are looking for Ryan-JaguarComm?

    Cheers!
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  2. Yogi, I like your clever, condensed solution, but can you help me understand it better? 1st) the "IF" (inside the ARRAYFORMULA) appears to be incomplete: where is the "false" condition? When A2:A = is "true" the COUNTIF will execute. A2:A will always be true for this data, but, syntactically, doesn't the "IF" require something to be coded there? 2nd) the COUNTIF scans the range of rows COUNTing occurrences of the values in the same range of rows. That's a clever property of the ARRAYFORMULA/COUNTIF combination. Then achieving a logical value, true/false, based on a count of 1 or more than 1 is a neat condensing of results. Thanx.

    ReplyDelete
  3. Hi TheFirstOneLast:

    Let me first ask you ... I have 3 sheets in the solution ... so which sheet are you referring to? once you tell me that then we can dissect the use of the IF function there.

    Cheers!
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  4. Yogi, sorry ... I saw only the first sheet in your solution.

    ReplyDelete
  5. Hi TheFirstOneLast:

    No problem.

    Cheers!
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  6. So, Yogi, why does the first IF seem incomplete?

    ReplyDelete
  7. Hi TheFirstOneLast:

    You still did not identify which sheet in my blog post you are referring to.

    In any event, the syntax of the IF function is ...
    =IF(test,then_value,otherwise_value)

    and if you check you will find that the use of IF formulation in my blog posts is consistent with the syntax of the IF function.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  8. Yogi, sorry for all the confusion. The spreadsheet I'm looking at is DuplicateswithcolumnD. The formula that looks incomplete (the IF) is:

    =ArrayFormula(if(A2:A="","",countif(D2:D,D2:D)>1))

    A2:A= --- is the condition, right?
    COUNTIF(D2...) ---is the "true" action, right?

    Where is the "else" false action?

    Thanx

    ReplyDelete
  9. This is great. Is it possible to report what row the duplicate exists?

    ReplyDelete
  10. Hi Michael:

    YES ... it is possible to report what row the duplicate exists in. Please give me reference to the sheet in my blog post, or post your own sample data, tell me where (in which cell) should the result be posted and your expected result with reference to the data that would be operated on.

    Cheers!
    Yogi

    ReplyDelete
  11. Thanks for the quick reply. So I only want to check for duplicates in only 1 column (column E for example):

    =ArrayFormula(if(E2:E="","",countif(E2:E,E2:E)>1))

    Your data has duplicates in E2, E6 and E8. Could cell I2 display "6, 8", cell I6 display "2, 8" and cell E8 display "2, 6".

    ReplyDelete
  12. Hi Michael:

    Thanks for a clear description of the problem ...

    because I think that your question stands on its own, I present a solution to the problem in the following new blog post:

    yogi_Delineate in The Active Cell Row Numbers Of Rows In The Active Column In Which The Active Cell Is Duplicated
    http://yogi--anand-consulting.blogspot.com/2012/11/yogidelineate-in-active-cell-row.html

    Please check it out and let me know how this works out for you.

    Cheers!
    Yogi

    ReplyDelete
    Replies
    1. Excellent. The solution works perfectly! :) I have one last question. Is it possible to highlight the text of any rows with duplicates in red? Either make the text of the entire row red or the background of the cells?

      Delete
  13. Hi Michael:

    Try combining this solution with the highligting duplicates solution in this blog post and let me know how it goes ... and then let us take it from there.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
    Replies
    1. Hi Yogi, it looks like the "conditional formating" just highlights that one specific cell. I'm looking to highlight the entire row if there is a duplicate. Thoughts?

      Delete