Excel 2016 telling me I have duplicates in relationship but cant find them

[masplin]

I've tried doing a count and a distinct count form the PP table, but same number. I've tried doing a pivot table in excel and count and seeing if any say 2 but all 1. I know that powerpivot can tell that " Mike" is the same as "Mike" so can it also think things are duplicates, when excel tools and its own distinct count don't e.g. "MBC" = "M B C"? I can't think of any way to find out which ones it thinks are duplicates

I can't remember how to attach the list to the post, but if anyone can give me some ideas to try be much appreciated as now 4,000 items long and can't do it by hand.

Thanks
Mike

 

[Special-K99]

FYI
You cant attach Excel files on this forum.
You have to upload the file to an online storage site then place a link to it on this forum.

 

[Matt Allington]

Each value in a lookup table must be unique - and no blanks. Add your lookup column to a pivot table with your key on rows. Add your key again in values and Change it to count. Then sort descending on the count. You will see your duplicates

 

[masplin]

Hi Matt

That is what i did all "1". That's my usual process for finding duplicates. I also loaded the table in PP and ran a pivot table doing both a count and distinct count of keys but 3,880 for both. I have lots of similar spellings with maybe spaces in different places. I know a space at the beginning excel sees as different but PP thinks it's the same so wondered if could be a similar issue further down the string?

Mike

 

[Matt Allington]

Send me the list of values and I will take a loom

 

[masplin]

most kind. Is there a way to send files through the forum or best to send you a dropbox link?

 

[Matt Allington]

In Mr excel, send me a private message with a Dropbox link. I am about to go to bed - I will look in the morning

 

[masplin]

Hi Matt

False alarm. I don't understand it as I just pasted the names on to my old list that worked, 100 at a time and it worked fine. I then pasted the whole list in instead and again worked fine. Absolutely no idea why it threw the error as seems i was right and there weren't any duplicates. This is excel 2016 so maybe still a bit glitchy.

Thanks for offering to help

Mike