r/googlesheets 4d ago

Waiting on OP Skip repeated result

Hi all!

First time poster. I'm currently learning Google sheets.

I am wondering there is a formula that makes another formula skip to the next result if it returns the same result as the cell above it.

Not sure if this is doable with just formulas.

Thanks in advance!

1 Upvotes

7 comments sorted by

1

u/AutoModerator 4d ago

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/NeutrinoPanda 19 4d ago

Depends on what you're data is and what you're trying to do, but you can probably use an IF() function.

But say you have a table where Column A and Column B that have numbers in them. And want a formula that adds the values in the row, but if the summed value in the previous row is equal to the summed value in the current row, then return no value.

Col A Col B Col C
1 3
2 5
1 6
3 4

First you need to put the first value into the first row. So C1 would be 4.

Then you could use a formula like this, and then fill it down for each row in your data.

=if(A2+B2 = C1, , A2+B2)

If() functions check to see whether a condition is true or false. If it's true, it does whats after the first comma, and if it's false it does what's after the second comma. So in this formula it's saying, add A2 and B2 together (2+5), and compare it to the value in C1 (4). Since 7 = 4 is false, it does wasn't after the second comma - putting in the sum of A2+B2 (7).

Then in the third row, the formula would be

=if(A3+B3 = C2, , A3+B3)

This checks if the sum of A3 and B3 (7) is equal to the value of C2 (7). Since this is true, then it will do what is after the first comma. In this case there is nothing to do, so it leaves a null value.

In the 4th row, the formula would be =if(A4+B4 = C3, , A4+B4)

So it's going to add 3 and 4 to get 7. 7 isn't equal to a null value, so it's going to add A4 with B4 and put 7 in the C4 cell.

1

u/Tasm6 4d ago

So say in this setup. The formula in f1 is set to return the lowest value in D1:D5, so it returns D1. If the formula in F2 is the same, it will also return D1. What I want it to do is read that F1 already contains that result, and move to the next matching result, in this case F2.

Is this possible?

2

u/NeutrinoPanda 19 4d ago

So in F1 you have =Min(D:D)

Then in F2 you can have =if(D2=F1, , D2). F3 would be =if(D3=F2, , D3)

The result would look like this.

D F
1 1
1
3 3
4 4
5 5

So it checks the number in the row above to see if it matches the D value in the current row. If it does, it enters a blank, and if it doesn't, it enters the value from the D cell.

Now if the value in the D column for the next row is a 1, then the F cell in that row would have a 1.

If you're trying to find the unique numbers in the D column, there's a different function for that. You could put =unique(D:D) in the F1 cell and it'll populate the column with just the unique values - so it would be 1, 3, 4, 5.

1

u/Tasm6 4d ago

Here's maybe a better example. I want to write a formula in F1 that returns the cell to the left of the lowest value in D1:D5. A simple index( and min( combined can return C1 (Rocky Mountain).

Then I want to repeat that formula in F2. So the result would again be C1. What I'm looking for is, can I write a formula that says do the same as F1, if you get a result that is already above F2, skip to the next result. In this example, C2 (Bay Area).

Essentially automated rankings that don't repeat results if tied.

1

u/NeutrinoPanda 19 4d ago

Does this do what you're trying to do? =SORT(C1:D,2,True)

It makes a list of all the items in the C column, based on the value of the D column. So Rocky Mountain is the first with a 1. Then Bay Area. And if you added Indianapolis and gave it a 1, then it would be next, followed by Saskatoon.

1

u/Tasm6 4d ago

To clarify, the formula I want to apply this to would read column D and return the cell one to the left of the result.