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
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
Ian
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 Id | Route Of Access | Outcome | Date Of Request |
000001 | Referral Form | No Services Provided | 11/03/2020 |
000001 | Community | Further Assessment Required | 08/04/2020 |
000003 | Hospital | Further Assessment Required | 08/10/2020 |
000004 | Integrated Care | Short Term Support to Maximise Independence | 15/10/2020 |
000005 | Community | No Services Provided | 29/05/2020 |
000006 | Community | Further Assessment Required | 07/01/2020 |
000007 | Hospital | Further Assessment Required | 04/08/2020 |
000008 | Community | Further Assessment Required | 13/01/2020 |
000005 | Hospital | Further Assessment Required | 22/08/2020 |
000010 | Community | No Services Provided | 27/11/2019 |
000001 | Community | Further Assessment Required | 05/05/2020 |
000012 | Hospital | No Services Provided | 13/11/2019 |
000013 | Hospital | No Services Provided | 25/11/2019 |
000014 | Hospital | Further Assessment Required | 25/11/2019 |
000015 | Community | Further Assessment Required | 06/02/2020 |
000016 | Community | Further Assessment Required | 28/11/2019 |
000007 | Community | Further Assessment Required | 03/12/2019 |
000018 | Community | Further Assessment Required | 04/12/2019 |
000019 | Community | Further Assessment Required | 24/10/2019 |
000020 | Community | No Services Provided | 30/10/2019 |
000021 | Hospital | No Services Provided | 24/01/2020 |
000022 | Hospital | Further Assessment Required | 27/02/2020 |
000022 | Hospital | Further Assessment Required | 25/03/2020 |
[SET 2] | |||
Client iD | Service Status | Date Agreement Started | Date Agreement Ended |
000001 | Current | 20/Sep/2019 | 31/Oct/2019 |
000001 | Closed | 17/Feb/2020 | 07/May/2020 |
000003 | Closed | 01/Sep/2019 | 31/Aug/2020 |
000004 | Closed | 01/Sep/2019 | 31/Aug/2020 |
000005 | Current | 19/Oct/2019 | |
000006 | Current | 08/Apr/2019 | |
000001 | Current | 06/May/2019 | |
000002 | Current | 27/Feb/2019 | |
000009 | Closed | 13/Mar/2020 | 16/Oct/2020 |
000010 | Current | 03/Nov/2020 | |
000001 | Current | 17/May/2019 | |
000012 | Current | 28/Apr/2014 | |
000013 | Current | 01/Jul/2020 | |
000014 | Closed | 14/Jul/2020 | 14/Jul/2020 |
000003 | Closed | 26/Mar/2020 | 08/Jun/2020 |
000004 | Closed | 20/Jul/2020 | 21/Jul/2020 |
000005 | Closed | 22/Aug/2020 | 23/Aug/2020 |
000006 | Current | 24/Aug/2020 | |
000001 | Current | 24/Aug/2020 | |
000002 | Current | 16/Sep/2019 | |
000021 | Closed | 03/Feb/2020 | 25/Sep/2020 |
000022 | Closed | 03/Feb/2020 | 25/Sep/2020 |
000022 | Closed | 26/Sep/2020 | 02/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
Ian