Is this possible?

[Guest]

Microsoft Office Excel 2003

I have two rows of data. The first are numbers 1 through 100, the second are
how many occurrences of the number from the first row in another area of the
spreadsheet. This part works just fine using the countif function. It looks
like this:

1 2 3 4 5 6 7 8 9 10 ...
15 3 29 14 12 28 54 11 2 82 ...

Is there a way I can look at the second row, determine how many cells have a
value greater than 50 and then take the corresponding numbers from the first
row and copy those to different cells elsewhere in the spreadsheet? So in the
above example, it would copy the number 7 and the number 10 to different
cells elsewhere.

Thanx.

 

[Guest]

It is certainly possible if you are willing to take one additional step. If
you transpose the data from rows into columns, then you can use Data > FIlter

Advanced Filter...to reduce the data to match you criteria. Once reduced,

the data can be copied elsewhere.

 

[Bernie Deitrick]

Steven,

One way (probably not the best, but....), assuming that your data starts in cell A1 (your first row
is in row 1, with the number 1 in cell A1):

Use this helper formula in A3:

=RANK(A2,2:2)+COUNTIF($A2:A2,A2)-1

And copy cell A3 across row 3 to match the cells in row 2.

Then in cell A5 enter a 1, and increment the value down column A (so you get 1, 2 in A6, 3 in A7,
etc.....)

Then in B5, enter the formula
=IF(INDEX($2:$2,MATCH(A5,$3:$3,FALSE))>50,INDEX($1:$1,MATCH(A5,$3:$3,FALSE)),"")

And in C5, you could also enter the formula:
=IF(INDEX($2:$2,MATCH(A5,$3:$3,FALSE))>50,INDEX($2:$2,MATCH(A5,$3:$3,FALSE)),"")

Then copy B5:C5 down to match your numbers in column A. If you don't get a blank returned from
either formula, you need to extend your list further.

HTH,
Bernie
MS Excel MVP