Excel Formula Help

[NeedExcelHelp250]

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. 

[#2 · Accepted solution]

@NeedExcelHelp250 

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.

[#3]

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!