Using Vlookup Or Match Function with the duplicated data will pick only the first match and it wont pick the second or third occurrence of the data as expected. In your case your data is having 4 zero values in Total and your data needs to be get sorted by the Total Value. So we are setting the Total Value as base value and trying to pull the remaining data. But due to duplication (0) in Total the vlookup and Match functions will leads to result the first match instead of the next subsequent data. So we need to tell Vlookup or Match function that each data are different by way of adding some other Number with the Total Value which should not be common. So I am using the Row() Function for differentiating each data.
=Row() Using the above function in any of the cell will get the current row number of that cell. For example if you use the =Row() in cell C5 then it will result 5 as result. The Row() function will get the Row Number and I just merged the Row Number and Total using the & symbol. =ROW()&O2 Will merge both the Number by Treating As Text Data, since & comes under Text Data Type, so it makes both the numbers [Row() and Total] as text data. But we cannot use the Large or Small function with the Number which is considered as text. So we need to convert the Text Number to real number for calculation purpose. =--(ROW()&O2) Value() - Length 7 characters -- (Unary Operator) - Length 2 characters Using Value function or using double hyphen (--) Unary operator in front of the text Number will convert it as Real Number. But the same will result #VALUE! Error when it is referred to Text Characters other than Text Number. Mostly I prefer to use Double Hyphen (--) (Length 2 chrs) instead of Value() Function (Length 7 chrs), because it will be useful for us when we are building a lengthy formula. Hope that helps! <><><><><><> *Ms.Exl.Learner* <><><><><><> On Fri, Aug 24, 2012 at 2:05 PM, Hilary Lomotey <resp...@gmail.com> wrote: > Nice its working,this is a beautiful formula pal , i am learning new stuff > everyday , but tell me what is the essence of =--(ROW()&O2) in the formula? > > thanks > > > On Fri, Aug 24, 2012 at 4:47 AM, Ms-Exl-Learner . < > ms.exl.lear...@gmail.com> wrote: > >> Refer the attached excel for modified solution. >> >> Let us know in case of any further assistance. >> >> ----------------------- >> Ms.Exl.Learner >> ----------------------- >> >> >> >> >> On Thu, Aug 23, 2012 at 8:26 PM, Hilary Lomotey <resp...@gmail.com>wrote: >> >>> Hello Bosses, >>> >>> PFA, >>> >>> i am trying to sort my data(in sheet1) by the largest figures(in sheet >>> 2), i am getting a num error in the formular and i also realised that when >>> two figures have same figure it repeats the first instance all though i >>> thought the below formula will resolve that. kindly assist. thanks >>> >>> >>> INDEX(Sheet1!A$2:A$21,SMALL(IF(Sheet1!$N$2:$N$21=P20,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1,""),COUNTIF(Sheet1!$N$2:N19,Sheet1!N19))) >>> >>> -- >>> Join official facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES (1120+ members already BANNED for violation) >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> >>> 2) Don't post a question in the thread of another member. >>> >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> >>> 4) Acknowledge the responses you receive, good or bad. >>> >>> 5) Cross-promotion of, or links to, forums competitive to this forum in >>> signatures are prohibited. >>> >>> 6) Jobs posting is not allowed. >>> >>> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. >>> >>> NOTE : Don't ever post personal or confidential data in a workbook. >>> Forum owners and members are not responsible for any loss. >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "MS EXCEL AND VBA MACROS" group. >>> To post to this group, send email to excel-macros@googlegroups.com. >>> To unsubscribe from this group, send email to >>> excel-macros+unsubscr...@googlegroups.com. >>> >>> >>> >> -- >> Join official facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES (1120+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> 6) Jobs posting is not allowed. >> >> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To post to this group, send email to excel-macros@googlegroups.com. >> To unsubscribe from this group, send email to >> excel-macros+unsubscr...@googlegroups.com. >> >> >> > > -- > Join official facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES (1120+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > 6) Jobs posting is not allowed. > > 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To post to this group, send email to excel-macros@googlegroups.com. > To unsubscribe from this group, send email to > excel-macros+unsubscr...@googlegroups.com. > > > -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
