Add rows with specific content after duplicates Excel

[Tita_86]

Hi All,
I'm trying to find the best way to add an additional row that tells me when the "Exclusion status" column is rejected. Unfortunately, I don't have this data and I need to add this in, but I'm totally failing at this.

All I want to do is to add a row below each "Indicator" (column B) that will copy the last row with data. Then I want to change the ''numerator'' to be the number that will complete the 'denominator' value and change the 'Exclusion Status' to 'Rejected'.
For example, indicator PI01 has numerator=63 out of denominator=149 for 'Accepted' Exclusion Status. I need to add then an additional row below with the remaining numerator=86 to complete the 149. This will be 'Rejected' Exclusion Status for this Indicator.
I added an example below of what I'm trying to do. The yellow rows are what I need in this case.

I hope you can give me some advice, since I really don't know how to do this. I'm quite new with data.. Thanks so much!

 

[Joe4]

Welcome to the Board!

It would be most helpful to us to have a copy of your data that we can work with to create this for you. You have posted an image that we cannot do anything with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Since we cannot tell from a picture which, if any cells have formulas and we cannot copy the data for testing I have made some guesses.

Here is my sample data. Note that I have assumed the Percentage column contains formulas.

Tita_86.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Numerator	Denominator	Percentage	Hdr17	Hdr18
2	Dec-21	P101					x							63	149	42%	Accepted	Draft
3	Dec-21	P102					x					H		1	63	2%	Accepted	Draft
4	Dec-21	P102					x					L		9	63	14%	Accepted	Draft
5	Dec-21	P102					x					N		53	63	84%	Accepted	Draft
6	Dec-21	P103	0-4	F	IP		x							216	440	49%	Accepted	Draft
7	Dec-21	P103	0-4	F	TH		x							1	440	0%	Accepted	Draft
Sheet1

Cell Formulas
Range	Formula
P2:P7	P2	=N2/O2

I run this vba code

Sub Rejected()
  Dim r As Long, RejRw As Long, Num As Long
  
  Application.ScreenUpdating = False
  For r = Range("B" & Rows.Count).End(xlUp).Row + 1 To 3 Step -1
    Num = Num + Range("N" & r).Value
    If Range("B" & r).Value <> Range("B" & r - 1).Value Then
      Rows(r).Insert
      
      Rows(r - 1).Copy Destination:=Rows(r)
      If Num <> 0 Then
        With Rows(RejRw + 1)
          .Cells(12).ClearContents
          .Cells(14).Value = .Cells(15).Value - Num
          .Cells(17).Value = "Rejected"
        End With
        Num = 0
      End If
      RejRw = r
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

This is what I ended up with

Tita_86.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Numerator	Denominator	Percentage	Hdr17	Hdr18
2	Dec-21	P101					x							63	149	42%	Accepted	Draft
3	Dec-21	P101					x							86	149	58%	Rejected	Draft
4	Dec-21	P102					x					H		1	63	2%	Accepted	Draft
5	Dec-21	P102					x					L		9	63	14%	Accepted	Draft
6	Dec-21	P102					x					N		53	63	84%	Accepted	Draft
7	Dec-21	P102					x							0	63	0%	Rejected	Draft
8	Dec-21	P103	0-4	F	IP		x							216	440	49%	Accepted	Draft
9	Dec-21	P103	0-4	F	TH		x							1	440	0%	Accepted	Draft
10	Dec-21	P103	0-4	F	TH		x							223	440	51%	Rejected	Draft
Sheet1

Cell Formulas
Range	Formula
P2:P10	P2	=N2/O2

Are we headed in the right direction with any of that?