A Problem I've NEVER EVER been able to find an answer for...

Lyons550

New Member
Joined
Nov 12, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Ok, first and formost I'm a newbie here so apologies if this has already been answered but as we all phrase things different ways i couldn't find anything similar to my request....so,with that in mind this is an issue that I have encountered many times in my job but never been able to truely address it other than a mix of vlookup and manual checking.

I regulary have 2 x datasets to compare, [set 1] includes requests for support from our team and [set 2] shows people who were already reciving a service from us. Ther are MULTIPLES in both sets.

So what I need to do is identify for each row in [set 1] based on the "Client iD" if there was a "service" active in [set 2] at the time the "request" came through in [set 1]. If there was i'd return "Existing Customer", otherise "New Customer" in the cell with the formula.

I have included examples of both [set] structures below...Also, In additon to understanding the Excel formula, i also need to build this into Powery Query moving forward...so if anyone is able to provide the MCode as well that would be SUPERB!!

Anyway...here are the [sets]...I really hope someone out there is able to help me :)
[SET 1]
Client IdRoute Of AccessOutcomeDate Of Request
000001Referral FormNo Services Provided11/03/2020
000001CommunityFurther Assessment Required08/04/2020
000003HospitalFurther Assessment Required08/10/2020
000004Integrated CareShort Term Support to Maximise Independence15/10/2020
000005CommunityNo Services Provided29/05/2020
000006CommunityFurther Assessment Required07/01/2020
000007HospitalFurther Assessment Required04/08/2020
000008CommunityFurther Assessment Required13/01/2020
000005HospitalFurther Assessment Required22/08/2020
000010CommunityNo Services Provided27/11/2019
000001CommunityFurther Assessment Required05/05/2020
000012HospitalNo Services Provided13/11/2019
000013HospitalNo Services Provided25/11/2019
000014HospitalFurther Assessment Required25/11/2019
000015CommunityFurther Assessment Required06/02/2020
000016CommunityFurther Assessment Required28/11/2019
000007CommunityFurther Assessment Required03/12/2019
000018CommunityFurther Assessment Required04/12/2019
000019CommunityFurther Assessment Required24/10/2019
000020CommunityNo Services Provided30/10/2019
000021HospitalNo Services Provided24/01/2020
000022HospitalFurther Assessment Required27/02/2020
000022HospitalFurther Assessment Required25/03/2020

[SET 2]
Client iDService StatusDate Agreement StartedDate Agreement Ended
000001Current20/Sep/201931/Oct/2019
000001Closed17/Feb/202007/May/2020
000003Closed01/Sep/201931/Aug/2020
000004Closed01/Sep/201931/Aug/2020
000005Current19/Oct/2019
000006Current08/Apr/2019
000001Current06/May/2019
000002Current27/Feb/2019
000009Closed13/Mar/202016/Oct/2020
000010Current03/Nov/2020
000001Current17/May/2019
000012Current28/Apr/2014
000013Current01/Jul/2020
000014Closed14/Jul/202014/Jul/2020
000003Closed26/Mar/202008/Jun/2020
000004Closed20/Jul/202021/Jul/2020
000005Closed22/Aug/202023/Aug/2020
000006Current24/Aug/2020
000001Current24/Aug/2020
000002Current16/Sep/2019
000021Closed03/Feb/202025/Sep/2020
000022Closed03/Feb/202025/Sep/2020
000022Closed26/Sep/202002/Oct/2020

