Formula answer plus one/two cells down

Question

Level 1

9 points

Formula answer plus one/two cells down

I am using a SMALL formula and then a LOOKUP formula to automatically sort a table because that is the only way I have found how to do it. The problem is when looking up the same value I only get the first one returned.

Is there a way to add have the formula and then add something like +1 cell down?

I dont know how to put the formulas in but the table below demonstrates my problem. i want something like IF header 5 is the same, do the same formula, but then go one cell down so it returns green instead of another red.

If adding one cell down isn't an option, are there other formulas that I could add in to get the result i want?

Thanks

Header 1	Header 2	Header 3	Header 4
autosort table
red	1	red	1
green	1	red	1
blue	2

MacBook Air, iOS 11.2.6

Posted on Mar 4, 2018 8:54 AM

Reply

Answer 1

Badunit

Level 7

25,796 points

Mar 4, 2018 6:12 PM in response to nels195

The problem is the numbers you are sorting are not all unique. LOOKUP and MATCH and other functions cannot tell one from the other and will always return the first match they find. One trick to make them unique is to somehow incorporate the row numbers into the numbers to be sorted and to do so in a way that does not affect the sort. In the example below, I created a new column C and added ROW()/10000 to the numbers from column B. Now they are unique and can be sorted.

The formula in column E is =INDEX(A,MATCH(SMALL(C,ROW()−1),C,0))&""

In this case, the "ROW()-1" in the formula is just a quick way to get a sequence of numbers 1,2,3,4, etc when you fill-down with the formula so you look for the smallest, second smallest, etc as the row numbers increase.

The &"" at the end is because blank cells in column A will get brought over as 0's unless Numbers thinks they are strings. Appending the null string "" to the end makes them strings so they appear empty, as they should be.

The formula in column F is similar to E.

Your example in your first post had number values to be sorted but you mentioned "time" in your more recent post. If your data is actually Date & Time values (e.g., March 4, 2018 9:03PM), you may have to find a way to convert them to numeric values and then do basically the same thing as above. With a little work they can be converted to "seconds since 1/1/1990" or something like that and you can add a fractional part to each number based on the row number.

Reply

Answer 2

nels195 Author

Level 1

9 points

Mar 4, 2018 12:49 PM in response to SGIII

Every time I type in a new number on another table, the totals change, and I want the name of the people next to their total time to move with the time every time new data is entered.

Right now, I can create a table that "automatically" sorts by using the SMALL function, but am having trouble pulling up the names associated with that time, when two times are the same. I am always getting one set of names for that time instead of the next set if names that also has the same time.

Reply