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.
- Hans,
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!
