Fill and expand
 

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.
--
Geoff

Fill and expand
 

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:D$100)*(F1<=E$1:E$100),0 )),0,INDEX(C$1:C$100,MATCH(1,(F1=D$1:D$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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Geoffric" wrote:
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.
--
Geoff

Fill and expand
 

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:

C00123
C00124
C00125
C00126
C00127
C00128
C00129
C00130
C00131,

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:


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

Thank you for any help you can offer.

Regards,


--
Geoff


"Max" wrote:

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:D$100)*(F1<=E$1:E$100),0 )),0,INDEX(C$1:C$100,MATCH(1,(F1=D$1:D$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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Geoffric" wrote:
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.
--
Geoff

Fill and expand
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Fill and expand
 

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


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---