Comparing Trip Legs To and From

[LearnVBA83]

Hi VBA experts!

I have a big data set that shows where individuals went on daily trips using addresses. One column contains the To address and one column contains the from address. Obviously if Row 1 says From Address A to Address B then you'd expect Row 2 to start with From Address B To_____. Also, I want to see if the last trip for the day is the same address as the very start of the trip. For example: (FYI i'm using places instead of addresses and fake names)

Name Date/Time To Address From Address
John Doe 1/1/15 8AM Home WalMart
John Doe 1/1/15 9AM WalMart Dollar Store
John Doe 1/1/15 11AM Dollar Store Home
Billy Bob 1/1/15 9AM Home Bowling
Billy Bob 1/1/15 1PM Bowling Walgreens
Billy Bob 1/1/15 3PM Walgreens Home
Billy Bob 1/1/15 4PM Home Huddlehouse (I'd like for it to flag this because the daily trip didn't end at home)
Jane Doe 1/2/15 10AM Home Mall
Jane Doe 1/2/15 11AM Mall WalMart
Jane Doe 1/2/15 2PM Salon Home (I'd like for it to flag this because she wasn't at the Salon in her prior leg of the trip).


This is basically what my data looks like except real names and 100's of real addresses.

Please let me know if you think you could help!

Best!

 

[Robby19]

It's not pretty, but here's what you get

Also, this is based off of the below table, as I think your From and To columns were flipped.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Date/Time
[/TD]
[TD]From Address
[/TD]
[TD]To Address
[/TD]
[TD]Discrepancy
[/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD][/TD]
[TD]Home
[/TD]
[TD]Walmart
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD][/TD]
[TD]Walmart
[/TD]
[TD]Dollar Store
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD][/TD]
[TD]Target
[/TD]
[TD]Home
[/TD]
[TD]Discrepancy
[/TD]
[/TR]
[TR]
[TD]Steven
[/TD]
[TD][/TD]
[TD]Subway
[/TD]
[TD]Target
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steven
[/TD]
[TD][/TD]
[TD]Bowling
[/TD]
[TD]Subway
[/TD]
[TD]Discrepancy
[/TD]
[/TR]
</TBODY>[/TABLE]

Keep in mind this formula will only work if all of the events from each person are in order. For example, all of the John Doe events must be in chronological order. If you have a John Doe, Steven, John Doe event like below;
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[/TR]
[TR]
[TD]Steven
[/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[/TR]
</TBODY>[/TABLE]
The formula will not work. I am sure someone will be along shortly to provide you a better solution. But this is my 2 cents.

This formula goes in the Discrepancy column and pasted down.

=IF(OR(A1="Name",A2<>A1),"",IF(AND(C2=D1,A2=A1),"","Discrepancy"))

 

[LearnVBA83]

Thanks Robby! This may just work!