Forum Discussion
Check if text exists in rows and columns
Hello all,
I'm doing an overhaul of an Exchange environment of one of my customers. I have listed all needed AD properties, SMTP, smtp and SIP adresses. I want the users to use a new UPN.
In columns L through Q I have put all the current aliasses for these users (some of them have 1 but there are people with 6 aliasses). In column R I have created a new UPN per user. Now I have to find out whether or not my newly created UPN is unique in all the aliases.
I have tried several functions but none of them seems to work as I think they should work.😛
So basically does the value of R2 exists in L2 through Q1094? Their all text fields.
How to go about htis?
=SUMPRODUCT(--($L$2:$Q$1094=R2))
If there is a newly created UPN in column R the expected result is 0. This means that the newly created UPN doesn't exist in columns L:Q.
If one of the already existing UPN is used as newly created UPN in column R the expected result is 1. This UPN should only appear once in columns L:Q.
The empty cells don't affect the result of the formula as they are not equal to the newly created UPN.
What formula is in cell R2? I can't answer as i don't know which formula is in your spreadsheet in column R. In my example i choose unique new UPN with the exception of "aliasL" in cell R13.
3 Replies
=COUNT(SEARCH(R2,$L$2:$Q$1094))
Maybe with this formula. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Thanks a lot for your swift reply OliverScheurich.
I see in the example pdf that all the fields are filled with data however I have several cells which are empty. Does this affect the COUNT? Because the only values I get in return is 1 or 0, while I expected that every value would be there at least once.
Or has it to do with the fact that R2 is a formula to create the new UPN?
With kind regards=SUMPRODUCT(--($L$2:$Q$1094=R2))
If there is a newly created UPN in column R the expected result is 0. This means that the newly created UPN doesn't exist in columns L:Q.
If one of the already existing UPN is used as newly created UPN in column R the expected result is 1. This UPN should only appear once in columns L:Q.
The empty cells don't affect the result of the formula as they are not equal to the newly created UPN.
What formula is in cell R2? I can't answer as i don't know which formula is in your spreadsheet in column R. In my example i choose unique new UPN with the exception of "aliasL" in cell R13.
