Sorting problem

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Dear all,

I have a list of integers from A1 to A10. If I would like to store the
absolute address of the third cell counted from the top in column A
containing "1" in B1, what formula should I use?

For example, if the data from A1 to A10 are 3, 1, 1, 2, 1, 2, 2, 2, 1,
3, I want B1 stores "$A$5" as A5 is the third cell containing "1". Thanks in
advance.

Best Regards,
Chris
 
Chris,

One way

Add a column B with the cell names i.e a1,a2,a3,a4,a5,etc
Enter in C1 ...........=B3
do a sort on A column after selecting a1:b10

Use the undo button, enter new data and sort again.............
You could create a macro to do the sort of course.

Sub Macro2()
Range("A1:B10").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Regards
Bill K
 
="$A$"&SMALL(IF((COLUMN($A$1:$J$1)*($A$1:$J$1=1))>0,COLUMN($A$1:$J$1)*($A$1:$J$1=1)),3)

array entered using CTRL+SHIFT+ENTER
 
Incredible formula Ken.

Learn every day

Of course it should have been in rows....
="$A$"&SMALL(IF((ROW($A$1:$A$10)*($A$1:$A$10=1))>0,ROW($A$1:$A$10)*($A$1:$A$10=1)),3)

Thanks
Bill K
 
Back
Top