adjusting formula to void duplicates items in list

[Alaa mg]

Hello
I use theses formulas in column L,M should bring items from column A,B, but I don't want any duplicates items.
theses formulas I use in data validation
L3=A2:A6
M3=OFFSET(B$1;MATCH(L$3;A$2:A$6;0);0;COUNTIFS(A$2:A$6;L$3))[/CODE]
thank you

 

[fjns]

Hi, see in the linked file a possible solution for Office 2019...

The L column contains the items of the A column without the duplicate items. The M column contains the items of the B column without the duplicate items, considering that there are no repeating items in columns L and M together.

Unfortunately, Google Drive does not recognize or mishandles some functions, displays some cells incorrectly. Spreadsheets worked fine most of the time after downloading, but there have been times when Google Drive broke a table and it malfunctioned after downloading. That's why I now uploaded the table Validation.xlsx also packed. Fortunately, after downloading the ZIP file, the unpacked Validation.xlsx table will work correct with your Office 2019.

The formulas used in the table...
L1: =IF(A1="","",A1)
L2: =IF(A2="","",IF(COUNTIF(L$1:L1,A2)=0,A2,IFERROR(INDEX(A:A,IFERROR(MATCH(0,IF(A:A="","",IFERROR(MATCH(A:A,L$1:L1,0),0)),0),"")),""))) (Range: L2:L20 ...)
M1: =IF(B1="","",IF(COUNTIF(L:L,B1)=0,B1,IFERROR(INDEX(B:B,IFERROR(MATCH(0,IF(B:B="","",COUNTIF(L:L,B:B)),0),"")),"")))
M2: =IF(B2="","",IF(AND(COUNTIF(L:L,B2)=0,COUNTIF(M$1:M1,B2)=0),B2,IFERROR(INDEX(B:B,IFERROR(MATCH(0,IF(B:B="","",COUNTIF(L:L,B:B)+IFERROR(MATCH(B:B,M$1:M1,0),0)),0),"")),""))) (Range: M2:M20 ...)

Validation.xlsx
Validation.zip