There is a new sheet on your sample spreadsheet called MK.Idea. There you will find this formula:
=ARRAYFORMULA(COUNTIF(UNIQUE(SPLIT(FLATTEN(Sheet1!B1:G1&"|"&Sheet1!B2:G),"|")),"Grass"))
That uses UNIQUE and a SPLIT(Flatten technique to isolate the "grass" values that are duplicates in a given column
Answer from MattKing on Stack OverflowIn order to find duplicates in a spreadsheet I have been using the formulas =IF((COUNTIF(P:P, C1)>1), “duplicate”, “nonduplicate”)
What I found is that it was catching most duplicates but not all.
I looked at a pair that should have been tagged as duplicates to try to see what was different about them, but they both were the same font, size, and color, were both not bolded, italicized, or underlined, and neither had extra spaces at the end.
I started fiddling around with the formula to see what would happen. I changed it from >1 to >.5 and when I checked some entries I knew were duplicates it marked them as such, and I didn’t find any that it marked as duplicates that were actually not.
So what I’m wondering is, What does the >1 mean? Why did it work when I lowered the 1 to .5? And will >.5 correctly identify duplicates and leave out different entries?
Thank you! Edit: adjusted format of post for clarity
What Is the Formula to Highlight Duplicates in Google Sheets?
How Do I Find Duplicates in Two Columns in Google Sheets?
How Do I Compare Different Google Spreadsheets for Duplicates?
Solution found -
Thanks for the solution
u/AdministrativeGift15
Hello I have a question regarding the Countifs formula in conditional formatting. As you can see the inmage below I have highlight duplicates based on the Link. In conditional formatting I have used the =COUNTIF(B:B; B1)>1
To make the sheet show duplicates. However I would like for the conditional formatting to work like this instead -If the Link is duplicated the color should be as shown in the picture. If however a special role in Column F (Risk/Borrower) is shown like here the color should change, but only on the rows where there is a duplicate.
I have tried using the method shown in Support.Google.com
COUNTIFS(A1:A10, ">20", B1:B10, "<30")
And then change it to this - =COUNTIFS(B:B; B1 ">1"; F:F; F1 "Risk")
To make the once where both criterias are met, they should be hightlighed.
If You know of a way to delete one of the rows where both criterias are met, That would be a life saver, Right now I have around 90,000 rows I need to manually look through.
I have a column with ID's, the column constantly gets new ID's added to it and I need a count of how many duplicate ID's are in the column. So, something like this =(COUNTIF(Range,ID)>1) is not an option since I'm not interested in knowing what ID is a duplicate I just want to know how many duplicates in total are in the column.
Example:
| 31430926 |
|---|
| 31430891 |
| 39041686 |
| 39041686 |
| 39041686 |
| 30422494 |
| 31430911 |
| 30422550 |
| 30422550 |
In this example, the formula or script should return a value of 3 since you have 3x 39041686 & 2x 30422550.