Help inserting blank cell after specific string

Question

Hello,

I am working on a project and I am trying to work on fixing formulas, but I am running into an issue where I can't figure out the best way to create a space after the "P-" product cell. Currently I have the top cells referencing the bottom and just manually creating a space after the "P-" cell is filled. What is the best way to automatically create the space after that cell given each product has a different formula and the "P-" location will change and to have the word "Holdout" appear in the leftmost column of the blank cell row?

	Product	% Formula
Formula	T-150	10.0%
	L-800	5.0%
	A-100	9.0%
	A-200	30.0%
	P-200	56.5%
Holdout
	L-800	19.3%

Line	Material	Quantity
1	T-150	10.0%
2	L-800	5.0%
3	A-100	9.0%
4	A-200	0.3%
5	P-200	56.5%
6	L-800	19.3%

Another thing I am trying to work on is anything after the space is to have whatever is under the space be 10% so the finished goal is to look like this:

	Product	% Formula
Formula	T-150	10.0%
	L-800	14.3%
	A-100	9.0%
	A-200	30.0%
	P-200	56.5%
Holdout
	L-800	10%

Another example:

	Product	% Formula
Formula	T-350	44.0%
	L-700	8.0%
	A-300	1.0%
	P-200	22.0%
Holdout
	L-700	20.0%
	Z-600	5%

Line	Material	Quantity
1	T-350	44.0%
2	L-700	8.0%
3	A-300	1.0%
4	P-200	22.0%
5	L-700	20.0%
6	Z-600	5.0%

End Goal:

	Product	% Formula
Formula	T-350	44.0%
	L-700	23.0%
	A-300	1.0%
	P-200	22.0%
Holdout
	L-700	5%
	Z-600	5%

Any help is appreciated. Thanks

Answer 1

The first two column can use formula to get. But I have no idea on the third column.

E2 is text formula

E3 formula:

=IF(LEFT(A2,2)="P-","Holdout","")

Formula in F2, Array formula need enough space to put the result, otherwise it will get spill error.

=VSTACK(TEXTSPLIT(TEXTJOIN(",",,IF(ROW(A2:A7)-1<=MATCH("P-*",A2:A7,0),A2:A7,"")),,","),"",TEXTSPLIT(TEXTJOIN(",",,IF(ROW(A2:A7)-1>MATCH("P-*",A2:A7,0),A2:A7,"")),,","))

Formula in G2 and fill down.

=IF(F2="","",IF(COUNTBLANK($F$2:F2),10%/SUM(--(ROW($A$2:$A$7)-1>MATCH("P-*",$A$2:$A$7,0))),IF(COUNTIF($A$2:$A$7,F2)>1,SUMIF($A$2:$A$7,F2,$B$2:$B$7)-10%/SUM(--(ROW($A$2:$A$7)-1>MATCH("P-*",$A$2:$A$7,0))),SUMIF($A$2:$A$7,F2,$B$2:$B$7))))

I will send the test file to you as attachment in private message.

    [![](https://learn-attachment.microsoft.com/api/attachments/66ddfa6e-e8e0-4bba-9f2c-8e2875cad8fa?platform=QnA