Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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 -- ***** birds of the same feather flock together.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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 "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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(E 1)),"") 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. -- Regards Roger Govier "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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" wrote in message ... 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(E 1)),"") 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. -- Regards Roger Govier "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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" wrote in message ... 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" wrote in message ... 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(E 1)),"") 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. -- Regards Roger Govier "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
Guys,
I think this thread is related to this one: http://tinyurl.com/yv4p8t And near the bottom of this one I seek guidance from the Master! http://tinyurl.com/yvuhjx This OP has a history of not being very explicit with the details! Biff "Roger Govier" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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(E 1)),"") 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. -- Regards Roger Govier "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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 -- ***** birds of the same feather flock together.. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
sorry typos
*** c) The range J11:T11, will contain............. -- ***** birds of the same feather flock together.. "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summary of milestone
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. -- Regards Roger Govier "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sample of a milestone tracker in excel | Excel Discussion (Misc queries) | |||
Align Line Charts By Milestone Dates? | Charts and Charting in Excel | |||
How can I set up a milestone report with red/green/amber alerts | Excel Worksheet Functions | |||
template for comparing projected vs actual milestone completion? | Charts and Charting in Excel | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) |