Search code examples
excelexcel-formulavlookup

Excel filtering/extracting data based on a couple conditions

I have the following data in Excel:

application version
app_01 1.0
app_01 2.0
app_02 2.0
app_02 2.0
app_03 1.0
app_03 2.0
app_03 3.0

I'm looking for a way to filter and display applications with more than one distinct version. First condition would be 1) application values need to match, then 2) if versions under those applications do NOT match, then display those rows. So, the expected output should be something like this:

application version
app_01 1.0
app_01 2.0
app_03 1.0
app_03 2.0
app_03 3.0

Any help or guidance would be appreciated!

Solution

  • Try using FILTER( ) & COUNTIFS( )

    enter image description here

    • Formula used in cell D2

    =FILTER(A2:B8,COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8)=1)

    Or, Use FILTER( ) with MMULT( )

    enter image description here

    • Formula used in cell D2

    =LET(
     α,A2:A8&"|"&B2:B8,
     σ,N(α=TOROW(α)),
     FILTER(A2:B8,MMULT(σ,SEQUENCE(ROWS(σ),,,0))=1))

    I am not sure whether this works for your query or not, found even UNIQUE( ) function would simply do the job. This is because, you need those which do not have same versions and the applications should match, which is actually getting the ones which have appeared only once. So the last parameter of UNIQUE( ) does that (TRUE / 1 --> Return items that appear exactly once).

    enter image description here

    • Formula used in cell D2

    =UNIQUE(A2:B8,,1)