Data pick-up
 

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 row 2. The intervening range has a default percentage
rate of 0%.

If I have a separate list of just data such as:

C00123
C00124
C00125
C00126
C00127
C00128
C00129
C00130
C00131

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. 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.

--
Geoff

Data pick-up
 

Try this macro where col a,b,c has the original, col e has the list and
desired in col f. Adjust to suit. Based on looking at the right 3 characters
as described in OP.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=====
Sub reordervalues()
For i = 2 To Cells(Rows.Count, "e").End(xlUp).Row
Cells(i, "f") = 0
x = Right(Cells(i, "e"), 3)
For j = 2 To Cells(Rows.Count, "a").End(xlUp).Row
If x = Right(Cells(j, "a"), 3) And _
x <= Right(Cells(j, "b"), 3) Then _
Cells(i, "F") = Cells(j, "c")
Next j
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Geoffric" wrote in message
...
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 row 2. The intervening range has a default percentage
rate of 0%.

If I have a separate list of just data such as:

C00123
C00124
C00125
C00126
C00127
C00128
C00129
C00130
C00131

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. 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.

--
Geoff