Forum Discussion
Excel Formula Help
This would require column F to contain values ("Y") and formulas at the same time, which is not possible.
Why not avoid duplicates in column A, for example using Data Validation? That would look a lot neater, and avoid the need to mark multiple instances.
A tricky workaround:
In F2, enter the formula =IF(COUNTIFS(A$1:A1,A2,F$1:F1,"Y"),"Y","") and fill down to the end of the data or even further if you want.
Always mark only the first occurrence of a phone number with Y, overwriting the formula. The formula will then mark all occurrences of the same number downwards.
The alternative would be to use VBA.
This would require column F to contain values ("Y") and formulas at the same time, which is not possible.
Why not avoid duplicates in column A, for example using Data Validation? That would look a lot neater, and avoid the need to mark multiple instances.
A tricky workaround:
In F2, enter the formula =IF(COUNTIFS(A$1:A1,A2,F$1:F1,"Y"),"Y","") and fill down to the end of the data or even further if you want.
Always mark only the first occurrence of a phone number with Y, overwriting the formula. The formula will then mark all occurrences of the same number downwards.
The alternative would be to use VBA.
It seems like it's not going to be possible the way I was envisioning it. I will try some of the other workarounds you suggested and see if I can make it work, or I'll just tweak the spreadsheet to better suit my needs. I appreciate your help!