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

L

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Lyons550 said:
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:
Click to expand...
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:
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
Fluff said:
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"
Click to expand...
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]
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
alansidman said:
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"
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
546
skyh3ck
skyh3ck
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
R
  • Solved
Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)
Replies
8
Views
605
rebel123
R
V
  • Question Question
Bank Reconciliation with the help of VBA
Replies
3
Views
1K
Dave Patton
D

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top