In column M how can I apply a 1 to the top 82 ranked values as defined in column L

In column M how can I apply a 1 to the top 82 ranked values as defined in column L
Example_LaborDistribution.xlsx
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro()

For Idx = 1 To 82
Set Rng = Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
    For Each c In Rng
        If Idx1 < Idx And c.Offset(, 1) = "" And c = WorksheetFunction.Large(Rng, Idx) Then
           c.Offset(, 1) = 1
           Idx1 = Idx1 + 1
        End If
    Next
Next
End Sub

Open in new window

Regards
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this...
In M2
=IF(L2>=LARGE($L$2:$L$503,82),1,"")

Open in new window

And then copy it down.
0
 
Rgonzo1971Commented:
@Neeraj The problem with the formula you get 90 values (8 more)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rgonzo1971Commented:
then try
=IF(AND(SUM($M$2:M62)<=83,L62>=LARGE($L$2:$L$503,82)),1,"")

Open in new window

Regards
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rgonzo
Well in that case, the following formula will produce the output as same as produced by the code suggested by you.
In M2
=IF(ROWS(M$2:M2)<=82,1,"")

Open in new window

0
 
Rgonzo1971Commented:
@ neeraj in this example yes but the data is not sorted

then try corrected formula in M2
=IF(AND(SUM($M$2:M2)<=82,L2>=LARGE($L$2:$L$503,82)),1,"")

Open in new window

and fill down

Regards
0
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thanks that worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.