PS I would've pasted as a range however my work PC will not allow me to install add ins to do so, so it's simple .jpegs i'm afraid :cry:
Ian
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,447
PS I would've pasted as a range however my work PC will not allow me to install add ins to do so, so it's simple .jpegs i'm afraid :cry:
you can post a link to the shared excel file with representative source data AND expected result using onedrive, googledrive, dropbox or any similar services
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Not really sure what your output should look like. Given your examples, please provide a mocked up solution so that we know what exactly you are looking for.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
ABCDE
1Client IdRoute Of AccessOutcomeDate Of Request
21Referral FormNo Services Provided11/03/2020Existing customer
31CommunityFurther Assessment Required08/04/2020Existing customer
43HospitalFurther Assessment Required08/10/2020New customer
54Integrated CareShort Term Support to Maximise Independence15/10/2020New customer
65CommunityNo Services Provided29/05/2020Existing customer
76CommunityFurther Assessment Required07/01/2020Existing customer
87HospitalFurther Assessment Required04/08/2020New customer
98CommunityFurther Assessment Required13/01/2020New customer
105HospitalFurther Assessment Required22/08/2020Existing customer
1110CommunityNo Services Provided27/11/2019New customer
121CommunityFurther Assessment Required05/05/2020Existing customer
1312HospitalNo Services Provided13/11/2019Existing customer
1413HospitalNo Services Provided25/11/2019New customer
1514HospitalFurther Assessment Required25/11/2019New customer
1615CommunityFurther Assessment Required06/02/2020New customer
1716CommunityFurther Assessment Required28/11/2019New customer
187CommunityFurther Assessment Required03/12/2019New customer
1918CommunityFurther Assessment Required04/12/2019New customer
2019CommunityFurther Assessment Required24/10/2019New customer
2120CommunityNo Services Provided30/10/2019New customer
2221HospitalNo Services Provided24/01/2020New customer
2322HospitalFurther Assessment Required27/02/2020Existing customer
2422HospitalFurther Assessment Required25/03/2020Existing customer
Sheet1
Cell Formulas
RangeFormula
E2:E24E2=IF(SUMPRODUCT((Sheet2!$A$2:$A$100=A2)*(Sheet2!$C$2:$C$100<=D2)*((Sheet2!$D$2:$D$100>=D2)+(Sheet2!$D$2:$D$100=""))),"Existing","New")&" customer"
 

Lyons550

New Member
Joined
Nov 12, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
ABCDE
1Client IdRoute Of AccessOutcomeDate Of Request
21Referral FormNo Services Provided11/03/2020Existing customer
31CommunityFurther Assessment Required08/04/2020Existing customer
43HospitalFurther Assessment Required08/10/2020New customer
54Integrated CareShort Term Support to Maximise Independence15/10/2020New customer
65CommunityNo Services Provided29/05/2020Existing customer
76CommunityFurther Assessment Required07/01/2020Existing customer
87HospitalFurther Assessment Required04/08/2020New customer
98CommunityFurther Assessment Required13/01/2020New customer
105HospitalFurther Assessment Required22/08/2020Existing customer
1110CommunityNo Services Provided27/11/2019New customer
121CommunityFurther Assessment Required05/05/2020Existing customer
1312HospitalNo Services Provided13/11/2019Existing customer
1413HospitalNo Services Provided25/11/2019New customer
1514HospitalFurther Assessment Required25/11/2019New customer
1615CommunityFurther Assessment Required06/02/2020New customer
1716CommunityFurther Assessment Required28/11/2019New customer
187CommunityFurther Assessment Required03/12/2019New customer
1918CommunityFurther Assessment Required04/12/2019New customer
2019CommunityFurther Assessment Required24/10/2019New customer
2120CommunityNo Services Provided30/10/2019New customer
2221HospitalNo Services Provided24/01/2020New customer
2322HospitalFurther Assessment Required27/02/2020Existing customer
2422HospitalFurther Assessment Required25/03/2020Existing customer
Sheet1
Cell Formulas
RangeFormula
E2:E24E2=IF(SUMPRODUCT((Sheet2!$A$2:$A$100=A2)*(Sheet2!$C$2:$C$100<=D2)*((Sheet2!$D$2:$D$100>=D2)+(Sheet2!$D$2:$D$100=""))),"Existing","New")&" customer"
Firstly many thanks for the replies all, much appreciated.

