Recode values

  • Thread starter Thread starter avi
  • Start date Start date
A

avi

Hello,

I want to recode values in a MainRange according to rules found in an
IndexTable with 3 columns : the 2 first columns delimit a range and
the 3rd one is the value to assign to the MainRange values if they
lay in that range



What would be the most efficient approach (some array function I
guess)


Thanks
Avi
 
The easiest way is to set up your table with just the low value for each bin. ie, if you have

Low Limit High Limit Value
1 5 3
5 12 4
12 25 5

set up your table like

Low Limit Value
1 3
5 4
12 5

and then set up a table of formulas like

=VLOOKUP(A2,TableAbove,2)
like
=VLOOKUP(A2,$J$2:$K$4,2)
and copy it to match your original table's size, then copy and paste values from those formulas over your original table.
Bernie
 
avi brought next idea :
Hello,

I want to recode values in a MainRange according to rules found in an
IndexTable with 3 columns : the 2 first columns delimit a range and
the 3rd one is the value to assign to the MainRange values if they
lay in that range



What would be the most efficient approach (some array function I
guess)


Thanks
Avi
Click to expand...

If this is the same Q you asked in '...vb.general.discussion' then...

A1: 3,5,7
A2: 2,4,6

B1: 1; C1: 4; D1: 57
B2: 5; C2: 7; D2: 88

Option Explicit

Sub RecodeValues()
Dim vValsToRecode, vSourceArray, vTemp
Dim i As Long, j As Long, k As Long
vValsToRecode = Range("A1:A2")
vSourceArray = Range("B1:D2")
For i = LBound(vValsToRecode) To UBound(vValsToRecode)
vTemp = Split(vValsToRecode(i, 1), ",")
For j = LBound(vSourceArray) To UBound(vSourceArray)
For k = LBound(vTemp) To UBound(vTemp)
Select Case CLng(vTemp(k))
Case vSourceArray(j, 1) To vSourceArray(j, 2)
vTemp(k) = vSourceArray(j, 3)
End Select 'Case CLng(vTemp(k))
Next 'k
Next 'j
vValsToRecode(i, 1) = Join(vTemp, ",")
Next 'i
Range("A1").Resize(UBound(vValsToRecode)) = vValsToRecode
End Sub


Results:

A1: 57,88,88
A2: 57,57,88
 
avi brought next idea :





If this is the same Q you asked in '...vb.general.discussion' then...

A1: 3,5,7
A2: 2,4,6

B1: 1; C1: 4; D1: 57
B2: 5; C2: 7; D2: 88

Option Explicit

Sub RecodeValues()
  Dim vValsToRecode, vSourceArray, vTemp
  Dim i As Long, j As Long, k As Long
  vValsToRecode = Range("A1:A2")
  vSourceArray = Range("B1:D2")
  For i = LBound(vValsToRecode) To UBound(vValsToRecode)
    vTemp = Split(vValsToRecode(i, 1), ",")
    For j = LBound(vSourceArray) To UBound(vSourceArray)
      For k = LBound(vTemp) To UBound(vTemp)
        Select Case CLng(vTemp(k))
          Case vSourceArray(j, 1) To vSourceArray(j, 2)
            vTemp(k) = vSourceArray(j, 3)
        End Select 'Case CLng(vTemp(k))
      Next 'k
    Next 'j
    vValsToRecode(i, 1) = Join(vTemp, ",")
  Next 'i
  Range("A1").Resize(UBound(vValsToRecode)) = vValsToRecode
End Sub

Results:

  A1: 57,88,88
  A2: 57,57,88

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thanks Garry,

Is this array approach supposed to be faster than looping directly on
the cells address directly?

Avi
 
avi formulated on Wednesday :
Thanks Garry,

Is this array approach supposed to be faster than looping directly on
the cells address directly?

Avi
Click to expand...

You're welcome!

Looping the worksheet will always be slower whether you're reading or
writing. You can time this so you see the difference. It will be
substantial if the range is large.
 
Back
Top