Mark rows where date and location are the same, but person is different

genex1

New Member
Joined
Jan 30, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
I have a table with amongst other fields a date, person and location. I need a formula that will mark rows showing where date and location are the same, but the person is different, as the example below. There could be more than 2 persons at the same location each day. The person may have multiple records showing them at the same location each day. The current table is around 600 rows like this. Would appreciate any ideas, as I'm struggling to come up with anything myself.

Date Location Person Match
01/09/2019 London John Smith
01/09/2019 Southampton Jane Taylor
01/09/2019 Leicester Richard Rogers
01/09/2019 Exeter Richard Rogers x
01/09/2019 Exeter John Smith x
02/09/2019 Chelmsford Louise Wright
02/09/2019 Leicester Jane Taylor x
02/09/2019 Leicester Richard Rogers x
02/09/2019 Exeter John Smith
02/09/2019 Exeter John Smith
02/09/2019 Exeter John Smith
03/09/2019 Southampton Richard Rogers x
03/09/2019 Southampton Jane Taylor x
03/09/2019 Leicester Louise Wright
03/09/2019 London Louise Wright x
03/09/2019 London John Smith x
03/09/2019 London Louise Wright x
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
My first post, bit of a mess the table, so added an image.

Excel table.PNG
 
Upvote 0
Hi genex1,

Does this do what you want?

Book1
ABCDE
1DateLocationPersonMatchFormula
21/9/2019LondonJohn Smith 
31/9/2019SouthamptonJane Taylor 
41/9/2019LeicesterRichard Rogers 
51/9/2019ExeterRichard Rogersxx
61/9/2019ExeterJohn Smithxx
72/9/2019ChelmsfordLouise Wright 
82/9/2019LeicesterJane Taylorxx
92/9/2019LeicesterRichard Rogersxx
102/9/2019ExeterJohn Smith 
112/9/2019ExeterJohn Smith 
122/9/2019ExeterJohn Smith 
133/9/2019SouthamptonRichard Rogersxx
143/9/2019SouthamptonJane Taylorxx
153/9/2019LeicesterLouise Wright 
163/9/2019LondonLouise Wrightxx
173/9/2019LondonJohn Smithxx
183/9/2019LondonLouise Wrightxx
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=IF(COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,B2)-COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,B2,$C$2:$C$18,C2)>0,"x","")
 
Upvote 0
Yes that's spot on, spent a few hours trying to solve this, thanks very much. Looks like you took all of 5 minutes at most. I will break the formula down and work out what's going now. I can remove duplicates myself, but can the formula be improved to unmark line 18 , which is a repeat of line 16. Didn't want to ask too much in my original question.
 
Upvote 0
Now I've worked out your logic here, it's surprisingly really simple. thanks again Toadstool.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top