CODEWITHSUNDEEP
excel
jdids
jdids

Reputation: 581

Excel function that identifies consecutive values

I have some data formatted like this

       A               B                C

     Phone           Name              Zip
1    111-222-3333    Steve Harvey      12345
2    111-222-3333    Steve Harvey      12354
3    222-333-4444    Barry Badgernaff  65423
4    222-333-4444    Barry Badgernaff  65432
5    333-444-5555    Jim Bob           36521
6    444-555-6666    Mickey Mouse      36541
7    666-777-8888    Donald Duck       39847
8    666-777-8888    Donald Duck       39874

What I'm looking to do is identify consecutive duplicate names. If there are duplicates, I want them ignored so I can filter them out

For example

       A               B                C        D

     Phone           Name              Zip     Result
1    111-222-3333    Steve Harvey      12345   Duplicate
2    111-222-3333    Steve Harvey      12354   Duplicate
3    222-333-4444    Barry Badgernaff  65423   Duplicate
4    222-333-4444    Barry Badgernaff  65432   Duplicate
5    333-444-5555    Jim Bob           36521   Not A Duplicate
6    444-555-6666    Mickey Mouse      36541   Not A Duplicate
7    666-777-8888    Donald Duck       39847   Duplicate
8    666-777-8888    Donald Duck       39874   Duplicate

What I've done is tried identifying these using =IF(B2=B1, "True", "False) but it slightly only does what I'm looking for and I end up getting false positives

What else can I try? I don't want to remove the data, just have a cell say if there are consecutive names in the data.

Upvotes: 0

Views: 89

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

Your formula only checks the next row, but you should check the previous one as well.

Try this in B2 : =IF(OR(B2=B1, B2=B3), "True", "False")

Upvotes: 1

Related Questions