Flagging matching/unmatching Customer date of births

[MarkJohn51]

Hi all,
I was wondering can anyone help with my problem?.

I want to create a field "DOB Check" to flag where there are different Date of Births, for a Customer (all Customers have corresponding Duplicate rows)

As you can see from my data, there are different Dates of Births, for some Customers:

If all of the Date of Births are matching for a Customer - I want to flag these as a "1", on all rows, for that Customer
If any of the Date of Births are not matching for a Customer - I want to flag these as a "0" on all rows, for that Customer

Thanks guys.

[Pete_UK]

You can use this formula in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

then copy it down as required.

Hope this helps.

Pete

[MarkJohn51]

You can use this formula in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

then copy it down as required.

Hope this helps.

Pete

Thanks Pete. Works fine with Duplicate Customer Number rows, though if I have just one row for a Customer with just one Date of Birth (see updated sheet with a unique Customer)...well I want to flag these unique Customer rows as a Null or a 0. What do I need to change?

[bebo021999]

Try :
=IF(COUNTIF(A:A,A2)=1,"",YOUR CURRENT FORMULA)

[Pete_UK]

You could use this in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*(COUNTIF(A:A,A2)<>1)

then copy down.

Hope this helps.

Pete

[MarkJohn51]

You could use this in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*(COUNTIF(A:A,A2)<>1)

then copy down.

Hope this helps.

Pete

That has worked. Thanks.

I now need to flag where a Customer Number, is not matching a Customer Name "Cust Num Error"
See attachment example.

On a Customer Name level:
1 is where the Customer duplicate Names will have matching duplicate Cust Numbers
0 is where the Customer duplicate Names do not have matching duplicate Cust Numbers
Anything other scenario, should be Null

I tried using formula above, but it is just giving me all 1's
=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

Thanks in advance

[MarkJohn51]

You can use this formula in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

then copy it down as required.

Hope this helps.

Pete

What do I need to change in the formula, so that it excludes Excel looking at dates with the following values (not date formats):

9999-12-31 and
0001-01-01 00:00:00.0

I dont want to filter out or delete these.

Need to exclude just those 2 dates values...as most of my data has "normal" dates such as 2000-12-10 and 1973-11-05

[JeteMc]

As those are the only two values that need to be excluded please try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Asad Mir]

You can use this formula in C2:

=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

then copy it down as required.

Hope this helps.

Pete

Hi!
Can't we use this formula, or why can't we use this formula?
=(COUNTIF(B:B,B2)=COUNTIF(A:A,A2))*1

[Pete_UK]

Don't try to hijack someone else's thread - start a new thread of your own.

Pete

[Asad Mir]

Just for my information!

1. Is it against the rules to ask a question in someone else's thread relating to the same topic under discussion?
I don't think so...

[AliGW]

Administrative Note:

Yes, it is against the rules.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[Richard Buttrey]

Just for my information!

1. Is it against the rules to ask a question in someone else's thread relating to the same topic under discussion?
I don't think so...

Since you've reported Pete's response let me try to help.
The simple answer to your question is Yes. Our rule 4 says asking for help in an existing thread is not permitted.

I'll put your uncertainty down to a matter of the rule being 'lost in translation'.
Your post was a question and reasonably enough was therefore treated as a request about a similar question you had in connection with your workbook, thereby in breach of the rule.

If you were merely intending to offer another suggestion then you should have simply said something like,
"Another option is to use ...blah blah", or "Why not try..blah blah"

[Pete_UK]

I didn't realize that I had been "reported". Shame on me for trying to point out the rules !!

Pete

[Asad Mir]

Rule No. 4. Do not post a new help request in an existing thread.

In my understanding, i didn't consider my question as a "New Help Request" as it was about the same topic, i tried that formula and just wanted to sought clarification about a slight variation in the formula. And, because I I am way too junior in Excel than all of you, so i could not say "Another Option is to use...", that was the reason i asked it in form of a question.

Anyway, Since senior forum members consider it as a breach of rule, i will abide by it next time.

Thanks alot!

[AliGW]

Please read post #12. If we allowed one follow-on question by somebody different to the OP, we’d have to allow all, and it would very soon become chaotic and impossible to follow.

If this happens again, start your own thread and in it, post a link back to the original thread so that helpers can cross-reference it.

Also, in future, if you have an issue with anything anyone posts, please bring it to a moderator’s attention instead of challenging it yourself, which can be seen as argumentative and rarely ends well.

Thank you for your understanding - please PM me if you have any questions about what I have said.

[FDibbins]

Asad Mir , just to add my thoughts to this.

The way you phrased your comment...

Hi!
Can't we use this formula, or why can't we use this formula?
=(COUNTIF(B:B,B2)=COUNTIF(A:A,A2))*1

To most here, that would sound like you were bouncing your question off the details provided by another member's question and data. I personally love to see different options/suggestions being made to the same question, it just shows how versatile excel really is.

Perhaps if you has said...try =(COUNTIF(B:B,B2)=COUNTIF(A:A,A2))*1 and then left it at that, it would have been seen as another suggestion for the OP to try.

What often happens here is that someone will make a suggestion like that, that may not really work, and other members - or the OP - will test it and report back that "this" or "that" didnt quite work.

By phrasing your suggestion (and trust me, ALL are welcome and free to make suggestions towards resolving the question) as a question, you rang a little bell for some that said that you were asking your own question here.

Hope that makes sense now?

We look forward to seeing you grow in excel and providing more suggestions to questions here

[Asad Mir]

Sure....

My apologies to all of the forum members here for my misunderstanding or misinterpreting. Specially, Pete.

I would wish to be a valuable contributor here.

Thanks all

[FDibbins]

Sure....

My apologies to all of the forum members here for my misunderstanding or misinterpreting. Specially, Pete.

I would wish to be a valuable contributor here.

Thanks all

Thank you so much for your understanding here, much appreciated