none
Counting number of cells in one column and compare to the number of cells in another RRS feed

  • Question

  • I would like to count the number of cells used in column A and compare this to the number of cells used in column J. This set of data usually (but not always) starts in row 152. I would like to have a statement that reads if counta(A152:A2020)=>counta(J152:J2020) then go to the worksheet "Check Sheet" in A12 & mark it "OK".  if counta(A152:A2020) is not equal to or greater than the counta(J152:J2020) then go to the worksheet "Check Sheet" in A12 & mark it "Not OK".

    Because this can vary at certain times, I have a text in column K to indicate where the set is located so I can go to K1 hit the end key and arrow down to find the data. There is also data in rows 10:148 that I want excluded from this comparison. This also can vary in length beyond rows 148

    The spread sheet looks like this (shortened for the example):

    

    so because the count from column A510 is greater than the count of cells from Column J510 the sheet "Check Sheet" would be marked OK 

    • Edited by xanthman4 Wednesday, April 6, 2016 3:02 PM
    Wednesday, April 6, 2016 1:26 PM

Answers

  • <rant>

    "...usually (but not always) starts in row 152..."

    When I see this kind of statement, I get all kinds of unhappy. Do yourself a huge favor and re-design your sheet so the data lives in one place in nice neat rows and columns that have range names and your calculations use range names and live somewhere else. If you do that, then your life gets easier, the sheet can be modified to add functionality without needing to re-design the whole thing, and you won't get calls about the sheet not working because some user decided to stuff the data into some new place and structure you can't handle. You also have the advantage of being able to do things like pivot tables/charts and statistics easily.

    I rant because I've had to pull data out of designs like this and the process is stupidly painful.

    </rant>

    My assumption is that you have discontinuous data that is structured as 1 to many variable length chunks of data. I can't tell if the blank cells are actually blank or if they have formulas that return "" as the answer. I'll assume they're blank. You may need to just write a VBA routine that starts at the lowest possible row to contain data and drives down until you see <some number of blank rows that means 'out of data'> and count those cells that have data, then do the same in your other column.

    If you have no way to flag 'out of data', then you'll just need to drive to the bottom of the sheet.

    Wednesday, April 6, 2016 4:21 PM
  • This is automated so that there are only two chunks of data which gets updated monthly. The usually but not always comes in when there is more data in the first chunk thus moving the second chunk down so they don't over-run each other as they come from two data sources (yes, life would be easier if they went into two different sheets, but life isn't always easier). The columns are consistent but the amount of rows can change. There is a spreadsheet that shows values with a vlookup based on the data in this sheet. Column A has the  formulas used by the vlookup and I want to make sure there is a formula for every row of data. 

    The rows are blank between the two sets of data. 

    what I'm looking for is as you said...

    a VBA routine that starts at the lowest possible row to contain data and drives down until you see <some number of blank rows that means 'out of data'> and count those cells that have data, then do the same in your other column.

     or counting rows highlighted by going to the bottom of the worksheet and moving up to where the data in starts by using this:

    Range("l2").Select
     Selection.End(xlDown).Select
        ActiveCell.Offset(0, -11).Select
    ActiveSheet.Range(ActiveCell, ActiveCell.End(xlUp)).Select
    ActiveSheet.Range(ActiveCell, ActiveCell.End(xlUp)).Select

    Wednesday, April 6, 2016 6:15 PM

All replies

  • I have data that starts at the beginning and then picks up again in the middle of a worksheet. What I would like to do is count the rows of Column A from the second set data and make sure that it is larger than the data that starts in column G in the second set of data. The second set of data can shift rows from time to time so I can't use a cell reference.

    I have the following so far.

    Sheets("Data").Select 
    Range("k2").Select
     Selection.End(xlDown).Select 'This finds an anchor to where the second set of data starts
        ActiveCell.Offset(0, -10).Range("A1").Select 'Moves to column A
    ActiveCell.Offset(2, 0).Select 'beginning of columns to count

    here is where I need help

    something like

    If counta(ActiveSheet.Range(ActiveCell, ActiveCell.End(xldown)).Select > ActiveCell.Offset(0,7).select counta(ActiveSheet.Range(ActiveCell, ActiveCell.End(xldown)).Select then

    Sheets("check sheet").Select

    Range("A1").select

    ActiveCell.FormulaR1C1 = "OK"

    else

    Sheets("check sheet").Select

    Range("A1").select

    ActiveCell.FormulaR1C1 = "Not OK"

    As a note, this data starts a few rows after another set of data so I can't just count column A and column G.

    Tuesday, April 5, 2016 4:43 PM
  • Re: something?

    To provide an answer, one has to understand the question...
      "The count of rows has to be larger than the data" - doesn't make sense.
    Suggest you  reword your explanation.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 1:13 PM
    Wednesday, April 6, 2016 3:34 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, April 6, 2016 8:41 AM
  • <rant>

    "...usually (but not always) starts in row 152..."

    When I see this kind of statement, I get all kinds of unhappy. Do yourself a huge favor and re-design your sheet so the data lives in one place in nice neat rows and columns that have range names and your calculations use range names and live somewhere else. If you do that, then your life gets easier, the sheet can be modified to add functionality without needing to re-design the whole thing, and you won't get calls about the sheet not working because some user decided to stuff the data into some new place and structure you can't handle. You also have the advantage of being able to do things like pivot tables/charts and statistics easily.

    I rant because I've had to pull data out of designs like this and the process is stupidly painful.

    </rant>

    My assumption is that you have discontinuous data that is structured as 1 to many variable length chunks of data. I can't tell if the blank cells are actually blank or if they have formulas that return "" as the answer. I'll assume they're blank. You may need to just write a VBA routine that starts at the lowest possible row to contain data and drives down until you see <some number of blank rows that means 'out of data'> and count those cells that have data, then do the same in your other column.

    If you have no way to flag 'out of data', then you'll just need to drive to the bottom of the sheet.

    Wednesday, April 6, 2016 4:21 PM
  • This is automated so that there are only two chunks of data which gets updated monthly. The usually but not always comes in when there is more data in the first chunk thus moving the second chunk down so they don't over-run each other as they come from two data sources (yes, life would be easier if they went into two different sheets, but life isn't always easier). The columns are consistent but the amount of rows can change. There is a spreadsheet that shows values with a vlookup based on the data in this sheet. Column A has the  formulas used by the vlookup and I want to make sure there is a formula for every row of data. 

    The rows are blank between the two sets of data. 

    what I'm looking for is as you said...

    a VBA routine that starts at the lowest possible row to contain data and drives down until you see <some number of blank rows that means 'out of data'> and count those cells that have data, then do the same in your other column.

     or counting rows highlighted by going to the bottom of the worksheet and moving up to where the data in starts by using this:

    Range("l2").Select
     Selection.End(xlDown).Select
        ActiveCell.Offset(0, -11).Select
    ActiveSheet.Range(ActiveCell, ActiveCell.End(xlUp)).Select
    ActiveSheet.Range(ActiveCell, ActiveCell.End(xlUp)).Select

    Wednesday, April 6, 2016 6:15 PM