Case Sensitive Vlookup - remove duplicates

[muejl1]

My first post...but I use this site all the time to figure out Excel issues...thanks for all the tips and help!

I'm struggling with a case sensitive Vlookup....as I look for duplicate data I really hope someone can help:

I have two sheets with data:
1. Sheet1
2. Sheet2

On Sheet1, I have case sensitive data in F2:F18468
On Sheet2, I have case sensitive data in A2:A9391

The goal is to locate duplicates found in Sheet1 and then remove them....leaving me with values not found in Sheet2. I believe this has to be done within an array, so I added an additional column on Sheet1 G2:G18468. I put the value of 1 in each cell....with the idea that if I wrote the formula correctly...then 1 would show up along side the duplicates, and N/A would indicate a unique value.

Thanks for any feedback, suggestions or help.

 

[JoeMo]

You can use an array version of INDEX-MATCH-EXACT to provide case sensitivity. See an example here:

https://exceljet.net/formula/exact-match-lookup-with-index-and-match

 

[muejl1]

Thank you...but I was still unable to get this to work. I did move all the data on one sheet and created this formula:
{=INDEX(data,MATCH(TRUE,EXACT(D2,I2:I18468),0),1)}

D column has potential duplicates that reside in the I column. I put a "1" instead of a 3 b/c I was only searching the one column. I do get the exclamation point that states "This formula contains unrecognized text".

Any additional help would be greatly appreciated.
Erik

 

[JoeMo]

Thank you...but I was still unable to get this to work. I did move all the data on one sheet and created this formula:
{=INDEX(data,MATCH(TRUE,EXACT(D2,I2:I18468),0),1)}

D column has potential duplicates that reside in the I column. I put a "1" instead of a 3 b/c I was only searching the one column. I do get the exclamation point that states "This formula contains unrecognized text".

Any additional help would be greatly appreciated.
Erik

You only need the match portion of this. Try entering this with ctrl+shift+enter:

=IF(ISNA(MATCH(TRUE,EXACT(D2,I2:I18468),0)),"","Duplicate")

and copy down. This will identify case-equivalent duplicates by returning "Duplicate".

 

[muejl1]

Hey Joe...thanks again for the formula to try.....I did it and every single cell returns "Duplicate". I did a find on a couple of the values...and made sure the search was set to case sensitive.....and each one I tried only returned one instance, so there those were not duplicates. Hmmm......if you have any other ideas, I'd love to give it a try.

 

[JoeMo]

Hey Joe...thanks again for the formula to try.....I did it and every single cell returns "Duplicate". I did a find on a couple of the values...and made sure the search was set to case sensitive.....and each one I tried only returned one instance, so there those were not duplicates. Hmmm......if you have any other ideas, I'd love to give it a try.

Are you entering the formula with ctrl+shift+enter (CSE)? I also notice that the formula I posted didn't have the col I references locked so when you copy down you will change those references. The formula below, when confirmed with CSE and copied down works for me.

=IF(ISNA(MATCH(TRUE,EXACT(D2,$I$2:$I$18468),0)),"","Duplicate")

confirm with CSE

EDIT: after you copy down, be sure your calculation is set to automatic.

 

[muejl1]

Joe...that did the trick! Thanks very much for all of your help! This site rocks!