Help with complicated formula

[Todd Kolar]

I'm a pretty avid Excel formula writer, however, I cannot figure out how to make this particular challenge work.
Attached is a screenshot of what I'm trying to accomplish.
I have a list of names in column B that have to repeat in several rows in Column I.
The rows needed are shown in column G which shows how many times the name needs to repeat.
For instance names "R15,109" and "XX,2" need only one instance so they are in order in column I as 1 and 2.
However, name "R15,111-118" needs 8 instances or rows so they are in order in column I as 3-10.
Then name "xx,19-36" needs 2 instances or rows in column I so it is at 10-11.
And you can see the pattern and the rest of the names how they need to be ordered.
The sum of the rows needed in column G total the number of rows in column H based on how many times the name needs to repeat.
Column I is where I need the formula to work and I just typed in the names for reference.
I'm at my wits end so I greatly appreciate any help on solving this one and I hope my explanation makes sense.
Thanks for reading this!

T

 

[RoryA]

For 2016, I think I'd add a running total column next to column G, then you could fill your row number sequence down and use an INDEX/MATCH to get the right list name. Well, I'd use Power Query, but you said you wanted a formula.

 

[Glenn Kennedy]

I2, copied down:

=IFERROR(INDEX($B$2:$B$6,MATCH(0,INDEX(--(COUNTIF($I$1:I1,$B$2:$B$6)=$G$2:$G$6),0),0)),"")

 

[RoryA]

My description was very bad, so here is an example of what I was trying to suggest!

Book3
	A	B	C	D	E	F	G	H	I	J
1	List Order	List Name	Prefix	Range	1st Number	Last Number	Rows needed	RT	Rows	Final Name
2	1	R15, 109	R15	109	109	109	1	1	1	R15, 109
3	2	XX,2	XX	2	2	2	1	2	2	XX,2
4	3	R15, 111-118	R16	111-118	111	118	8	3	3	R15, 111-118
5	4	XX,11-12	XX	11-12	11	12	2	11	4	R15, 111-118
6	5	R15,121-126	R17	121-126	121	126	6	13	5	R15, 111-118
7	6	XX,19-36	XX	19-36	19	36	18	19	6	R15, 111-118
8	7								7	R15, 111-118
9	8								8	R15, 111-118
10	9								9	R15, 111-118
11	10								10	R15, 111-118
12	11								11	XX,11-12
13	12								12	XX,11-12
14	13								13	R15,121-126
15	14								14	R15,121-126
16	15								15	R15,121-126
17	16								16	R15,121-126
18	17								17	R15,121-126
19	18								18	R15,121-126
20	19								19	XX,19-36
21	20								20	XX,19-36
22									21	XX,19-36
23									22	XX,19-36
24									23	XX,19-36
25									24	XX,19-36
26									25	XX,19-36
27									26	XX,19-36
28									27	XX,19-36
29									28	XX,19-36
30									29	XX,19-36
31									30	XX,19-36
32									31	XX,19-36
33									32	XX,19-36
34									33	XX,19-36
35									34	XX,19-36
36									35	XX,19-36
37									36	XX,19-36
38									37
39									38
40									39
41									40
42									41
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=F2-E2+1
H2:H7	H2	=SUM(G$1:G1)+1
J2:J42	J2	=IF(I2>SUM($G$2:$G$7),"",INDEX($B$2:$B$7,MATCH(I2,$H$2:$H$7,1)))

 

[Todd Kolar]

I2, copied down:

=IFERROR(INDEX($B$2:$B$6,MATCH(0,INDEX(--(COUNTIF($I$1:I1,$B$2:$B$6)=$G$2:$G$6),0),0)),"")

Wow!!! That worked like a charrm!!!! I never could understand the proper use of index with match so thank you soooooooooo very much! You are a lifesaver!

 

[Todd Kolar]

My description was very bad, so here is an example of what I was trying to suggest!

Thank you for the feedback as I will definitely use this in future formulas!