Find duplicates in different rows and different columns

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!

Parse

Accepted answers

estherb47

Hi @ssphv

Wow!! What a challenge this one was. I wanted to build something that would match any value in any row or column, as per your request, which took a few transpose, joins, cross tabs, and unions. May be more convoluted than it needs to be. But since Row 5 could match to row 1, transposing and cross-tabbing back together wouldn't work.

Figuring out the "over 20 columns" was fun too. Decided to reassign numbers to the headers, and anything over 20 had the numbers restart at 1. Those records also got a letter appended to their record ID so that the data would build back in the proper order.

Please let me know if any of this doesn't make sense, and if it works for you.

cheers!

Esther

DuplicatesInRowsOrColumns.yxmd

All comments

MarqueeCrew

Try this approach:

Use a TRANSPOSE tool to group by ROW (RECORDID will help). This will give you RecordID plus name value pairs. Filter out all blank values. Repeat for table 2. Union the data together and then CROSSTAB the data back to a single row by GROUPING on Record ID, using Name as Header and Value as Value. In the crosstab, you can select FIRST as the method. This will remove duplicates. If it doesn't, then use a unique tool (all 3 fields checked) and try it using concatenate.

This should get you past your basic problem. Next you'll have to deal with limits of 20 fields.

Cheers,

Mark

Thableaus

Hi @ssphv

Not the easiest solution here, but it might work.

Check the workflow details and let me know if it suits you. Hard to explain, but it was quite a challenge.

Cheers,

WorkFlowSolution.yxmd

estherb47

Hi @ssphv

Esther

DuplicatesInRowsOrColumns.yxmd

estherb47

Just tried it with different sets of numbers, and it still works

ssphv

This works perfectly and yes I forgot to mention that it should match any value in any row and any column. I appreciate your help!! Thank you!

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

DuplicatesInRowsOrColumnsV2.yxmd

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,

Quick Links

Unanswered
Community Events
Groups

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335