Increment numbers in cells of column based on a filter
Rom27_52 Posted messages 30 Status Member -
EXCEL: how can I automatically fill, increment a number in cells of a column based on a filter on other cells of a column.
Thank you for your help
14 answers
-
Hello,
Coding for fun, I would like to understand what you want to do.
Assumptions:
A): You want each name to have its own incremented list.
B): You want to increment from a specific name (what happens to the previous names?).
What is the ultimate goal? (try to describe it without thinking in "Excel" logic).Show a spreadsheet as you would like it in the end.
-
Hello,
I have several names in my table, each assigned 3 codes (sp1, sp2, sp3).
Each person (name) has completed several sessions (sp1, sp2, sp3), e.g.
Person A: 3 sp1 sessions followed by 4 sp2 sessions, ...
People B, C, etc. are the same: Number of sessions to be recorded in the table.
When I apply the filter on person A, I would like to increment the number of sessions by copying them into the table column: this works with the COUNTIFS formula, but only if we start from the number 1 (i.e., session no. 1).
If person A (whom I select by applying the filter on person A among all the other people in the table) has already attended 6 sessions, and I want to increment the following sessions (7, 8, 9, 10, etc.) by copying them into the table column,
the incrementation does not work by copying; I assume that Excel also counts the values of the hidden cells? in the table.
I hope I am clearer regarding my question. :-)
Thank you very much for your help.
https://www.cjoint.com/c/NCehh4fDHqm
Here is the example attached.
-
-
Hello.
Please rephrase the question: I didn't understand anything here.
Retirement is great! Especially in the Antilles...
Raymond (INSA, AFPA) -
Hello Rom27_52
I am like Raymond (my regards), I don't understand.
Be careful with the use of words, to increment means "to increase (a variable) by a given increment."
Best regards
-
Attached is my Example table.
Thank you for your help.
https://www.cjoint.com/c/NCdlrpu5tzm
-
Here are macros that I found on CCM but which are not suitable for my example
Sub NumeroteLigneVisible() Dim i As Long, DerLig As Long, P As Long Sheets("Sheet2").Select DerLig = Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Number the H column (8) P = 1 For i = 1 To DerLig If CelR.Rows(i).Hidden = False Then Cells(i, 8) = P P = P + 1 End If Next i End Subor this macro Sub NumFacture() ' select the range to number before calling the macro Dim c As Range, num As Long num = CLng(InputBox("First invoice number?", "Automatic numbering of the selected range")) For Each c In Selection If c.EntireRow.Hidden = False Then c.Value = num num = num + 1 End If Next c End SubI found this VBA on CCM from a question asked in 2008.
Excel: Incrementing hidden cells
Resolved/Closed
-
-
Hello Rom27_52
I still don't understand what you're trying to achieve because you haven't provided any examples of the desired result in your file.
We should review this if you want a response through a formula.
As for VBA, I don't know anything about it and I really don't understand it, so I'll leave it to the specialists to respond to you.
Best regards
-
I created an Excel file where I filter certain rows, and I would like to be able to enter a number that increments only on the visible cells.
Is this possible? I can't find a solution, and I have to do the incrementing manually, which takes a long time.https://www.cjoint.com/c/NCehh4fDHqm
Thank you for your help
-
6 sessions sp1 (sp2)
Or 6 sessions in total (all sp combined)?
-
If you're on Windows, I can create a dedicated software for you (free, open source, and no installation required). I sent you a private message (red circle at the top right of the page).
-
You're with Mac, (your response in private message), too bad.
I'll do it anyway.
Be patient, some Excel expert will guide you.
I'm putting it here for 14 days
To delete, it's in the "données.txt" file
No spaces in names
Double click on: "sp.exe"
Input format: Name spX
It's here--->: https://www.mediafire.com/file/pks85abt50wlaa9/seances.zip/file
-
-
Hello,
Just in case. If it’s about incrementing the visible cells:
In F3:
=SUBTOTAL(103;Tableau2[@Nom])
Drag down. In E3:
=SUM($F$2:F2)+F3
Drag down as well.
Daniel
-
Thank you Daniel,
But this is not the solution I am looking for.
What I am looking for is to be able to increment - to pull down - according to the "Name" and "code" sort, but it is the rows hidden by the sort/filter that cause problems for this incremental copy.
https://www.cjoint.com/c/NCehh4fDHqm
-
Hello Rom27_52
Still the same file without additional explanations about what you're trying to do, you know what you want but you describe nothing.
Increment - copy what and how.
What is the relationship between the table on the left and the table on the right ??
In the absence of clear, concise, and precise indications, this will be my last intervention on this subject.
Best regards
-
It is evident that what Rom wants is an update of the states (incrementing with "forgetting" the previous states).
I am waiting for the feedback on the software that I gave him, which will confirm or refute my assertion.
-
The same with totals by sp.
Link 14 days (Windows only)--->: https://www.mediafire.com/file/bg70j2n1gkmkfy9/seaces.zip/file
-
Thank you very much, Mariam-j, unfortunately I currently do not have Windows.
The formula COUNTIFS(col.name; col.sp) works, but
the col.nb is always incremented starting from 1. I would like to copy the incrementing of nb starting from any number.
