How do you write the formula to check for duplicates from an entire range A2:J5?

A

arthurz11

Board Regular
Joined
Nov 9, 2007
Messages
158
Office Version
  1. 2021
Platform
  1. Windows
How would I write the formula in cell L2 to check for duplicates for the entire range A2:J5? Just in case there are duplicates of x,y coordinates on another row? For example if cell I5 is -2 and cell J5 is -7, this will be an x,y coordinates of -2,-7 in row four and not just in row one. The formula in L5 =LET(f,FILTER(A5:J5,A5:J5<>""),s,SEQUENCE(COLUMNS(f)/2,,0),u,UNIQUE(INDEX(f,,s*2+{1,2})),ss,SEQUENCE(COUNT(u),,0),TRANSPOSE(INDEX(u,INT(ss/2)+1,MOD(ss,2)+1))) of row four will not recognize -2,-7 as a duplicate from row one so -2,-7 and will be displayed twice, in range L2:U5 in row one and in row four.


Picture1.png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hopefully this all works in excel 2021

MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTU
1XYXYXYXYXYXYXYXYXYXY
2-2-7-7-3-2-7-41-2-7-7-3-41
3-3-5-2-39-66-1-3-5-2-39-66-1
40-6-53600-6-5360
5-6-2-5-1-6-2-6-2-2-7-6-2-5-1
Sheet18
Cell Formulas
RangeFormula
L2:U5L2=LET(a,TOCOL(A2:J5),b,WRAPROWS(a,2),c,WRAPROWS(TOCOL(A2:J5)="",2),d,IF(c,"",b),e,UNIQUE(d),f,XLOOKUP(CHOOSECOLS(e,1)&"x"&CHOOSECOLS(e,2),CHOOSECOLS(d,1)&"x"&CHOOSECOLS(d,2),SEQUENCE(ROWS(d)),"",0),g,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,1),"",0),5),h,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,2),"",0),5),j,CHOOSECOLS(HSTACK(g,h),1,6,2,7,3,8,4,9,5,10),j)
Dynamic array formulas.
 
Upvote 0
JamesCanale said:
Hopefully this all works in excel 2021

MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTU
1XYXYXYXYXYXYXYXYXYXY
2-2-7-7-3-2-7-41-2-7-7-3-41
3-3-5-2-39-66-1-3-5-2-39-66-1
40-6-53600-6-5360
5-6-2-5-1-6-2-6-2-2-7-6-2-5-1
Sheet18
Cell Formulas
RangeFormula
L2:U5L2=LET(a,TOCOL(A2:J5),b,WRAPROWS(a,2),c,WRAPROWS(TOCOL(A2:J5)="",2),d,IF(c,"",b),e,UNIQUE(d),f,XLOOKUP(CHOOSECOLS(e,1)&"x"&CHOOSECOLS(e,2),CHOOSECOLS(d,1)&"x"&CHOOSECOLS(d,2),SEQUENCE(ROWS(d)),"",0),g,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,1),"",0),5),h,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,2),"",0),5),j,CHOOSECOLS(HSTACK(g,h),1,6,2,7,3,8,4,9,5,10),j)
Dynamic array formulas.
Click to expand...
My results was #NAME?. So how do I remove that error?
 
Upvote 0
Some of those functions are not available in Excel 2021 so if you have it there is a need for alternative approach...
 
Upvote 0
This is incomprehensible in 2021, but this should work. I had to mangle some arrays.

MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTU
1XYXYXYXYXYXYXYXYXYXY
2-2-7-7-3-2-7-41-2-7-7-3-41
3-3-5-2-39-66-1-3-5-2-39-66-1
40-6-53600-6-5360
5-6-2-5-1-6-2-6-2-2-7-6-2-5-1
6
7excel 2021-2-7-7-3-41
8-3-5-2-39-66-1
90-6-5360
10-6-2-5-1
Sheet18
Cell Formulas
RangeFormula
L2:U5L2=LET(a,TOCOL(A2:J5),b,WRAPROWS(a,2),c,WRAPROWS(TOCOL(A2:J5)="",2),d,IF(c,"",b),e,UNIQUE(d),f,XLOOKUP(CHOOSECOLS(e,1)&"x"&CHOOSECOLS(e,2),CHOOSECOLS(d,1)&"x"&CHOOSECOLS(d,2),SEQUENCE(ROWS(d)),"",0),g,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,1),"",0),5),h,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,2),"",0),5),j,CHOOSECOLS(HSTACK(g,h),1,6,2,7,3,8,4,9,5,10),j)
L7:U10L7=LET(a,A2:J5,b,SEQUENCE(20,2,0),c,MOD(b,10)+1,d,INT(b/10)+1,e,IF(INDEX(a,d,c)<>"",INDEX(a,d,c),""),f,UNIQUE(e),g,XLOOKUP(INDEX(f,,1)&"x"&INDEX(f,,2),INDEX(e,,1)&"x"&INDEX(e,,2),SEQUENCE(ROWS(e)),"",0),h,XLOOKUP(SEQUENCE(ROWS(e)),g,CHOOSECOLS(f,1),"",0),i,XLOOKUP(SEQUENCE(ROWS(e)),g,CHOOSECOLS(f,2),"",0),j,SEQUENCE(4,10),jj,IF(ISODD(j),INT(j/2)+1,INT(j/2)+20),k,SEQUENCE(40),m,IF(k<21,INDEX(h,k),INDEX(i,k-20)),n,j*0+INDEX(m,jj),IFERROR(n,""))
Dynamic array formulas.
 
