Formula or Code to Add a Value AND Delete Line

[kmsprague]

Hi all,

I have a report that gives all the data as one big list. To match it up with the invoice, I need to combine and delete some lines. Basically, the breakdown is like this:

Plan	Code for Billing
Just Plan 1	1​
Just Plan 2	2​
Just Plan 3	3​
Plan 1 & 3	4​
Plan 2 & 3	5​

So If I have a person who has a combination (Plan 1 & 3 or Plan 2 & 3), I need the code to populate as 4 or 5 respectively, but delete the second line for that person.

Employee Number	Last Name	First Name	Plan	Code for Billing
1234​	Smith	Bill	Plan 1	1​
4281​	Wilson	Joe	Plan 2	2​
5678​	Miller	Mary	Plan 1	4​
5678​	Miller	Mary	Plan 2		Delete Line
7592​	Simpson	Barb	Plan 2	5​
7592​	Simpson	Barb	Plan 3		Delete Line

I've tried multiple conditioning formatting options, but so far all I've been able to do is highlight them and then manually override the code for billing and manually delete the extra line. Does anyone know of a better way to attack this? It takes up a lot of time. I appreciate all your ideas on how to go about this! Thank you in advance for your help!

 

[JamesCanale]

This should serve, but it isn't great - I haven't gotten it down to one formula.

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H
1	Employee Number	Last Name	First Name	Plan			Plan	Code
2	1234	Smith	Bill	Plan 1			Plan 1	1
3	4281	Wilson	Joe	Plan 2			Plan 2	2
4	5678	Miller	Mary	Plan 1			Plan 3	3
5	5678	Miller	Mary	Plan 2			Plan 1,Plan 2	4
6	7592	Simpson	Barb	Plan 2			Plan 2,Plan 3	5
7	7592	Simpson	Barb	Plan 3
8
9	Employee Number	Last Name	First Name	Plans	Code
10	1234	Smith	Bill	Plan 1	1
11	4281	Wilson	Joe	Plan 2	2
12	5678	Miller	Mary	Plan 1,Plan 2	4
13	7592	Simpson	Barb	Plan 2,Plan 3	5
Sheet13

Cell Formulas
Range	Formula
A10:C13	A10	=UNIQUE(A2:C7)
D10:D13	D10	=TEXTJOIN(",",TRUE,FILTER($D$2:$D$7,$A$2:$A$7=A10))
E10:E13	E10	=XLOOKUP(D10,$G$2:$G$6,$H$2:$H$6,"",0)
Dynamic array formulas.