Forum Discussion
Excel Formula Help
Hello,
I am trying to create a spreadsheet to track scam numbers we receive on our work phone. I am also trying to mark whether or not something has been reported to our telecom department to be blocked.
I have gotten everything formatted the way I want so far, but what I am hoping to do is write a formula where in column F, if someone marks "Y" for yes, it will search through column A and find all recurrences of that same phone number and mark a "Y" in their respective column F to indicate that, so all instances of that number show that they were reported. say that A2 is 1234567890 and we mark "Y" in F2, it will search through the entire A column and find 1234567890 in A487 and mark F487 column with a Y.
Hopefully this makes sense. I appreciate any and all help.
Thank you.
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.
2 Replies
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!
