How to identify employees living at the same address at the same time

[TamStuff]

Hi

Have visited this forum loads and have always found an answer to my problem, but this one seems a pretty tricky issue.

We have an allowance that applies per household and I suspect we have a number of employees who may not have received it. I am trying to double check if anyone else in the household was in receipt of the allowance, so need to identify those who were living at the same address at any point.

My file is approx 10,000 rows of data to review. Is there a way to calculate if someone is recorded at the same address during the same time period, sample data set below:

Personnel Number	Street and House Number	Start Date	End Date	Same address at same time?
Sally Sample	103A Kennedy Street	30/04/2012​	13/06/2020​	Help!
John Doe	103A Kennedy Street	12/07/2012​	1/01/2020​	Help!

 

[denzo36]

I can't see any easy way to do this so probably need a few steps. Here's how I would do it:

1. Make a pivot table of your data, put 'Street and House Number' in the rows area and do a count of this field in the values area. This will tell you how many times each address appears in your list. If the count is 1, you can immediately exclude these addresses as you only care about addresses that are in your data more than once.
2. If the count is 2 or more, add 'StartDate' and 'EndDate' into the values area. Change the aggregation of the 'StartDate' to min and the aggregation of the 'EndDate' to max. Doing this will then give you the minimum start date and max end date for each particular address. Make sure to format the min and max fields as dates like below

3. You could then use this pivot table and vlookup the address in your data table and return the min and max dates for that address for each row in your data table.
4. Then you could use a formula on each row of your data to say if the start date is greater than or equal to the min start date and the end date is less than or equal to the max end date, then 1 else 0.
5. You could then use another pivot table including the new fields you've created, put that new 1,0 field in the filters, only select 1. Then drop 'Street and House Number' into the rows again and do a count again. If you have addresses with a count of 2 or more, these should be ones where there was more than one person in the address at the same time.

I think that should you get a close as you can to be honest.

 

[Herakles]

Hi

Have visited this forum loads and have always found an answer to my problem, but this one seems a pretty tricky issue.

We have an allowance that applies per household and I suspect we have a number of employees who may not have received it. I am trying to double check if anyone else in the household was in receipt of the allowance, so need to identify those who were living at the same address at any point.

My file is approx 10,000 rows of data to review. Is there a way to calculate if someone is recorded at the same address during the same time period, sample data set below:

Personnel Number	Street and House Number	Start Date	End Date	Same address at same time?
Sally Sample	103A Kennedy Street	30/04/2012​	13/06/2020​	Help!
John Doe	103A Kennedy Street	12/07/2012​	1/01/2020​	Help!

I take it that you want to know if there was any overlap of the dates in which a particular person was living at a property. I assume that the date range does not have to be the same
and that the overlap, in terms of days, only need be 1 day.

Let me know if that is not the case.

I did not have much data to go on so I invented some, 'unrealistic' data, but it proves the method works. I thought that having the names there may be useful.

I'm not sure how you are going to process this data but I can help whatever you want to do.

Using Excel to create a schedule table from given data table.xlsm
	A	B	C	D	E
1	Personnel Number	Street and House Number	Start Date	End Date	Same address at same time?
2	Jack Roth	4 Kennedy Street	10/01/2023	11/01/2023	Jack Roth, Elizabeth East, David Wakeman
3	Ursula Tennison	2 Kennedy Street	07/01/2023	13/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
4	Ryan Nevin	1 Kennedy Street	08/01/2023	18/01/2023	Ryan Nevin
5	Elizabeth East	4 Kennedy Street	04/01/2023	14/01/2023	Jack Roth, Elizabeth East, David Wakeman
6	Cecil Haddow	2 Kennedy Street	02/01/2023	18/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
7	Leah Portillo	2 Kennedy Street	10/01/2023	24/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
8	David Wakeman	4 Kennedy Street	08/01/2023	21/01/2023	Jack Roth, Elizabeth East, David Wakeman
9	Tia Desborough	3 Kennedy Street	01/01/2023	06/01/2023	Tia Desborough
10	Arnold Wallis	2 Kennedy Street	08/01/2023	17/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
11	Alice Foley	3 Kennedy Street	10/01/2023	20/01/2023	Alice Foley
Version2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$11,($B2=$B$2:$B$11) * ($C2<=$D$2:$D$11) * ($D2>=$C$2:$C$11)))

 

[TamStuff]

I take it that you want to know if there was any overlap of the dates in which a particular person was living at a property. I assume that the date range does not have to be the same
and that the overlap, in terms of days, only need be 1 day.

Let me know if that is not the case.

I did not have much data to go on so I invented some, 'unrealistic' data, but it proves the method works. I thought that having the names there may be useful.

I'm not sure how you are going to process this data but I can help whatever you want to do.

Using Excel to create a schedule table from given data table.xlsm
	A	B	C	D	E
1	Personnel Number	Street and House Number	Start Date	End Date	Same address at same time?
2	Jack Roth	4 Kennedy Street	10/01/2023	11/01/2023	Jack Roth, Elizabeth East, David Wakeman
3	Ursula Tennison	2 Kennedy Street	07/01/2023	13/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
4	Ryan Nevin	1 Kennedy Street	08/01/2023	18/01/2023	Ryan Nevin
5	Elizabeth East	4 Kennedy Street	04/01/2023	14/01/2023	Jack Roth, Elizabeth East, David Wakeman
6	Cecil Haddow	2 Kennedy Street	02/01/2023	18/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
7	Leah Portillo	2 Kennedy Street	10/01/2023	24/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
8	David Wakeman	4 Kennedy Street	08/01/2023	21/01/2023	Jack Roth, Elizabeth East, David Wakeman
9	Tia Desborough	3 Kennedy Street	01/01/2023	06/01/2023	Tia Desborough
10	Arnold Wallis	2 Kennedy Street	08/01/2023	17/01/2023	Ursula Tennison, Cecil Haddow, Leah Portillo, Arnold Wallis
11	Alice Foley	3 Kennedy Street	10/01/2023	20/01/2023	Alice Foley
Version2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$11,($B2=$B$2:$B$11) * ($C2<=$D$2:$D$11) * ($D2>=$C$2:$C$11)))

Herakles, this is perfect and does exactly was I was hoping.

Thank you very much!!