Is there a good way to find fields that do not match when using the unique tool?

I am using the Unique and Only Unique tools to try and find matches on a number of different fields. I want to make sure it pulls all of those matching rows, but I want to also have a criteria where a specific field does NOT match. Is there an easy way to do this?

Thanks!

Best Practices

Preparation

Accepted answers

gabrielvilella

Here is an example.

Count.yxmd

All comments

gabrielvilella

If you could please provide a sample dataset and the desired output would be helpful.

RobMotiwalla

Hi @gabrielvilella

Here is an example of the data. I am trying to match on the account, doc date, and absamount (in yellow), but I would like for the doc type and/or amount (red) to be different. From the data below, the only ones that i would like to see are ones in green.

Thanks for the help!

Reference	Account	Branch Name	Profit Center	Bottler	Accounting Doc #	Doc Type	Doc Date	Posting Date	Amount	AbsAmount	Invoice Reference
9.1E+13	300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
9.1E+13	300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
9.1E+13	300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
9.1E+13	300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
9.1E+13	300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
2419808AC871	300000141	300096550	CI00750005	Viking - Duluth	1506119365	CZ	9/2/2021	10/13/2021	-10	10	V
1376617C123	300000141	300063911	CI01600000	Love Bottling Co.	1506218296	CZ	1/26/2022	1/29/2022	-10	10	V
1102102C17486	300000141	300005042	CI02150000	Binks CCBC	1506213298	CZ	1/20/2022	1/26/2022	-10	10	V
2123188C17486	300000141	300005042	CI02150000	Binks CCBC	1505501691	CZ	3/12/2020	3/18/2020	-10	10	V
302876C896	300000141	300161656	CI03400000	Hancock Bottling Co.	1505858465	CZ	2/2/2021	2/6/2021	-10	10	V
304728C896	300000141	300161656	CI03400000	Hancock Bottling Co.	1506010682	CZ	6/22/2021	6/25/2021	-10	10	V
590671	300000149			UNKNOWN	1400164740	DZ	4/7/2021	4/7/2021	-10	10
1166948	300000149	300190382	CI00100005	Consolidated CONA	1400293933	DZ	8/20/2021	2/24/2022	-10	10	1838822224
825395	300000149	300133358	CI00150005	United CONA	1400137535	CZ	8/20/2021	8/20/2021	10	10	1837368389
1505899146	300000149	300111051	CI00450005	NNE CONA	1505899146	CZ	3/9/2021	3/18/2021	-10	10	V

RobMotiwalla

Hi @gabrielvilella

I just replied to your comment and posted it, but I do not see the comment now. So my apologies if this is duplicated. I would like to find duplicates on the account, doc date, and absamount (yellow). But I need the doc type and/or amount to be different (red). From the data below, following those criteria, the only rows that I would expect to see are highlighted in green.

Thank you for the help!

gabrielvilella

@RobMotiwalla thank you for the explanation! You need to insert a Summarize tool, group by Account, Doc Date and AbsAmount, and count distinct Doc Type and Amount. All records with count > 1 are what you call duplicates. Then you can use a join to filter those records. If you provide the file or just paste the table here I'd be able to build a sample workflow, it can be just some sample data.

RobMotiwalla

I tried pasting in straight from Excel before and it gave me an html error. Let me try again.

Account	Branch Name	Profit Center	Bottler	Accounting Doc #	Doc Type	Doc Date	Posting Date	Amount	AbsAmount
300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
300000385			UNKNOWN	1400152916	DZ	10/22/2018	10/19/2018	-10	10
300000149	300190382	CI00100005	Consolidated CONA	1400293933	DZ	8/20/2021	2/24/2022	-10	10
300000149	300133358	CI00150005	United CONA	1400137535	CZ	8/20/2021	8/20/2021	10	10
300000141	300096550	CI00750005	Viking - Duluth	1506119365	CZ	9/2/2021	10/13/2021	-10	10
300000141	300063911	CI01600000	Love Bottling Co.	1506218296	CZ	1/26/2022	1/29/2022	-10	10

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703