Forum Discussion

Ashwin540's avatar
Ashwin540
Icon for Microsoft rankMicrosoft
Dec 26, 2022

Compare the rows and get output excel

Hi ,

I'm trying to get comparison between the 4 rows which are having identical and different values as well, need help on sorting and getting answer please.

 

Scenario: below in in=mage i have 4 rows where row D and E are set 1, G and H are another set 2

If Set 1 has one name and value which is equal to set then it is fine but if set 1 has value but set 2 is not same then there should be the name D stored in J.

 

Example: in below image 1 the option Algeria present in both sets with equal values but in in name 2 the Macao SAR is not present in set 2 so added in J row.

 

 

 

 

 

 

 

 

Excel
  • MY's avatar
    MY
    Brass Contributor
    Dec 27, 2022

    Ashwin540

    =IFERROR(FILTER(A,A&B<>C&D),"")

    where:

    • A is the range of Original Name
    • B is the range of Original Value
    • C is the range of Parameter Name
    • D is the range of Parameter Value

         

  • XXplore's avatar
    XXplore
    Brass Contributor
    Dec 26, 2022
    I2=VLOOKUP(D2,G:H,2)=E2
    j2=FILTER(D2:D6,I2:I6=TRUE)
    • Ashwin540's avatar
      Ashwin540
      Icon for Microsoft rankMicrosoft
      Dec 26, 2022
      Hi,

      Can you please brief this , as i am trying to compare two sets in which all the data is not identical and not same values included in both.
      Your section included I column?
      • XXplore's avatar
        XXplore
        Brass Contributor
        Dec 26, 2022
        (1) Column-I is a referecen column. If (D2,E2) have same pairs found in G:H, return true in I2
        I2=False means (D2,E2) have no identical pair in G:H
        Input formular in I2 (=VLOOKUP(D2,G:H,2)=E2) and Fill Down

        (2) Use FILTER to filter column D with rule of column I
        J2=FILTER(D2:D6,I2:I6=FALSE)

        Or Use Array to skip reference column-I
        J2=FILTER(D2:D6,VLOOKUP(D2:D6,G:H,2)=E2:E6)

Share

Resources