New client vs. continued client

@Jennifer Lenander

If you do not already have an Auto# column then add one called LINE-ID.

The create a checkbox column and add the below formula. This will make the checkbox checked if it is a new client.

=IF(MATCH(client@row, client:client, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0),1,0)

Make sure to replace the column names above to match what you have on your sheet...

You can then run count functions on this column based on 1 (new client) and 0 (existing client).

By running a match on the LINE ID i get the current row #.

We also run a match on the client. If the match returns the same # that means that the first time this client shows up on the sheet is on the current row.

If they do not match that means the first time it shows up is on a previous row and they are an existing client...

@Jennifer Lenander What matters is that you have something working, but here is a quick rundown of my formula...

We start with the COUNTIFS. Using Client$1 means the range starts in row 1. Then we use the @row reference on the other side of the range to count down to the current row. It basically says to count how many times from row 1 down to the current row does that client name exist.

Then we use the IF statement to say that if that count is 1 (meaning it is the first time that particular text is found from the top of the sheet down to the current row), we output a 1, otherwise 0. So you will have a 1 in the first occurrence and a 0 in all of the others below it.

The difference with mine is that using the absolute reference to lock in row 1 for the start of the range means that it cannot be used as a column formula which means dragfilling, but it allows for a single column instead of two columns.

It is simply a matter of personal preference. I have done so much work in Smartsheet before column formulas were a thing, so my thought/solution process hasn't quite pulled that in yet. Haha

All comments

@Jennifer Lenander

If you do not already have an Auto# column then add one called LINE-ID.

The create a checkbox column and add the below formula. This will make the checkbox checked if it is a new client.

=IF(MATCH(client@row, client:client, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0),1,0)

Make sure to replace the column names above to match what you have on your sheet...

You can then run count functions on this column based on 1 (new client) and 0 (existing client).

@Leibel S - this worked! This is awesome. Can I ask, how it works? Why is the LINE ID/Auto Number needed? Thank you so much for your help. I have been trying to figure this out for longer than I would like to admit.

By running a match on the LINE ID i get the current row #.

We also run a match on the client. If the match returns the same # that means that the first time this client shows up on the sheet is on the current row.

If they do not match that means the first time it shows up is on a previous row and they are an existing client...

I get it! Thank you. That is pretty awesome. You are in a close race with @Paul Newcome for being my hero. :)

@Jennifer Lenander Haha. As long as you are getting the answers you need.

If you are not using those new fangled column formulas (@Leibel S 😜), you can actually do this in a single column using a very similar logic.

=IF(COUNTIFS(Client$1:Client@row, Client@row) = 1, 1, 0)

I guess I should quit being behind the times and get the hang of those things at some point. Haha

@Paul Newcome

On most my sheets I end us using the LINE ID / ROW# for so many things that the work becomes worthwhile...

@Leibel S and @Paul Newcome - thank you both. I greatly appreciate both of you.

FWIW, I am able to follow Leibel's formula a little better than Paul's. Thanks again! :)

@Jennifer Lenander What matters is that you have something working, but here is a quick rundown of my formula...

It is simply a matter of personal preference. I have done so much work in Smartsheet before column formulas were a thing, so my thought/solution process hasn't quite pulled that in yet. Haha

@Leibel S I put the row number column on every single one of my sheets right off just in case I end up needing it. I have done it without the column references so many times, that I don't even think about it. Haha

https://community.smartsheet.com/discussion/comment/271020#Comment_271020

@Paul Newcome - this is so incredibly helpful. I really appreciate the education. I can copy/paste formulas all day but I really appreciate learning how they work so I can hopefully create them myself. Thank you for taking the time to explain, I am very grateful.