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
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
User Ryan-JaguarComm had a comment
ReplyDeletehttp://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
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.
ReplyDeleteHi TheFirstOneLast:
ReplyDeleteLet 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
Yogi, sorry ... I saw only the first sheet in your solution.
ReplyDeleteHi TheFirstOneLast:
ReplyDeleteNo problem.
Cheers!
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
So, Yogi, why does the first IF seem incomplete?
ReplyDeleteHi TheFirstOneLast:
ReplyDeleteYou 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
Yogi, sorry for all the confusion. The spreadsheet I'm looking at is DuplicateswithcolumnD. The formula that looks incomplete (the IF) is:
ReplyDelete=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
This is great. Is it possible to report what row the duplicate exists?
ReplyDeleteHi Michael:
ReplyDeleteYES ... 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
This comment has been removed by the author.
DeleteThanks for the quick reply. So I only want to check for duplicates in only 1 column (column E for example):
ReplyDelete=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".
Hi Michael:
ReplyDeleteThanks 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
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?
DeleteHi Michael:
ReplyDeleteTry 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
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