Fluff, This is so close but when I copy the formula down it shows a #SPILL error
[SET 1]
Client IdRoute Of AccessOutcomeDate Of RequestClient Status At time Of Request
1​
Referral FormNo Services Provided
11/03/2020​
#SPILL!​
1​
CommunityFurther Assessment Required
08/04/2020​
#SPILL!​
3​
HospitalFurther Assessment Required
08/10/2020​
#SPILL!​
4​
Integrated CareShort Term Support to Maximise Independence
15/10/2020​
#SPILL!​
5​
CommunityNo Services Provided
29/05/2020​
#SPILL!​
6​
CommunityFurther Assessment Required
07/01/2020​
#SPILL!​
7​
HospitalFurther Assessment Required
04/08/2020​
#SPILL!​
8​
CommunityFurther Assessment Required
13/01/2020​
#SPILL!​

The range will be anything up to 50,000 records for each [SET]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Not sure why you would get a spill error, but if you have that many rows, this should be more efficient.
+Fluff v2.xlsm
ABCDE
1Client IdRoute Of AccessOutcomeDate Of Request
21Referral FormNo Services Provided11/03/2020Existing Customer
31CommunityFurther Assessment Required08/04/2020Existing Customer
43HospitalFurther Assessment Required08/10/2020New Customer
54Integrated CareShort Term Support to Maximise Independence15/10/2020New Customer
65CommunityNo Services Provided29/05/2020Existing Customer
76CommunityFurther Assessment Required07/01/2020Existing Customer
87HospitalFurther Assessment Required04/08/2020New Customer
98CommunityFurther Assessment Required13/01/2020New Customer
105HospitalFurther Assessment Required22/08/2020Existing Customer
1110CommunityNo Services Provided27/11/2019New Customer
121CommunityFurther Assessment Required05/05/2020Existing Customer
1312HospitalNo Services Provided13/11/2019Existing Customer
1413HospitalNo Services Provided25/11/2019New Customer
1514HospitalFurther Assessment Required25/11/2019New Customer
1615CommunityFurther Assessment Required06/02/2020New Customer
1716CommunityFurther Assessment Required28/11/2019New Customer
187CommunityFurther Assessment Required03/12/2019New Customer
1918CommunityFurther Assessment Required04/12/2019New Customer
2019CommunityFurther Assessment Required24/10/2019New Customer
2120CommunityNo Services Provided30/10/2019New Customer
2221HospitalNo Services Provided24/01/2020New Customer
2322HospitalFurther Assessment Required27/02/2020Existing Customer
2422HospitalFurther Assessment Required25/03/2020Existing Customer
25
Sheet1
Cell Formulas
RangeFormula
E2:E24E2=IF(COUNT(FILTER(Sheet2!$A$2:$A$50000,(Sheet2!$A$2:$A$50000=A2)*(Sheet2!$C$2:$C$50000<=D2)*((Sheet2!$D$2:$D$50000>=D2)+(Sheet2!$D$2:$D$50000=""))))>0,"Existing","New")&" Customer"
 

Lyons550

New Member
Joined
Nov 12, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Wow! Thank you so much @Fluff, much appreciated...never used the FILTER formula before, it works a treat! :) Will now explore M code to see what the equivilant will be in Power Query now...once again many thanks!

Ian
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Using Power Query

