Fill and expand

[Geoffric]

I have information exported from an accounting system into an Excel
spreadsheet. The export has 3 columns; the first column contains the value of
the first data of the range; the second column contains the end value of the
range; the third column shows a percentage rate applicable to all values
within the range. e.g

C00123 C00125 15%
C00128 C00130 100%

Note there is a gap between the end value at column 2 in row 1 and the next
value in column 1 of row2. The intervening range has a default percentage
rate of 0%.

Is there a way to expand and fill the/a spreadsheet so that the result looks
like this:

C00123 15%
C00124 15%
C00125 15%
C00126 0%
C00127 0%
C00128 100%
C00129 100%
C00130 100%
C00131 0% et seq

I would greatly appreciate any help.

 

[Max]

One play which should deliver it for you ..

Assume source data in cols A to C, from row1 down
Put in D1: =SUBSTITUTE(A1,"C","")+0
Copy D1 to E1, fill down to last row of source data

Then place
In F1: =$D$1+ROWS($1:1)-1
In G1: ="C"&TEXT(F1,"00000")
In H1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula)
=IF(ISNA(MATCH(1,(F1>=D$1$100)*(F1<=E$1:E$100),0)),0,INDEX(C$1:C$100,MATCH(1,(F1>=D$1$100)*(F1<=E$1:E$100),0)))
Format H1 as percentage to taste. Adapt the ranges in H1 to suit your actual
extents. Then select F1:H1, fill down as far as required. Cols G & H should
return the exact results that you seek.

 

[Geoffric]

Superb reply, Max. It works perfectly. May I ask a supplementary question?
Instead of listing out the info as per your reply, is it possible to allocate
the data in column 3 of the original export (without expanding it) to a list
of data, by a formula that refers to the original export? i.e. if I had a
list of just data such as:

I could use a formula copied down in the next column that would refer to the
original export, and pick up the 3rd column data, including the data not
shown therein (i.e. the 0% allocated to the "invisible" ranges between the
ranges shown) to arrive at:

Thank you for any help you can offer.

Regards,

 

[Max]

Superb reply, Max. It works perfectly.
Welcome, glad it did.

May I ask a supplementary question?

Usually best to do this as a fresh posting,
for maximum visibility to all possible responders

 

[Geoffric]

Thanks for the advice. I will post a new query.