Do I need a Vlookup on steriods?

[bumfart66]

Hi

I've been scratching my head for ages on this one

I want to be able to enter two circuit IDs and see whether there is any duplication of the link IDs, for information the Links go up to Link 42

For example , i enter Circuit 001 and 002 and the response would show there is duplication of 202397

Circuit ID	Link 01	Link 02	Link 03	Link 04	Link 05	Link 06	Link 07	Link 08	Link 09
Circuit 001	202363	202397	203916
Circuit 002	202935	202397	202937	203944	203945	203946	203947	203948	203949
Circuit 003	203066	203067	203858	203958	203959	203962	204053	204054	204055
Circuit 004	201507	201510	203802	204071	204077
Circuit 005	200830	200831	201009	201391	203886	204076	204078
Circuit 006	200149	200188	200195	200197	200238	200300	200301	200350	200373
Circuit 007	200223	200249	200382	200383	200384	200433
Circuit 008	200191	200197	200198	200203	200210	200211	200254	200257	200294
Circuit 009	200148	200374	200539	200540	200541	203607
Circuit 010	200780	200781	200782	200838	201723	203990
Circuit 011	203881
Circuit 012	200223	200249	200433	200434	200586
Circuit 013	200460
Circuit 014	200223	200249	200382	200426	200433	200534	200562	200563
Circuit 015	200078	200093	200112	200130	200131	200779	202553	202966	203607
Circuit 016	200057	200099	200151	200152	200380	200381	200555	200572	200573
Circuit 017	200191	200200	200201	200202	200203	200210	200211	200255	200256
Circuit 018	200186	200192	200382	200426	200434	200534	200543	200544	200561
Circuit 019	200186	200192	200424	200425	200543	200544	204006	204007
Circuit 020	200186	200192	200426	200535	200536	200537	200543	200544	200561

 

[hagia_sofia]

Hello, it is not clear how the output should look like so let us know how to proceed further:

=LET(
ID,A2:A21,
links,B2:J21,
first,"Circuit 001",
second,"Circuit 002",
a,FILTER(links,ID=first),
b,FILTER(links,ID=second),
c,IF(a=b,a,""),
IF((ID=first)+(ID=second),c,""))

 

[bumfart66]

Hi

Thanks for your help, I was thinking of something like this, I enter two circuit in cells AR1 and AR2 and any duplicates are displayed

The links are in cells B2:AQ259
and the ID are in A2:A259

 

[hagia_sofia]

Many thanks for the reply. As I forgot to ask whether to count as duplicates only those numbers that are within the same column (i.e. the same link) or any duplicates within selected circuits, here are both versions:

=LET(
ID,A2:A259,
links,B2:AQ259,
first,AR1,
second,AR2,
a,FILTER(links,ID=first),
b,FILTER(links,ID=second),
c,UNIQUE(FILTER(a,a=b,""),TRUE),
TRANSPOSE(IF(ISBLANK(c),"",c)))

=LET(
ID,A2:A259,
links,B2:AQ259,
first,AR1,
second,AR2,
a,TOCOL(FILTER(links,(ID=first)+(ID=second)),1),
u,UNIQUE(a,,TRUE),
UNIQUE(FILTER(a,ISNUMBER(XLOOKUP(a,u,u))=FALSE,"")))

 

[bumfart66]

Thanks , works really well