summary of milestone

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello,
i have a range V5:IQ5, it contains values from 0+.

most of the values are the same. the range may contain 1 up to 11 different
valus.

I need to

1) make a formula for table of values in range (J5:T5) which will collect
each different values within the range (V5:IQ5).
2) then another formula to lookup from the range $V$2:$IQ$2, for the
corresponding series number where the data falls in the column. This formula
to be filled along range (J4:T4).

thanks and regards,
driller
 
Ok, since I'm not the sharpest pencil in the box when it comes to worksheet
formulas, I attacked this with a macro. I wasn't exactly sure what you
wanted displayed in J4:T4, so it displays the address where a match is found
in V2:IQ2 for unique values found in V5:IQ5. Also, since I wasn't sure
whether the values in V5:IQ5 and V2:IQ2 were integers, floating point or
what, I set up a Variant array to keep up with them.

To put this code to use, use [Alt]+[F11] to open the VB Editor. Choose
Insert and Module to create a code module in it. Copy and paste the code
below into it. Close the VB Editor. Now this code is available to be run
through Tools | Macro | Macros. The sheet that all your information is on
must be selected before running it to get the expected results. Hope this
helps.

P.S. I think I could have dealt with your #2) with a formula, it was part 1
that gave me a headache in trying to devise a worksheet formula to deal with
it. <g> But since I was coding, I just pressed on with it.


Sub ProcessMilestones()
Dim uniqueValues() As Variant
Dim rngMilestones As Range
Dim anyCell As Object
Dim LC As Integer

ReDim uniqueValues(1 To 2, 1 To 1)
'get first value
uniqueValues(1, 1) = ActiveSheet.Range("V5").Value
Set rngMilestones = _
ActiveSheet.Range("W5:IQ5")
For Each anyCell In rngMilestones
For LC = LBound(uniqueValues, 2) To UBound(uniqueValues, 2)
If anyCell.Value = uniqueValues(1, LC) Then
'already in the list, get out
Exit For ' out of LC loop
Else
'add it to the array
'and go look at next entry
ReDim Preserve uniqueValues(1 To 2, 1 To _
UBound(uniqueValues, 2) + 1)
uniqueValues(1, UBound(uniqueValues, 2)) = _
anyCell.Value
Exit For ' again, out of LC loop
End If
Next
Next
'now all unique values are in uniqueValues()
'need to find where they are in row V2:IQ2
'since it's a short list, we'll just loop through it
Set rngMilestones = ActiveSheet.Range("V2:IQ2")
For LC = LBound(uniqueValues, 2) To UBound(uniqueValues, 2)
For Each anyCell In rngMilestones
If IsNumeric(anyCell.Value) And _
anyCell.Value = uniqueValues(1, LC) Then
uniqueValues(2, LC) = anyCell.Address
Exit For ' jump out of anyCell loop
End If
Next
Next 'LC loop
'display unique values
Set rngMilestones = ActiveSheet.Range("J5")
For LC = 0 To UBound(uniqueValues, 2) - 1
'display value in row 5
rngMilestones.Offset(0, LC) = _
uniqueValues(1, LC + 1)
'display address in row 4
rngMilestones.Offset(-1, LC) = _
uniqueValues(2, LC + 1)
Next
Set rngMilestones = Nothing ' release resource

End Sub
 
Hi

To answer the first part of you post, I used Frequency as an array
formula.
I set the range of bins in G5:G15 as 0,1,2,3,4,5,6,7,8,9,10
Mark H5:H10 and array enter
{=FREQUENCY($V$5:$IQ$5,$G$5:$G$15)}

Use Control,Shift,Enter to enter or edit the formula.
Excel will create the curly braces { } if you use CSE. Do not type
them yourself.

Then in cell J5 enter
=IF(INDEX($H:$H,COLUMN(E1))>0,INDEX($G:$G,COLUMN(E1)),"")
and copy across through K5:T5
Column(E1) is used to return 5 as the index value (therefore look at H5
and G5) and will be stepped up by one as you copy across.

I am not sure what you are wishing to do for the second part of your
question.
 
Roger,
I wasn't sure if the 11 values are always the same or not - I also thought
about just looking for 11 values individually.
As for part 2, I interpreted him to mean that he wanted to find out where in
row 2 the values found in row 5 appeared (first time?) - kind of comparing
milestones perhaps? So I just gave him the address of the matches.

