Find out how many different places are visited by each salesperson within a period (date range)

[mukulesh]

I have a table showing different sales persons visiting different places and their period of stay in those places. Now, I want to find out which salespersons visited more than one city within a given range of dates. Sample sheet has been attached. My results should look like the cells colored in 'green' (i.e. in the range: H2:N2)

Thanks in advance.

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Place	Arrival Date	Departure Date		Period From	Period To	Davis	Henry	John	Kate	Mitchel	Naomi	Peter
2	Peter	London	01-02-2021	03-02-2021		01-02-2021	28-02-2021	Yes	No	No	Yes	Yes	No	No
3	Davis	Kairo	04-02-2021	07-02-2021
4	Naomi	New York	02-01-2021	06-01-2021
5	Henry	London	03-01-2021	05-01-2021
6	Kate	Singapore	12-01-2021	17-01-2021
7	Davis	Dubai	10-02-2021	12-02-2021
8	Peter	Paris	15-01-2021	19-01-2021
9	Kate	New Delhi	05-02-2021	08-02-2021
10	John	Dubai	04-05-2021	08-05-2021
11	Mitchel	London	18-02-2021	21-02-2021
12	Henry	Paris	07-01-2021	12-01-2021
13	Naomi	New Delhi	08-01-2021	15-01-2021
14	Peter	London	24-02-2021	28-02-2021
15	Mitchel	Dubai	23-02-2021	24-02-2021
16	Mitchel	London	26-02-2021	28-02-2021
17	Naomi	Singapore	12-02-2021	16-02-2021
18	Kate	New Delhi	21-02-2021	25-02-2021
19	Henry	Paris	15-01-2021	20-01-2021
20	Davis	London	25-02-2021	28-02-2021
21	Kate	Dubai	12-02-2021	18-02-2021
Sheet1

 

[alansidman]

If you use Power Query, the following will generate a report that shows who travelled during that time frame and if they visited more than one city.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Arrival Date", type date}, {"Departure Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Arrival Date] >= #date(2021, 2, 1) and [Arrival Date] <= #date(2021, 2, 28)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Departure Date] >= #date(2021, 2, 1) and [Departure Date] <= #date(2021, 2, 28)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Count]>1 then "Yes" else "NO"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

 

[mukulesh]

If you use Power Query, the following will generate a report that shows who travelled during that time frame and if they visited more than one city.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Arrival Date", type date}, {"Departure Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Arrival Date] >= #date(2021, 2, 1) and [Arrival Date] <= #date(2021, 2, 28)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Departure Date] >= #date(2021, 2, 1) and [Departure Date] <= #date(2021, 2, 28)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Count]>1 then "Yes" else "NO"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

Thanks for your response. Unfortunately I'm not well versed with Power Query.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have MS365, you could try this

21 12 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Place	Arrival Date	Departure Date		Period From	Period To	Davis	Henry	John	Kate	Mitchel	Naomi	Peter
2	Peter	London	1/02/2021	3/02/2021		1/02/2021	28/02/2021	Yes	No	No	Yes	Yes	No	No
3	Davis	Kairo	4/02/2021	7/02/2021
4	Naomi	New York	2/01/2021	6/01/2021
5	Henry	London	3/01/2021	5/01/2021
6	Kate	Singapore	12/01/2021	17/01/2021
7	Davis	Dubai	10/02/2021	12/02/2021
8	Peter	Paris	15/01/2021	19/01/2021
9	Kate	New Delhi	5/02/2021	8/02/2021
10	John	Dubai	4/05/2021	8/05/2021
11	Mitchel	London	18/02/2021	21/02/2021
12	Henry	Paris	7/01/2021	12/01/2021
13	Naomi	New Delhi	8/01/2021	15/01/2021
14	Peter	London	24/02/2021	28/02/2021
15	Mitchel	Dubai	23/02/2021	24/02/2021
16	Mitchel	London	26/02/2021	28/02/2021
17	Naomi	Singapore	12/02/2021	16/02/2021
18	Kate	New Delhi	21/02/2021	25/02/2021
19	Henry	Paris	15/01/2021	20/01/2021
20	Davis	London	25/02/2021	28/02/2021
21	Kate	Dubai	12/02/2021	18/02/2021
>1

Cell Formulas
Range	Formula
H2:N2	H2	=IF(ROWS(UNIQUE(FILTER($B2:$B21,($A2:$A21=H1)*($C2:$C21<=$G2)*($D2:$D21>=$F2),"")))>1,"Yes","No")

 

[alansidman]

This should assist you in understanding PQ
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.