Greetings, I am trying to find a way to identify the first occurrence of an entry in a column
Answers
-
The MATCH function can be used to find the first exact match in a range if the "0" argument is used. The exact usage depends on what you wish to return.
-
Hi Carson,
Doesn't the MATCH function require that I know what I am looking for? Whereas, I just want to know which row the Sales Order & Line number appears in first. I do not know what specific number I am looking for.
Brent
-
Are you saying that you may have any given number of blank cells above the first entry, and you need to determine which row the first entry is in? If so, this may give you the results you are looking for.
=MATCH("*", [Blank]:[Blank]) + 1
-
The website doesn't want to let me edit my last post currently.
=MATCH("*", [Sales Order & Line]:[Sales Order & Line]) + 1
^^Fixed the references I was using for testing.
-
That doesn't seem to do what I want. It is giving me a result of "No Match".. even when I use it on a row that contains the first occurrence of the number.
I am having partial success with: =IF(COUNTIF([Sales Order]$1:[Sales Order]1, [Sales Order]@row) = 0, 1, 0)
Unfortunately, I cannot turn this into a column formula. What this formula is doing, is looking at all the rows above and telling me if this particular occurrence has appeared already.
If I can get my current formula to auto populate for new rows being added, it would solve my issue.
Thanks again for your efforts.
Brent
-
I must still be unclear on what you are trying to do. When you say you are attempting to determine the first appearance of a number in a column, are you looking for a specific number, or any number, and if so, what is the source of the number you are looking for?
-
It could be any number. The numbers are imported from another database on a weekly basis. The report breaks up the entries by another variable, but I want to combine the entries into their common Sales Order & Line number. If I can identify when the first occurrence of the Sales Order & Line number appears, I can copy that row to another sheet, then JOIN the other variable into one cell for project management purposes.
So, I am looking for a way to identify when a unique number shows up for the first time.
The formula I came up with is working, but will not allow me to make it a column formula, only cell formula. Which is fine, but when new entries are added, I need to manually extend the formula to the new entries.
I am hoping to find a way to create a column formula that looks at all current and future entries, selects the first time a number is added.
There might not be a way to do this at this time.
-
So you are essentially looking for a way to identify the row of the first occurrence of any entry in the column?
-
Yes.
-
The only real way I can see doing this would be to add an auto number helper column. You could then do a countif and then determine the lowest number in the autonumber column for any results greater than 1.
Smartsheet does not permit using references to specific cells in a column formula. This is the cause of the issue you mention above in creating a column formula.
Help Article Resources
Categories
Check out the Formula Handbook template!