Forum Discussion

Barrie Kirton's avatar
Barrie Kirton
Copper Contributor
Jun 18, 2018

Combining IF and AND formula applied to text strings.

Hi, I would like to insert a combined  IF and AND logical routine to return a result in another cell of "True"  / "False" / or ideally a number  when  one cell = a text string eg. "TextA" and anothe...
excel
Formulas and Functions
Barrie Kirton's avatar
Barrie Kirton
Copper Contributor
Jun 21, 2018

Sergei, It is the latter, ie generate the list of unique names , ie First Name Barrie (A) and Surname Kirton B and be able to count the number of times in the list that this unique combination of First and Surname appears  also would like to incorporate a location in column C  eg St Cuthberts church 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 21, 2018

Okay, I see. Locations in column C are the same for same persons or one person could have different locations in your records?

  • Barrie Kirton's avatar
    Barrie Kirton
    Copper Contributor
    Jun 21, 2018

    Sergei,

    The location could be different for each line so that the COUNTIF only returns a 1  when A, B and  C   satisfy the Formula String requirements, eg( A)=barrie,       (B)=kirton and C = St Cuthbert's church.  =1 

                                                                 and   (A )= David      (B)= kirton  and (c) = St Cuthbert's church =0

                                                                 and   (A0)  = barrie    (B) kirton    and (C) = St James' church   = 0

    etc.

     

    Many thanks for your help

    Barrie

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 22, 2018

      Hi Barrie,

       

      Thank you for the clarification. When for the data structured like this

      in column D added number of times records FirstName+Surname+Location are repeated

      =COUNTIFS($A$2:$A$30,$A2,$B$2:$B$30,$B2,$C$2:$C$30,$C2)

      and such records are highlighted by Conditional formatting.

       

      To generate list of unique records let put in F2

      =IFERROR(
   IFERROR(
      IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,$A$2:$A$30)=0),$A$2:$A$30),
            LOOKUP(2,1/(COUNTIF($G$1:G1,$B$2:$B$30)=0),$A$2:$A$30)),
      LOOKUP(2,1/(COUNTIF($H$1:H1,$C$2:$C$30)=0),$A$2:$A$30)),
"")

      and similar in G2

      =IFERROR(
   IFERROR(
      IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,$A$2:$A$30)=0),$B$2:$B$30),
            LOOKUP(2,1/(COUNTIF($G$1:G1,$B$2:$B$30)=0),$B$2:$B$30)),
      LOOKUP(2,1/(COUNTIF($H$1:H1,$C$2:$C$30)=0),$B$2:$B$30)),
"")

      and H2

      =IFERROR(
   IFERROR(
      IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,$A$2:$A$30)=0),$C$2:$C$30),
         LOOKUP(2,1/(COUNTIF($G$1:G1,$B$2:$B$30)=0),$C$2:$C$30)),
      LOOKUP(2,1/(COUNTIF($H$1:H1,$C$2:$C$30)=0),$C$2:$C$30)),
"")

      plus counting of such records in I2

      =COUNTIFS($A$2:$A$30,$F2,$B$2:$B$30,$G2,$C$2:$C$30,$H2)

      After that select all cells F2:I2 and drag them down till empty cells will appear.

       

      The latest table is easier to generate with Power Query

      - query source table and merge columns FirstName, Surname,Location

      - duplicate that query, second one Group by new combined column and merge with first query, after some cosmetic land the result into excel sheet

      Result in green looks like this

      Both files are attached.