Excel help

[The Boosh!]

Hi,

I've a speadsheet with 27k rows in it.

I want to identify who has same names but different DOB in this sheet using a formula.

I can do it using pivot tables but would rather not as I cba to train other people how to use pivots.

Cheers

 

[Tim.]

Maybe you could sort by Name then DOB columns then use some kind of nested IF statement? I'm sure someone will be along shortly to confirm a better way of doing it.

 

[sbridgey]

Could you not use a vlookup?

 

[yellowbelly]

^^ This sort by Name column then DOB.

There is a more complicated formula whereby you can locate how many name matches there are but going by what you need the above is perfect

 

[Budgie]

Easiest way of doing it would be to just filter by contains.

You could split it all out using text to columns. Then sort by surname.

What's the context though- what are you trying to show people?

 

[The Boosh!]

Mike, my OP is seriously dumbed down. Can't really explain the context as there's loads. I just need to be able to identify people who have same names but different dates of birth

 

[Tim.]

Column A is populated with NAME
Column B is populated with DOB

Sort by NAME then DOB

In C2 use =IF(A2=A1,"YES","NO") then copy formula down
(This tells you whether the NAME is repeated)

In D2 use =IF(B2=B1,"YES","NO") then copy formula down
(This tells you whether the DOB is repeated)

Apply Filter and filter column C to show only "YES" and column D to show only "NO"
(This should show you all people who have the same NAME but different DOB)

 

[Budgie]

^ that'll just about do it.

 

[Tim.]

Just don't say their title, first, middle & last names are each in separate columns

 

[Budgie]

Concatenate, if they are.

 

[The Boosh!]

Thanks for the help Tim. Although that works a treat this is a regular update thing.

I think I've solved it by using a hidden pivot table in combination with a vlookup. Have just stuck a simple bit of VBA in there that refreshes the pivot everytime it's saved.