Upvote 0
You need to change the choosecols to Index for 2021.
 
Upvote 0
I missed those two...

THis is better.

MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTU
1XYXYXYXYXYXYXYXYXYXY
2-2-7-7-3-2-7-41-2-7-7-3-41
3-3-5-2-39-66-1-3-5-2-39-66-1
40-6-53600-6-5360
5-6-2-5-1-6-2-6-2-2-7-6-2-5-1
6
7excel 2021-2-7-7-3-41
8-3-5-2-39-66-1
90-6-5360
10-6-2-5-1
Sheet18
Cell Formulas
RangeFormula
L2:U5L2=LET(a,TOCOL(A2:J5),b,WRAPROWS(a,2),c,WRAPROWS(TOCOL(A2:J5)="",2),d,IF(c,"",b),e,UNIQUE(d),f,XLOOKUP(CHOOSECOLS(e,1)&"x"&CHOOSECOLS(e,2),CHOOSECOLS(d,1)&"x"&CHOOSECOLS(d,2),SEQUENCE(ROWS(d)),"",0),g,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,1),"",0),5),h,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,2),"",0),5),j,CHOOSECOLS(HSTACK(g,h),1,6,2,7,3,8,4,9,5,10),j)
L7:U10L7=LET(a,A2:J5,b,SEQUENCE(20,2,0),c,MOD(b,10)+1,d,INT(b/10)+1,e,IF(INDEX(a,d,c)<>"",INDEX(a,d,c),""),f,UNIQUE(e),g,XLOOKUP(INDEX(f,,1)&"x"&INDEX(f,,2),INDEX(e,,1)&"x"&INDEX(e,,2),SEQUENCE(ROWS(e)),"",0),h,XLOOKUP(SEQUENCE(ROWS(e)),g,INDEX(f,,1),"",0),i,XLOOKUP(SEQUENCE(ROWS(e)),g,INDEX(f,,2),"",0),j,SEQUENCE(4,10),jj,IF(ISODD(j),INT(j/2)+1,INT(j/2)+20),k,SEQUENCE(40),m,IF(k<21,INDEX(h,k),INDEX(i,k-20)),n,j*0+INDEX(m,jj),IFERROR(n,""))
Dynamic array formulas.
 
Upvote 0
JamesCanale said:
I missed those two...

THis is better.

MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTU
1XYXYXYXYXYXYXYXYXYXY
2-2-7-7-3-2-7-41-2-7-7-3-41
3-3-5-2-39-66-1-3-5-2-39-66-1
40-6-53600-6-5360
5-6-2-5-1-6-2-6-2-2-7-6-2-5-1
6
7excel 2021-2-7-7-3-41
8-3-5-2-39-66-1
90-6-5360
10-6-2-5-1
Sheet18
Cell Formulas
RangeFormula
L2:U5L2=LET(a,TOCOL(A2:J5),b,WRAPROWS(a,2),c,WRAPROWS(TOCOL(A2:J5)="",2),d,IF(c,"",b),e,UNIQUE(d),f,XLOOKUP(CHOOSECOLS(e,1)&"x"&CHOOSECOLS(e,2),CHOOSECOLS(d,1)&"x"&CHOOSECOLS(d,2),SEQUENCE(ROWS(d)),"",0),g,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,1),"",0),5),h,WRAPROWS(XLOOKUP(SEQUENCE(ROWS(d)),f,CHOOSECOLS(e,2),"",0),5),j,CHOOSECOLS(HSTACK(g,h),1,6,2,7,3,8,4,9,5,10),j)
L7:U10L7=LET(a,A2:J5,b,SEQUENCE(20,2,0),c,MOD(b,10)+1,d,INT(b/10)+1,e,IF(INDEX(a,d,c)<>"",INDEX(a,d,c),""),f,UNIQUE(e),g,XLOOKUP(INDEX(f,,1)&"x"&INDEX(f,,2),INDEX(e,,1)&"x"&INDEX(e,,2),SEQUENCE(ROWS(e)),"",0),h,XLOOKUP(SEQUENCE(ROWS(e)),g,INDEX(f,,1),"",0),i,XLOOKUP(SEQUENCE(ROWS(e)),g,INDEX(f,,2),"",0),j,SEQUENCE(4,10),jj,IF(ISODD(j),INT(j/2)+1,INT(j/2)+20),k,SEQUENCE(40),m,IF(k<21,INDEX(h,k),INDEX(i,k-20)),n,j*0+INDEX(m,jj),IFERROR(n,""))
Dynamic array formulas.
Click to expand...

That was the short version range A2:J5. My actual range is ADU140:AJN159. It has the same x,y data format but with 150 columns and 20 rows. Anyway, what do I need to revise on your formula besides just changing range A2:J5 to range ADU140:AJN159. I figured it would work just be changing the range. Right now, I just get a 0 in cell ADU140.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
How do I write a formula to count duplicate x,y coordinates?
Replies
3
Views
147
Joe4
Joe4
I
  • Solved
Large file applying formulas via VBA help
Replies
9
Views
476
ItalianPlatinum
I
J
  • Locked
  • Question
formula for randomly choosing an option
Replies
1
Views
109
Fluff
Fluff
D
  • Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
401
Davenil
D
E
  • Solved
Formula not working
Replies
2
Views
271
esmarques
E

Forum statistics

Threads
1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top