FUNCTION or MACRO counting the number of lines
Solved
Elariana
-
eriiic Posted messages 24581 Registration date Status Contributeur Last intervention -
eriiic Posted messages 24581 Registration date Status Contributeur Last intervention -
Hello,
I have a problem with my table. I've been searching since this morning on different forums, but I can't find the answer to my issue. I still don't know if I should use a macro or if a simple function would suffice.
Let me explain my problem.
I would like to count the number of rows in a table that meet the criteria of another column. It seems simple at first glance, but since I'm asking you, it has gotten complicated, so I'll try to illustrate the problem:
I have several columns
A (identifier) - ... - AH (date)
I would like to count the number of rows in A before a certain date entered in column AH. The complication arises because the table has several merged rows (for example, rows 9 and 10 correspond to the same identifier). I also need to split the cells corresponding to the date in AH to make SUMPRODUCT functions. Now the problem is to avoid duplicates.
In short, it's a bit of a complicated issue, so I can guide you as it's not easy to explain.
Please don't hesitate to help me and ask me questions.
Best regards
Elariana
P.S: I work on OpenOffice Calc, but if I can find the solution in Excel, that’s fine by me!
Configuration: Windows XP / Firefox 3.6.8
I have a problem with my table. I've been searching since this morning on different forums, but I can't find the answer to my issue. I still don't know if I should use a macro or if a simple function would suffice.
Let me explain my problem.
I would like to count the number of rows in a table that meet the criteria of another column. It seems simple at first glance, but since I'm asking you, it has gotten complicated, so I'll try to illustrate the problem:
I have several columns
A (identifier) - ... - AH (date)
I would like to count the number of rows in A before a certain date entered in column AH. The complication arises because the table has several merged rows (for example, rows 9 and 10 correspond to the same identifier). I also need to split the cells corresponding to the date in AH to make SUMPRODUCT functions. Now the problem is to avoid duplicates.
In short, it's a bit of a complicated issue, so I can guide you as it's not easy to explain.
Please don't hesitate to help me and ask me questions.
Best regards
Elariana
P.S: I work on OpenOffice Calc, but if I can find the solution in Excel, that’s fine by me!
Configuration: Windows XP / Firefox 3.6.8
13 réponses
Hello,
It's a bit confusing all of this.
If my interpretation is correct, we need to count the rows that meet two conditions:
the date in column AH is earlier than a fixed date (in $AG$3 for example)
and the corresponding cell in column A is not empty.
In this case, we can apply =SUMPRODUCT((AH2:AH100<$AG$3)*(A2:A100<>""))
Be careful with merged cells; the cell that contains a value is not always the one you think it is, depending on the alignment chosen.
It's a bit confusing all of this.
If my interpretation is correct, we need to count the rows that meet two conditions:
the date in column AH is earlier than a fixed date (in $AG$3 for example)
and the corresponding cell in column A is not empty.
In this case, we can apply =SUMPRODUCT((AH2:AH100<$AG$3)*(A2:A100<>""))
Be careful with merged cells; the cell that contains a value is not always the one you think it is, depending on the alignment chosen.
This partially answers my question, as it does count the number of lines for me; the problem lies with the split cells. Let me explain:
For an identifier in column A (identifier), I can have two merged rows but split in AH for the date field, which is therefore duplicated. This proposed formula does not take my duplicates into account.
For an identifier in column A (identifier), I can have two merged rows but split in AH for the date field, which is therefore duplicated. This proposed formula does not take my duplicates into account.
Hello everyone,
For an identifier in column A (identifier), I can have two merged rows but split in AH for the date field which is thus duplicated. This proposed formula therefore does not take my duplicates into account.
So this means that only the presence of a date interests you....
=COUNTIF(AH2:AH100,"<" & $AG$3)
eric
For an identifier in column A (identifier), I can have two merged rows but split in AH for the date field which is thus duplicated. This proposed formula therefore does not take my duplicates into account.
So this means that only the presence of a date interests you....
=COUNTIF(AH2:AH100,"<" & $AG$3)
eric
No, precisely the presence of the date is interesting only as a criterion, because with this proposed function, I count the lines in column AH (date field) and it counts the duplicates.
In fact, my table is structured as follows:
In column A, I have my identifiers. Most of the time I have an identifier for a single line, but some lines are merged, so I end up with an identifier for two lines. However, the line is not fully merged, especially regarding column AH (date field) where the lines have been split to allow for other calculations.
Are you still with me?
In fact, my table is structured as follows:
In column A, I have my identifiers. Most of the time I have an identifier for a single line, but some lines are merged, so I end up with an identifier for two lines. However, the line is not fully merged, especially regarding column AH (date field) where the lines have been split to allow for other calculations.
Are you still with me?
Are you still following me?
no
1st incorrect response because This proposed formula therefore does not take my duplicates into account.
2nd incorrect response because so it includes the duplicates.
Upload an example file (describing the desired results on it) on cijoint.fret and paste the provided link here.
no
1st incorrect response because This proposed formula therefore does not take my duplicates into account.
2nd incorrect response because so it includes the duplicates.
Upload an example file (describing the desired results on it) on cijoint.fret and paste the provided link here.
Thank you for your help.
So I simplified the table to summarize that column AH is now in J and the identifier I'm interested in is in A, B, or C, it doesn't change anything. I made a general table in the bottom right to understand the different calculations I'm undertaking. The red line corresponds to the result I would like to achieve with a formula or macro.
OpenOffice Calc link: http://www.cijoint.fr/cjlink.php?file=cj201008/cijTx0yIWL.ods
Excel link: http://www.cijoint.fr/cjlink.php?file=cj201008/cij5yAA15M.xls
So I simplified the table to summarize that column AH is now in J and the identifier I'm interested in is in A, B, or C, it doesn't change anything. I made a general table in the bottom right to understand the different calculations I'm undertaking. The red line corresponds to the result I would like to achieve with a formula or macro.
OpenOffice Calc link: http://www.cijoint.fr/cjlink.php?file=cj201008/cijTx0yIWL.ods
Excel link: http://www.cijoint.fr/cjlink.php?file=cj201008/cij5yAA15M.xls
No, they must be 2, 3, and 4.
In fact, lines 7 - 8 and 9 - 10 represent two conventions for 4 dates. The result 3, 5, 6 does not take the duplicates into account.
I hope I am clear.
Thank you for your help.
In fact, lines 7 - 8 and 9 - 10 represent two conventions for 4 dates. The result 3, 5, 6 does not take the duplicates into account.
I hope I am clear.
Thank you for your help.
Counting the codes in column C: enter in M14
=SUMPRODUCT(($J$6:$J$100<="30/06/2009"*1)*($C$6:$C$100<>""))
Suggestion for M15:
=SUMPRODUCT(($I$6:$I$11="yes")*($J$6:$J$11<="30/06/09"*1)*($F$6:$F$11))
It is possible to improve the summary table: in M13 enter 30/06/2009 and set the cell to the custom format mmm yyyy to display June 2009. The same for N13, O13, etc.
In the formulas replace "30/06/2009"*1 with M$13.
The formulas are thus "copyable" horizontally.
=SUMPRODUCT(($J$6:$J$100<="30/06/2009"*1)*($C$6:$C$100<>""))
Suggestion for M15:
=SUMPRODUCT(($I$6:$I$11="yes")*($J$6:$J$11<="30/06/09"*1)*($F$6:$F$11))
It is possible to improve the summary table: in M13 enter 30/06/2009 and set the cell to the custom format mmm yyyy to display June 2009. The same for N13, O13, etc.
In the formulas replace "30/06/2009"*1 with M$13.
The formulas are thus "copyable" horizontally.
Great, the formula works perfectly. Thank you very much.
Now, to appear less clueless, could you give me an explanation of this formula? Particularly about the second part: *($C$6:$C$100<>""))
Regarding your suggestion for M15, it seems to me that this is the formula I put in the cell, isn't it?
Thanks for your help, buddy
EDIT: In the end, I'm having trouble getting it to work in my table, I'll try again and keep you updated
Now, to appear less clueless, could you give me an explanation of this formula? Particularly about the second part: *($C$6:$C$100<>""))
Regarding your suggestion for M15, it seems to me that this is the formula I put in the cell, isn't it?
Thanks for your help, buddy
EDIT: In the end, I'm having trouble getting it to work in my table, I'll try again and keep you updated
Sure, thank you very much for your help, the formula works perfectly this time.
As for the structure of the table, I actually inherited this table, and I am therefore supposed to create intermediate tables to produce graphics. In the future, I am considering changing the structure or even better integrating it into a database.
Thanks for everything.
As for the structure of the table, I actually inherited this table, and I am therefore supposed to create intermediate tables to produce graphics. In the future, I am considering changing the structure or even better integrating it into a database.
Thanks for everything.
Hello everyone
a simple question !!
I would like to, in my column A, starting from the last row (65536) go up to the first row above that is filled (for example row 1300) and put in my macro a variable: for i = 2 to 1300
the final goal, starting from this example, is to filter different values in column A, from row 2 to 1300 (here). The number of rows may vary...
then, do I need to include a new variable to create an auto filter, depending on the value of my cell??
thank you
a simple question !!
I would like to, in my column A, starting from the last row (65536) go up to the first row above that is filled (for example row 1300) and put in my macro a variable: for i = 2 to 1300
the final goal, starting from this example, is to filter different values in column A, from row 2 to 1300 (here). The number of rows may vary...
then, do I need to include a new variable to create an auto filter, depending on the value of my cell??
thank you
Thank you Eric
I have one last question, I would like it to copy the info from each row (therefore i=i+1) but I tried Rows, range or cells, but it doesn't work. The syntax must not be correct!!
Range(i, 26).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
It's to copy the info from each row into a new file
Thank you
I have one last question, I would like it to copy the info from each row (therefore i=i+1) but I tried Rows, range or cells, but it doesn't work. The syntax must not be correct!!
Range(i, 26).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
It's to copy the info from each row into a new file
Thank you
Re,
[A1:A1300].AutoFilter Field:=1, Criteria1:=ActiveCell
But you don't have to activate a cell to use it; it's actually a waste of time in the macro.
For example, if your criterion is in E1 (row 1 column 5):
[A1:X1300].AutoFilter Field:=1, Criteria1:=Cells(1,5)
eric
edit: you changed your question....
I'll look at that after lunch.
[A1:A1300].AutoFilter Field:=1, Criteria1:=ActiveCell
But you don't have to activate a cell to use it; it's actually a waste of time in the macro.
For example, if your criterion is in E1 (row 1 column 5):
[A1:X1300].AutoFilter Field:=1, Criteria1:=Cells(1,5)
eric
edit: you changed your question....
I'll look at that after lunch.