Grouping assistance

[bvolles]

Hello,

I am hoping someone can help or at least lead me in the right direction.  My use case is I have a list of people and what I am wanting to show is if a person ONLY switches to a different package grouping.  For example if a person goes from 38PL to 38PP I would not want to see that because its within package 1 for 2016 but if a person goes from 38PL to 38PT I would want to see that because its a different package grouping as shown below.

• 2016:
  • 1 package – 38PL, 38PP, 38PM, 38PQ, 38PN, 38PR, 38PO, 38PS
  • 2 package – 38PT, 38PX, 38PU, 38PY, 38PV, 38PZ, 38PW, 38P0
  • 3 package – 38P1, 38P2, 38P3, 38P4
• 2017:
  • 1 package – 4EJA, 4EJC, 4EJD, 4EJE, 4EJF, 4EJG, 4EJH, 4EJI
  • 2 package – 4EJJ, 4EJK, 4EJL, 4EJM, 4EJN, 4EJO, 4EJP, 4EJQ
  • 3 package – 4EI5, 4EI6, 4EI8, 4EI9
• 2018:
  • 1 package – 4EJJ, 4EJL, 4EJN, 4EJP
  • packages – 4EI5, 4EI6, 4EI8, 4EI9

[Kenda]

@bvolles Can you provide some sample rows of data so we can see how it is set up?

[bvolles]

Person	Old Package	Start	End	New Package	Start	End	Description
11111111111	38P4	20160101	20160119	38P3	20160120	20160126	70/30 package
22222222222	38P3	20160120	20160126	38P4	20160127	20161231	70/30 package
33333333333	38PW	20160101	20160131	38PV	20160201	20160630	80/20 package
44444444444	38PW	20160101	20160131	38P3	20160701	20161231	80/20 package
55555555555	38P4	20160101	20160131	38P3	20160201	20160531	70/30 package

Here is an example

[Kenda]

@bvolles See if the attached workbook accomplishes what you're looking for. It works for the sample data set you provided. I wasn't sure which field the years you provided in your question should match with, so I used the first start column. 

As you will see, I used some Find Replace tools and changed each of the packages' codes in a group to the same code. This way, you can just use a formula to check if they are the same or not. 

Hope this helps!

[Kenda · Accepted solution]

@bvolles Attached is another way that should work using just a bunch of filters. These check if any of the values in each of the package groups is present in both the old and new columns. Here, you will want the F outputs.

[bvolles]

Thank you...I am testing with the real data now...I will let you know once I validate

[bvolles]

It looks like the last one is getting me what I need.  I appreciate the assistance!