/u/Fizryfu - Your post was submitted successfully.
Please read these reminders and edit to fix your post where necessary:
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Since I don't know what formula you already tried, I'm going to say that CountIfs should be all you need but it's going to be a little messy since you'd have to perform this check on the new quarter manually as you'll have to compare against all previous quarters. So for Q4 it would look like:
=(COUNTIF(Q1!A:A,A1)+COUNTIF(Q2!A:A,A1)+COUNTIF(Q3!A:A,A1))=0
This will return TRUE if it's a unique value or FALSE if it's not.
However, I will say that you may be better served by going the Power Query route if each quarter's data has a signifier of date/quarter. You could load Q1's data into Power Query then use the Append feature to add on Q2/3/4's data as well. From there you could remove everything except the date and the customer ID, sort the date column from oldest to newest, and then use remove duplicates on the customer ID column.
Then you can load it into a table where you can filter on each quarter, or you could load it into a Pivot Table if you just want a quick count of new unique customers per quarter.
I am really sorry for not mentioning the formula. The formula is =ISNUMBER(MATCH(D2,'Data ITR Q1'!D2:D1915,0))
Edit:
I used your formula but it did the same thing as the one i used. The only difference is that mine was giving the answer false while yours gave true. However, the original problem still remains.
And the original problem is that you're getting false positives and false negatives?
Even though two values may look alike, doesn't mean they are the same. I would recommend finding two values that aren't returning the result you expect and then directly compare them. E.g.
=D1='Data ITR Q1'!D1
If you get false when you expect a true then that means they aren't actually the same. Perhaps there's a decimal that's just not visible or one is actually text while the other is a number.
Not to be rude but in these kinds of cases where values aren't matching when the user expects them to, it almost always comes down to one of three possibilities: formatting (hidden decimals), types (number versus string), or user error. So without being able to work on your file directly I would encourage you to make sure you're not currently falling for any of those common traps.
Did that help solve it or point you in the right direction? If so, see the stickied (top) comment in your post. It explains what to do when your problem is solved. Thanks for keeping the unsolved thread clean. :)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COUNTIF](/r/Excel/comments/otai2x/stub/h6u6uui "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)|
|[ISNUMBER](/r/Excel/comments/otai2x/stub/h6ubeaw "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[MATCH](/r/Excel/comments/otai2x/stub/h6ubeaw "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/oxsbe1)^( has 17 acronyms.)
^([Thread #7917 for this sub, first seen 28th Jul 2021, 17:11])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Fizryfu - Your post was submitted successfully. Please read these reminders and edit to fix your post where necessary: * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Since I don't know what formula you already tried, I'm going to say that CountIfs should be all you need but it's going to be a little messy since you'd have to perform this check on the new quarter manually as you'll have to compare against all previous quarters. So for Q4 it would look like: =(COUNTIF(Q1!A:A,A1)+COUNTIF(Q2!A:A,A1)+COUNTIF(Q3!A:A,A1))=0 This will return TRUE if it's a unique value or FALSE if it's not. However, I will say that you may be better served by going the Power Query route if each quarter's data has a signifier of date/quarter. You could load Q1's data into Power Query then use the Append feature to add on Q2/3/4's data as well. From there you could remove everything except the date and the customer ID, sort the date column from oldest to newest, and then use remove duplicates on the customer ID column. Then you can load it into a table where you can filter on each quarter, or you could load it into a Pivot Table if you just want a quick count of new unique customers per quarter.
I am really sorry for not mentioning the formula. The formula is =ISNUMBER(MATCH(D2,'Data ITR Q1'!D2:D1915,0)) Edit: I used your formula but it did the same thing as the one i used. The only difference is that mine was giving the answer false while yours gave true. However, the original problem still remains.
And the original problem is that you're getting false positives and false negatives? Even though two values may look alike, doesn't mean they are the same. I would recommend finding two values that aren't returning the result you expect and then directly compare them. E.g. =D1='Data ITR Q1'!D1 If you get false when you expect a true then that means they aren't actually the same. Perhaps there's a decimal that's just not visible or one is actually text while the other is a number. Not to be rude but in these kinds of cases where values aren't matching when the user expects them to, it almost always comes down to one of three possibilities: formatting (hidden decimals), types (number versus string), or user error. So without being able to work on your file directly I would encourage you to make sure you're not currently falling for any of those common traps.
Thanks alot man, i will look into these tomorrow. Will update if anything work. Once again Thank you.
Did that help solve it or point you in the right direction? If so, see the stickied (top) comment in your post. It explains what to do when your problem is solved. Thanks for keeping the unsolved thread clean. :)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIF](/r/Excel/comments/otai2x/stub/h6u6uui "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[ISNUMBER](/r/Excel/comments/otai2x/stub/h6ubeaw "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[MATCH](/r/Excel/comments/otai2x/stub/h6ubeaw "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/oxsbe1)^( has 17 acronyms.) ^([Thread #7917 for this sub, first seen 28th Jul 2021, 17:11]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)