View original (French)

FUNCTION or MACRO counting the number of lines

Solved
Elariana -  
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

13 answers

  1. tontong
    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.
    0
    1. Elariana
      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.
      0
      1. eriiic Contributor
        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
        0
        1. Elariana
          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?
          0
          1. eriiic Contributor
            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.
            0
          2. Elariana
            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
            0
        3. tontong
          Hello,
          Is it normal to find 2010 in A6 and 2009 in J6 (28/04/2009)?
          0
          1. Elariana
            Indeed, it is an error on my part; in A6 it should be 2009.

            But further down in the table (as this is an excerpt), we can find codes with 2010.
            0
            1. tontong
              Should the results in line 14 change to 3,5,6 instead of 2,3,4?
              0
          2. Elariana
            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.
            0
            1. tontong
              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.
              0
              1. Elariana
                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
                0
                1. tontong
                  C6:C100 not equal to ""
                  For M15, the syntax is different in the Excel file.
                  One last piece of advice: try to avoid the "merge and center" button, especially in sheets with a lot of calculations.
                  0
                2. Elariana
                  Okay, I understand where the mistake came from. In fact, the table I provided was just an excerpt. It turns out that in the original table, there are rows where the date field (column J in the example) is not yet filled in. So it seems that the formula is not effective in this case.
                  0
                3. tontong
                  Add a date condition later than 01/01/1900 to prevent empty cells from being treated as 0.
                  =SUMPRODUCT(($J$6:$J$100<="30/06/2009"*1)*($J$6:$J$100>"01/01/1900"*1)*($C$6:$C$100<>""))
                  0
                4. Elariana
                  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.
                  0
              2. vince_nus
                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
                0
                1. eriiic Contributor
                  Hello,

                  for i = 2 to [A65536].end(xlup).row

                  eric
                  0
              3. vince_nus
                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
                0
                1. eriiic Contributor
                  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.
                  0
                  1. eriiic Contributor
                    Well, since it was done quickly...

                    If you need to copy all the lines, you have to do it at once:
                    [A1:X1300].Copy
                    Workbooks.Add
                    ActiveSheet.Paste

                    erix
                    0
                2. vince_nus
                  no, it's once!!!
                  a file to be created for each line

                  ++

                  thank you again
                  0
                  1. eriiic Contributor
                    Ak, ok.... 

                     Dim lig As Long For lig = 2 To 5 Rows(lig).Copy Workbooks.Add ActiveSheet.Paste Next lig


                    eric
                    0