@row referencing cell above
Is there a way that I can use @row to reference the cell above? I'm creating a formula to check for duplicates and I want to compare details in the current row to the one above.
Best Answer
-
A process I like to use to solve for these kind of issues is to create 2 columns:
- "LINE-ID" : Auto Number Column
- "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
This will allow you to run a check on a previous row value by using Index(column:column, [ROW#]@row -1)
Answers
-
Hi @Kirstine ,
I can't find a way to do it with @row without a reference to a specific row too. There are lots of posts in this community about how to find and flag duplicates.
Curious if someone else has a better answer.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
A process I like to use to solve for these kind of issues is to create 2 columns:
- "LINE-ID" : Auto Number Column
- "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
This will allow you to run a check on a previous row value by using Index(column:column, [ROW#]@row -1)
-
Thanks @Mark Cronk and @Leibel S for your input!
Leibel your clever trick has done the job! I don't really understand it yet (I've just plugged in the formulae with my column names) but I'll wrap my head around it soon! Thanks so much!
🤩
-
Thanks @Leibel S, that is a good solution that will work until we have a supported command.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Mark and Kirstine, this is just what I've been looking for, however, I keep getting an #UNPARSEABLE response. I have the "LINE-ID" AND "ROW#" columns working fine, however whenever I add an "=INDEX([Customer],[ROW#]@row -1)" either by itself or within a longer formula, I get #UNPARSEABLE .
Any ideas?
-
Try:
=INDEX([Customer]:[customer], match([ROW#]@row -1, row#:row#))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks Mark, I really appreciate the support. I still get an #UNPARSEABLE statement.
At one time I thought for sure I had your original solution working, so I'm wondering if something else is messed up in my sheet? I've attached it for reference.
See formulas in "Customer" column
-
Mark Crok, i like this formula, but i can not get it to work.
=IF([email protected] <> "", (IF(date4 = [email protected] - 1, Mds4 + [email protected], [email protected])), "")
i am trying to compare date in row 4 with row 5. it come with a False,
if i remove the "@row-1" and use "4" it work fine. =IF([email protected] <> "", (IF(date4 = [email protected], Mds4 + [email protected], [email protected])), "")
but i am trying to covert the column into a formula and it does not like the reference to a number.
any idea?
-
This is a pretty slick solution, but a word of warning if you're hoping to use this in sheets with relatively large numbers of rows:
This kind of full-range matching apparently can cause Out Of Memory errors. https://community.smartsheet.com/discussion/86274/error-when-running-out-of-cpu-memory
Having a function like Excel's "OFFSET()" might significantly reduce the processing requirements for this kind of functionality by not needing to scan hundreds/thousands of rows just to look one row above.
