Iron Contributor

# Common entry

Dear Experts,

I need to prepare a list of rnti's in column "N" ( a parameter say) which is common in all these 4 Tables,

may be with XMATCH , or any other formulae..

Br,

Anupam

6 Replies

# Re: Common entry

In your example, there are no common values, but if there were, they would be returned by

=FILTER(Table7[rnti-BIP], COUNTIF(Table1[[CRNTI]:[CRNTI3]], Table7[rnti-BIP])*COUNTIF(Table4[rnti-UL-TTI], Table7[rnti-BIP])*COUNTIF(Table5[rnti-DL-TTI], Table7[rnti-BIP]), "")

# Re: Common entry

Thanks @Hans Vogelaar it really helps, once you have some bandwidth , can you please share one more logic /Formula apart form the Filter function..

Thanks & Regards

Anupam Shrivastava

Why?

# Re: Common entry

Hi@Hans Vogelaar seems a bit Lengthy..

# Re: Common entry

A formula avoiding FILTER would probably be much longer...

# Re: Common entry

If you have the FILTER function I suggest you use it!  An alternative might be UNIQUE:

``````= LET(
list,     SORT(TOCOL(data,1)),
distinct, UNIQUE(list),
unique,   UNIQUE(list,,TRUE),
combined, VSTACK(distinct, unique),
UNIQUE(combined,,TRUE)
)``````

What this does is bring all your data into a single sorted column.  It then calculates every distinct value and appends the list of those occurring only once.  Duplicates in the original list occur only once in the combined list and are easily identified using the final application of UNIQUE.