Excel Finding matching values in a column and comparing the values in another column to one another-CodePudding

EDIT I have to accomplish this in Excel 2016 so I don't have access to Unique or Filter Functions

I have a table that contains a list of names and addresses and other information. I want to identify instances where the street address is used more than once, and then compare all the instances of the street address to return a “Match” or “Alert” if the last names of the people at the address do or don’t match.

I created a CountIf Column to identify entries that need to be compared, and a column to pull just the last name. =COUNTIF([Street],[@[Street]])

=LEFT([@Name],(FIND(" ",[@Name],1)-1))

How can I compare the last names of everyone living at the same address?

Is there a way to use the Filter function here, or will I need VBA?

ID#s	Name	Last Name	Street	Street Count
1	Brown Bob	Brown	Address 1	2
2	Brown Sue	Brown	Address 1	2
3	Green Adam	Green	Address 2	2
4	Chruchill John	Chruchill	Address 2	2
5	Smith Gary	Smith	Address 3	3
6	Smith Lisa	Smith	Address 3	3
7	Parker Peter	Parker	Address 4	1
8	Parker Lewis	Parker	Address 4	1
9	Smith Evan	Smith	Address 3	3

CodePudding user response：

This formula returns:

Ok if the [Street] is unique (change as required)
Match if the [Street] is not unique and all the [Last Name] for that [Street] are the same.

Alert if the [Street] is not unique and not all the [Last Name] for that [Street] are the same.

= IF( COUNTIF( [Street], [@Street] ) = 1, "Ok",
 IF( COUNTIF( [Street], [@Street] ) 
 = COUNTIFS( [Street], [@Street], [Last Name], [@[Last Name]] ), "Match", "Alert" ) )

CodePudding user response：

Try:

Result:  =IF(COUNTA(UNIQUE(FILTER([Last Name],[Street]=@[Street])))=1,"Match","Alert")

Edit: If you only have Excel 2016, try:

=IF(COUNTIFS([Street],[@Street],[Last Name],[@[Last Name]])=COUNTIF([Street],[@Street]),"Match","Alert")