Greetings, I am trying to find a way to identify the first occurrence of an entry in a column

BGATES

Carson Penticuff

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.

BGATES

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

Carson Penticuff

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

Carson Penticuff

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.

BGATES

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

Carson Penticuff

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?

BGATES

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.

Carson Penticuff

So you are essentially looking for a way to identify the row of the first occurrence of any entry in the column?

BGATES

Yes.

Carson Penticuff

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.

DylBlake

I've struggled with this problem before and found a solution today. I wanted to be able to label the first occurrence without using any of the [Column A]$1:{Column A]@row syntax because Smartsheet doesn't allow that to be turned into a column formula. Also, I don't like Smartsheet's Auto Number columns because my solutions often have rows moved around, deleted, etc. which makes these columns not reliable as an index or row counter. However, you can follow the steps below to solve both of these issues and reliably get back whether a value in a given column is the first occurrence or not.

I typically don't post but think this one is worth a post. There's a ton of applications of this. Fig 1 is my sample sheet. Fig 2-5 show the formulas. Steps below

1. Set up an "Auto Number" column.
2. Set up a dynamic row count using the match function (Fig 2)
3. Set up another match function on the column you are looking to identify the first occurrence of a value in (Fig 3)
4. If these two match columns are equal, you are looking at the first occurrence (Fig 4)
   1. Fig 5 shows the occurrences of "Groceries" and "Fuel" in adjacent cells in the column.
   2. Fig 6 shows where I move the first occurrence of "Fueld to row 4.

Fig 1

Fig 2

Fig 3

Fig 4

Fig 5

Fig 6