Find duplicates in different rows and different columns

[ssphv]

I have 2 tables as below and below is my requirement:

1.       Find if there are duplicates for any column in any row. For example, row1 in table1 and row1 in Table2 have ‘2’ in common.  Pull such records and generate a record combining both and removing duplicate like row1 in Table3(Final output)

2.       One condition is when combining such records if total values exceed 20, split into 2 records. Like Row4 in Table1 and Row4 in Table2 together after removing duplicates have 22 values, from Table3 you can see that it is split into 2 rows.

Table1:

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	3
1	11	56	48	22	38
81	54
10	9	8	7	6	5	12	13	14	15	16	17	18	19	20	21	23	25

Table2:

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
77	2	78
60	61	62	63
88	89	54	86	90
29	30	31	10	9	8	45

Final Output(Table3):

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	3	77	78
1	11	56	48	22	38
81	54	88	89	86	90
60	61	62	63
10	9	8	7	6	5	12	13	14	15	16	17	18	19	20	21	23	25	29	30
31	45

How can I achieve this. Thanks in advance!

[ssphv]

Thanks for the suggestion @estherb47 . I'm currently using alteryx 11.7 version. I'm new to alteryx and never used macro. I should give it a try and see how it works. 

Thanks again for the solution!

[ssphv]

@estherb47  I tried to implement macros in the workflow but couldn't succeed. Can you please provide a sample solution. 

[estherb47]

Let me see what I can work up for you tonight. Thank you for sharing your version of alteryx so that I can give you a macro that will work :)

[ssphv]

Hi @estherb47 ,

Any luck with the workflow?

[estherb47]

Hi @ssphv ,

Having some challenges with 1) time to work on this and 2) turning it into a macro.

Wondering if @Thableaus or @MarqueeCrew can help out? My workflow (solution) works when there is a match to one row. We want to turn it into a macro, that will compare each row of the second data set to the entirety of the first data set, so that all matches are captured.

My struggle is that the batch macro would want you to match up all of the fields one by one, which is pretty tedious. Is there a way to bring in an entire row without needing to do a field to field match in the macro configuration?

Or perhaps an iterative macro is the way to go, using the iteration number to determine which row to look at in the second data set? Thinking out loud, and continuing to noodle on this.

Cheers!

Esther

Best,

Esther

[Thableaus]

I'll take a look in your workflow @estherb47  and see if I can help with some ideas.

Cheers,

[estherb47]

Hi @ssphv ,

Thank you for your amazing patience.

Idea for a macro turned out to be a temporary dead-end. But, I did fix the workflow so that it can capture if any row from the bottom dataset matches to more than one row on the top data set. Just tweaked the logic a bit so that the rows where there were multiple matches would remain distinct (basically just modified the crosstabs to have 2 header fields instead of the original 1 header field)

Please let me know if this new solution works for you. Thanks!!

Cheers!

Esther

[ssphv]

Thank you so much @estherb47  for your efforts. I really appreciate it. This solution worked to some extent but not completely. Keeping in mind  the complexity of this solution we have decided to opt for a different solution for special cases. Thanks again for all the help. 

[Thableaus]

@estherb47 and @ssphv, I'm sorry that this week has been really busy for me.

I did take a look, but due to the complexity of the solution, I couldn't find something that @estherb47  had not thought about it.

Good to know everything turned out to be fine for all.

Cheers,