Combining IF and AND formula applied to text strings.

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.