In column A, I have projects and in column B I want to have a serial number for the project which increases when the project is repeated and starts from 1 for a new project. It’s simple to achieve with a formula that can be dragged or with scripts. Any way to achieve it with arrayformula?
Hi Manan,
You can try =ARRAYFORMULA(IF(A2:A<>"",COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),""))
Explanation:
- A2:A<>"" : To only number the rows that have data in A
- COUNTIFS: Only consider rows that have the same project name (A2:A, A2:A) and apply a dynamic range containing only from the start of the column to that specific row (ROW(A2:A), <=&ROW(A2:A)
Please try and get back to me if you need more help.
6 Likes
This is great. The dynamic range is going to help in many ways. Never knew about it. Thanks!
1 Like
You could certainly achieve this in the data editor using Row ID, Relations, and a Rollup column. You will want to to separate the Project and Serial Number (we’ll call this “Project/SN” Sheet) list to a different sheet from the individual project data(we’ll call this “Project Data”. Then in the “Project/SN” sheet add Row IDs in the data editor. Then you will want to ensure that the Row ID of this project is recorded to the “Project Data” sheet. Depending on how you do this in the app, it will vary in difficulty. Preformatted textYou will also want Row IDs on in the “Project Data” sheet. This will allow you to keep them separate even if they share essentially the exact same info.
You then need to create a relation from the “Project/SN” sheet Row Id to the “Project Data” sheet. Then do a “Count” Rollup of that relation and you will have your dynamically based serial number. you can then use this ID to as a column added to each new project as they are created.
I hope that makes sense! Let mew know if you need anhy other help setting that up! I would be happy to help.
4 Likes
I didn’t completely understand. I created 2 duplicate sheets and both have different row IDs in them. Now which columns are to be related to each other?
While I already used the solution provided by @ThinhDinh, I would love to learn a method using the data editor.
Well, first, how are you or users adding the individual projects that you are adding a S/N to?
I am using a form button from the user profile tab
Are the project types predefined using a Choice Component? So those will always be the same in the second sheet?
Yes the types are predefined
@ThinhDinh Hello ThinhDinh. I am facing a similar problem. My data is in Glide tables. Can you please help me with how do I do this through glide?
1 Like
Maybe there’s some way to do that using the Experimental Code column with the input being an array of “categories” you want to add the serial number too, but I won’t have time to code it. Sorry.