Hi guys,
I would really appreciate if you could please help me solve a couple of things that drive me crazy.
1. I am trying to return the top 5 values from a list using this formula:
=INDEX($B$4:$B$12,MATCH(LARGE($B$4:$B$12,ROWS($F$4 :F4)),$B$4:$B$12,FALSE))
A4:A12 has A,b,c,d,e,f,g,h,i and B4:B12 has the values 10,15,15,7,5,20,18,18,3
Apologies, I tried to upload a file, but couldn't find the link to do it.
The formulas work, but if the two entries have the same value, my formula can't differentiate btw the two, and will list the same entry twice (15,18 repeat in col B)
Is there any way I can tweak the formula to give me the next name associated with the duplicate value?
2. I am trying to select a range whose end is variable. The end of the range is dependent on a formula that tries to find unique values. The fomula is =SUMPRODUCT(1/countif(rng, rng)
I am a newbie to VBA and I don't know how to insert the end of the range to display the result of the SUMPRODUCT formula.
My code is:
Code:
Sub dstc()
Sheets("Sheet1").Select
Sheets("Sheet1").Range("H4:H" & Sheets("Sheet1").Range("H1").Value).Select
Selection.FormulaArray = "=distinctvalues(rng_A,TRUE)"
End Sub
So far I've tricked it by pointing to a cell (H1) that displays the result of the sumproduct, but somebody may delete it someday. My dream is to have something such as:
Code:
Sheets("Sheet1").Range("H4:H" & formula ).Value).Select
I'm attaching the file and I am really thankful for any help.
Distinct Values and variable dynamic range
