Macro to identify different values in different rows taking into account a duplicated one

Hi guys,

I´m trying to find a solution for a problem that might be really interesting. I´ll try to explain the problem and the output it should have. I´ll also upload the data that is supposed to be the one of the problem.

The problem is the following:

Provider ID	Corporation	InvoiceCode	Amount	Transaction Code	Concatenated Value
12779	LIE1	V8756	100	MIRO	V8756100
16769	LIE1	V8756	100	MIRO	V8756100
1103	CHE1	M12283	5879	FB01	M122835879
1103	VFE1	M12283	5879	FB01	M122835879

I have built a workflow to identify duplicated invoices, i do this by the concatenation of "InvoiceCode" and "Amount", you can see the result column under ConcatenatedValue.

Now i would like to build a Macro or a formula tool, if it works, to identify the value of the other columns in case the concatenated value is duplicated and check if those are different or the same.

Let's put an example, the first case. Let's say the concatenated Value is V8756100, first of all i would like to check if this value is duplicated under that same column, if it is i would like to know if the Provider Code for both lines is the same, in case it is, that's fine, but if it´s not i would like to add another column and mark it with an "X" o something like that on both rows

The same for the other example, if the concatenated value is the same, but the corporation value is not, i would like to mark that with an "X"

I don´t know if these is even possible with Alteryx, i think it is because Alteryx is the best.

Hope u guys can help me out, if i need to clarify anything feel free to comment.

Macros

Accepted answers

Hi @lascurainge ,

I have mocked up a workflow to get you started. Let me know if this helps.

I added a couple of extra rows to your dataset to test the logic.

Concatenated Value.yxzp

All comments

Hi @lascurainge ,

Why dont we just remove duplicates using all columns.

You can use Unique tool to remove duplicate.

Here i am setting unique on all columns like below.

Hope this is what you are looking for.

Resource for unique tool https://community.alteryx.com/t5/Interactive-Lessons/Removing-Duplicate-Values/ta-p/85809

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Hello @atcodedog05:

Yes, that might work, the thing is that i need that extra column to check If those are duplicated, and i need to do it for every condition.

Hi @lascurainge ,

I have mocked up a workflow to get you started. Let me know if this helps.

I added a couple of extra rows to your dataset to test the logic.

Concatenated Value.yxzp

Glad to see this is solved 🙂 Great job @purnimat 🙂👍

Got to look at how its solved 😀

Quick Links

Tags

This months top contributors

AkimasaKajitani 387

Hollingsworth 335