Book1
ABCDEF
1Merged.1Merged.2Merged.3Merged.4Client iD.1Custom
21Referral FormNo Services Provided3/11/20201Existing
31CommunityFurther Assessment Required4/8/20201Existing
41CommunityFurther Assessment Required5/5/20201Existing
53HospitalFurther Assessment Required10/8/20203Existing
64Integrated CareShort Term Support to Maximise Independence10/15/20204Existing
75CommunityNo Services Provided5/29/20205Existing
85HospitalFurther Assessment Required8/22/20205Existing
96CommunityFurther Assessment Required1/7/20206Existing
102New
1110CommunityNo Services Provided11/27/201910Existing
1212HospitalNo Services Provided11/13/201912Existing
1313HospitalNo Services Provided11/25/201913Existing
1414HospitalFurther Assessment Required11/25/201914Existing
1521HospitalNo Services Provided1/24/202021Existing
1622HospitalFurther Assessment Required2/27/202022Existing
1722HospitalFurther Assessment Required3/25/202022Existing
187HospitalFurther Assessment Required8/4/2020New
197CommunityFurther Assessment Required12/3/2019New
208CommunityFurther Assessment Required1/13/2020New
2115CommunityFurther Assessment Required2/9/2020New
2216CommunityFurther Assessment Required11/28/2019New
2318CommunityFurther Assessment Required12/4/2019New
2419CommunityFurther Assessment Required10/24/2019New
2520CommunityNo Services Provided10/30/2019New
Sheet3


Power Query:
let
    Source = Table.NestedJoin(Set1, {"Client Id"}, Set2, {"Client iD"}, "Set2", JoinKind.FullOuter),
    #"Expanded Set2" = Table.ExpandTableColumn(Source, "Set2", {"Client iD"}, {"Client iD.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Set2", {{"Client Id", type text}, {"Date Of Request", type text}}, "en-US"),{"Client Id", "Route Of Access", "Outcome", "Date Of Request"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Merged.1]=[Client iD.1] then "Existing" else "New")
in
    #"Added Custom"
 

Lyons550

New Member
Joined
Nov 12, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Using Power Query

Book1
ABCDEF
1Merged.1Merged.2Merged.3Merged.4Client iD.1Custom
21Referral FormNo Services Provided3/11/20201Existing
31CommunityFurther Assessment Required4/8/20201Existing
41CommunityFurther Assessment Required5/5/20201Existing
53HospitalFurther Assessment Required10/8/20203Existing
64Integrated CareShort Term Support to Maximise Independence10/15/20204Existing
75CommunityNo Services Provided5/29/20205Existing
85HospitalFurther Assessment Required8/22/20205Existing
96CommunityFurther Assessment Required1/7/20206Existing
102New
1110CommunityNo Services Provided11/27/201910Existing
1212HospitalNo Services Provided11/13/201912Existing
1313HospitalNo Services Provided11/25/201913Existing
1414HospitalFurther Assessment Required11/25/201914Existing
1521HospitalNo Services Provided1/24/202021Existing
1622HospitalFurther Assessment Required2/27/202022Existing
1722HospitalFurther Assessment Required3/25/202022Existing
187HospitalFurther Assessment Required8/4/2020New
197CommunityFurther Assessment Required12/3/2019New
208CommunityFurther Assessment Required1/13/2020New
2115CommunityFurther Assessment Required2/9/2020New
2216CommunityFurther Assessment Required11/28/2019New
2318CommunityFurther Assessment Required12/4/2019New
2419CommunityFurther Assessment Required10/24/2019New
2520CommunityNo Services Provided10/30/2019New
Sheet3


Power Query:
let
    Source = Table.NestedJoin(Set1, {"Client Id"}, Set2, {"Client iD"}, "Set2", JoinKind.FullOuter),
    #"Expanded Set2" = Table.ExpandTableColumn(Source, "Set2", {"Client iD"}, {"Client iD.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Set2", {{"Client Id", type text}, {"Date Of Request", type text}}, "en-US"),{"Client Id", "Route Of Access", "Outcome", "Date Of Request"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Merged.1]=[Client iD.1] then "Existing" else "New")
in
    #"Added Custom"
Thanks for having an attempt @alansdiman but I think all you've done is merge the two datasets on the [client id] rather than trying to identify for each record in SET 1 wether any of the records for the [Client id] in SET2 were open or active at the time of the [Date Of Request] in SET 1... but thnks for replying. Much appreciated :) Ian
 

Watch MrExcel Video

Forum statistics

Threads
1,119,093
Messages
5,576,061
Members
412,696
Latest member
khanhvy31
Top