 SOLVED

# Find the first, second, third, and so values in an array.

I have an array that is 70 rows and 30 columns wide. Looking up the first value larger than zero in a certain column in the array is simple. How do I find the second, the third, the fourth, and so on until there are no more found? When I know all those rows that exceed zero in a cell I will use that information to make a new array of only those found.

3 Replies
best response confirmed by FifthSteve (New Contributor)
Solution

# Re: Find the first, second, third, and so values in an array.

``=SORT(FILTER(column_range,column_range>0))``

# Re: Find the first, second, third, and so values in an array.

@Detlef Lewin Thank you. I doubt if I would have found this simple elegant solution. I was working so hard on this solution I didn't think of the next step. I am very poor at chess.

The spreadsheet I am making has the user input three numbers in three columns separated by 6 columns left blank. Every 7 columns a formula uses the input and continues to the end of the columns. So, out of each series of 7 columns, I only need to display one. Is there a way to eliminate those 6 and keep the one I want?

# Re: Find the first, second, third, and so values in an array.

*

@FifthSteve wrote:

The spreadsheet I am making has the user input three numbers in three columns separated by 6 columns left blank. Every 7 columns a formula uses the input and continues to the end of the columns. So, out of each series of 7 columns, I only need to display one. Is there a way to eliminate those 6 and keep the one I want?