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

[RobMotiwalla]

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!

[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

[gabrielvilella · Accepted solution]

Here is an example.

[RobMotiwalla]

Thanks @gabrielvilella, that is exactly what I was looking for!