CountIfs based on dynamic similar values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, hope that anyone can help me with the issue that I have been battling with for some time now.
I'm trying to switch from Excel to Alteryx and I have a COUNTIFS function which does not look for an exact match, but a range of values and dates in order to find "possible" duplicates if data is similar.
Conditions:
Value: If the value is similar to another value up to (+- 2 %)
Name: If the name is the same
Date: If the payment has been made on the same date or up to + - 1 day.
Example of possible duplicate:
In the below example Row 2 and Row 5 would be a possible duplicate, as the name is the same, values are similar (within +- 2 %) and the payment dates are not more than +- 1 day appart.
Regards
Lasse
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @benakesh ,
Thanks for your reply. Here you go!
Value | Name | date |
100 | a | 10-10-2019 |
200 | b | 11-10-2019 |
300 | c | 12-10-2019 |
101 | a | 11-10-2019 |
400 | b | 14-10-2019 |
500 | c | 15-10-2019 |
600 | d | 16-10-2019 |
700 | e | 17-10-2019 |
800 | f | 18-10-2019 |
900 | g | 19-10-2019 |
1000 | h | 20-10-2019 |
1100 | i | 21-10-2019 |
1200 | j | 22-10-2019 |
1300 | k | 23-10-2019 |
1400 | l | 24-10-2019 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @laha10ae ,
Let me know if this works.
Conditions:
Value: If the value is similar to another value up to (+- 2 %)
[Right_Value] >= ([Value] - [Value] * .02 ) and
[Right_Value] <= ([Value] + [Value] * .02 )
Name: If the name is the same : join tool
Date: If the payment has been made on the same date or up to + - 1 day.
DateTimeDiff([DateTime_Out],[Right_DateTime_Out],'days') < 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @OllieClarke and @benakesh
Both solutions do exactly what I need. Thanks a lot for your help!
Regards
Lasse