SOLVED

Excel Formula Help

New Contributor

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 Replies
best response confirmed by NeedExcelHelp250 (New Contributor)
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.

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!