I'm not sure about the whole thing either - not enough information. Maybe
between the two of us, he'll be able to rig up a solution. Or come back and
fill in the blanks.
Roger Govier said:
Hi

To answer the first part of you post, I used Frequency as an array
formula.
I set the range of bins in G5:G15 as 0,1,2,3,4,5,6,7,8,9,10
Mark H5:H10 and array enter
{=FREQUENCY($V$5:$IQ$5,$G$5:$G$15)}

Use Control,Shift,Enter to enter or edit the formula.
Excel will create the curly braces { } if you use CSE. Do not type them
yourself.

Then in cell J5 enter
=IF(INDEX($H:$H,COLUMN(E1))>0,INDEX($G:$G,COLUMN(E1)),"")
and copy across through K5:T5
Column(E1) is used to return 5 as the index value (therefore look at H5
and G5) and will be stepped up by one as you copy across.

I am not sure what you are wishing to do for the second part of your
question.
Click to expand...
 
Hi Jerry

I (perhaps erroneously) thought that when the OP said 11 values from 0+
he meant 0 through 10, hence the idea of using frequency to determine
which of the 11 existed in the range V5:IQ5.

As for the second part, I hadn't a clue what was meant, but you could
well be right.
Hopefully he will come back with more info.

--
Regards

Roger Govier


JLatham said:
Roger,
I wasn't sure if the 11 values are always the same or not - I also
thought about just looking for 11 values individually.
As for part 2, I interpreted him to mean that he wanted to find out
where in row 2 the values found in row 5 appeared (first time?) - kind
of comparing milestones perhaps? So I just gave him the address of
the matches.

I'm not sure about the whole thing either - not enough information.
Maybe between the two of us, he'll be able to rig up a solution. Or
come back and fill in the blanks.
Click to expand...
 
thanks for the helpful reply.

i do some experimentation while trying the solutions availed.

..CORRECTION THE row RANGES ARE
**W2:IQ2** payroll series number from max to min. (229 to 1)
**W11:IQ11** (values 0+ from sumproduct formula)down to row 300.
**J2:T2 (new ref series of values from 11 to 1)
**J11:T11 (the formula to reside here to be filled 'til row 300.

farther explanation.

a) look for the number different values within the range W11:IQ11 (values
are the milestone changes made on salary rates) meaning the rates shown are
only those that are different.
b) Once the values appear in, let's say, <W11,AA11,IG11>, then, only 3
positive values appear within range W11:IQ11. The rest of the values are all
zero(0).
c) The range J5:T5, will contain formula to enumerate these 3 values (one
value per cell) and *include* the intersecting payroll series number from
W2:IQ2.
4) J2:T2 (11,10,9,8,7,6,5,4,3,2,1) contains the series number of <"n"th>
occurence (something like *small*).

after some configuration, i tried to merged the two formulas.
--------------
i.e. on J11 copy paste towards T11
=TEXT(IF(ISERROR(SMALL($W11:$IQ11,229-COUNTIF($W11:$IQ11,">0")+J$2)),"",SMALL($W11:$IQ11,229-COUNTIF($W11:$IQ11,">0")+J$2)),"[$â‚¡-140A]#,##0.00_);([$â‚¡-140A]#,##0.00)")&"
Start onPlan#
"&TEXT(INDEX($W$2:$IQ11,1,MATCH(IF(ISERROR(SMALL($W11:$IQ11,229-COUNTIF($W11:$IQ11,">0")+J$2)),"",SMALL($W11:$IQ11,229-COUNTIF($W11:$IQ11,">0")+J$2)),$W11:$IQ11,0)),"0")
--------------
so based on example e.g. on (b):
W11,AA11,IG11 has positive values (salary rates).
so the formulated results for range J11:T11 is ....
(#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,"*text for W11*","*text for
AA11*","*text for IG11*")

PROBLEM: I now have the correct 3 "text results" yet i cannot eliminate the
#N/A into something like "-".

The sense is to prepare the summary milestone sheet of salary rates
adjustments (J:T) been made or *not had not yet been made*.

thanks a lot
regards,
driller
 
Hi

I fully appreciate that English may not be your first language, but I am
having difficulty in understanding exactly what you are trying to
achieve.

If you want to mail me direct with a copy of your workbook, then I will
be pleased to take a look and see if I can understand, and assist you.
If you want to mail direct, then remove NOSPAM from my email address.
 
Back
Top