Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Nov 01, 2023

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..

 

Thanks in Advance,

Br,

Anupam

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 01, 2023

    anupambit1797 

    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]), "")

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 02, 2023

    anupambit1797 

    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.

Share

Resources