Hello,
On [Sheet 1] I have a listing of certain Customers, Customer IDs, and Times within my population. On [Sheet 2] I have a complete listing of all the Customer IDs and an Event Time.
[Sheet 1]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer
[/TD]
[TD]Customer ID
[/TD]
[TD]Time (TIMESTAMP)
[/TD]
[TD]Looking to see if a Event Time occurred within 180 days prior to the [Time] column (column C).
[/TD]
[TD]Looking to see if a Event Time occurred within 4 days after the [Time] column (column C).
[/TD]
[/TR]
[TR]
[TD]Mr X
[/TD]
[TD]1
[/TD]
[TD]12/13/2017 9:21 AM
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Mr XX
[/TD]
[TD]2
[/TD]
[TD]12/13/2017 10:33 AM
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Mrs Y
[/TD]
[TD]3
[/TD]
[TD]12/13/2017 8:56 PM
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Ms Z
[/TD]
[TD]4
[/TD]
[TD]12/13/2017 2:31 AM
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]
[Sheet 2]
Sheet 2 contains every single record of each of the four Customer IDs and the Event Time.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer ID
[/TD]
[TD]Event Time
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]12/13/2017 9:20 AM
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]8/4/2016 10:22 AM
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]6/12/2017 5:35 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1/1/2018 11:24 AM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12/15/2017 8:59 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/12/2018 6:35 AM
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12/14/2017 4:55 PM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]9/1/2017 7:33 AM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]12/16/2017 8:10 AM
[/TD]
[/TR]
</tbody>[/TABLE]
So what I am asking is to create to column on [Sheet 1] - the ones with bold headers. Look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an event happen within 180 days prior to the [Time] column. Also, look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an even happen within 4 days after the [Time] column.
I hope this make sense.... Time is a factor as well.
On [Sheet 1] I have a listing of certain Customers, Customer IDs, and Times within my population. On [Sheet 2] I have a complete listing of all the Customer IDs and an Event Time.
[Sheet 1]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer
[/TD]
[TD]Customer ID
[/TD]
[TD]Time (TIMESTAMP)
[/TD]
[TD]Looking to see if a Event Time occurred within 180 days prior to the [Time] column (column C).
[/TD]
[TD]Looking to see if a Event Time occurred within 4 days after the [Time] column (column C).
[/TD]
[/TR]
[TR]
[TD]Mr X
[/TD]
[TD]1
[/TD]
[TD]12/13/2017 9:21 AM
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Mr XX
[/TD]
[TD]2
[/TD]
[TD]12/13/2017 10:33 AM
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Mrs Y
[/TD]
[TD]3
[/TD]
[TD]12/13/2017 8:56 PM
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Ms Z
[/TD]
[TD]4
[/TD]
[TD]12/13/2017 2:31 AM
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]
[Sheet 2]
Sheet 2 contains every single record of each of the four Customer IDs and the Event Time.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer ID
[/TD]
[TD]Event Time
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]12/13/2017 9:20 AM
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]8/4/2016 10:22 AM
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]6/12/2017 5:35 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1/1/2018 11:24 AM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12/15/2017 8:59 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/12/2018 6:35 AM
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12/14/2017 4:55 PM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]9/1/2017 7:33 AM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]12/16/2017 8:10 AM
[/TD]
[/TR]
</tbody>[/TABLE]
So what I am asking is to create to column on [Sheet 1] - the ones with bold headers. Look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an event happen within 180 days prior to the [Time] column. Also, look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an even happen within 4 days after the [Time] column.
I hope this make sense.... Time is a factor as well.
