Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Counting rows (Excel 2003)

    I have a spreadsheet with about 3,500 rows

    Column B contains a True / False value
    Columns D, E and F contain a Yes / No value
    I need to populate column H with Yes / No based on the following rules
    <UL><LI>If B is True, or any of D, E, or F has a Yes then H is No
    <LI>A maximum of 1/3 of the rows may have a Yes in column H, it doesn't matter which ones as long as the rules above are followed (this is selecting which members of a group to send a survey form to and we need to balance the numbers sent over time).[/list]I need the value in column H to remain stable even if I use AutoFilter to hide all the rows except those with a "Yes" in column H.
    If it would make life easier, I could (with some effort) replace the Yes/No values with True/False ones.

    Any suggestions?

    StuartR

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting rows (Excel 2003)

    I think this will do it Stuart, not yes/no but true/false

    =IF(B2="yes",OR(C2="yes",D2="yes",E2="yes",F2="yes "),"FALSE")
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting rows (Excel 2003)

    This time with a workbook <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting rows (Excel 2003)

    How about this macro (it'll just fill column H with values, so it won't update automatically if columns B, D-F change):

    Sub FillH()
    Dim n As Long
    Dim r As Long
    Dim lngCount As Long
    ' Get last row
    n = Range("B2").End(xlDown).Row
    ' Clear target cells
    Range("H2:H" & n).ClearContents
    ' Loop through populated rows
    For r = 2 To n
    ' Does row meet criteria for "Yes"?
    If Range("B" & r) = False And Range("D" & r) = "No" And _
    Range("E" & r) = "No" And Range("F" & r) = "No" Then
    ' Increase count
    lngCount = lngCount + 1
    ' Have we reached the maximum number of "Yes" yet?
    If lngCount > (n - 1) 3 Then
    ' If so, stop looking
    Exit For
    End If
    ' Set column H to "Yes"
    Range("H" & r) = "Yes"
    End If
    Next r
    ' Fill the remainder with "No"
    Range("H2:H" & n).SpecialCells(xlCellTypeBlanks) = "No"
    End Sub

    See attached sample workbook.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Counting rows (Excel 2003)

    Jezza,

    That does the "easy" bit, but would allow too many rows to be selected. The second part of my problem is to restrict the total number of rows to a maximum of 1/3 of the total.

    StuartR

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Counting rows (Excel 2003)

    Hans,

    That will do it, but I was hoping to avoid using a Macro.

    I tried using a SUBTOTAL worksheet function to count the numbers of rows with Yes, and No as this is documented as including hidden rows in the count, but it seems that rows hidden with an AutoFilter still don't get counted.

    Using Macros might let me fix my other problem (I use INDIRECT functions to populate all the Yes / No cells from information on other sheets, so I can add a new sheet and easily fix up all the references, but this is very slow so I have had to turn Auto Calculation off).

    Stuart goes away to think about it for a while.

    StuartR

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting rows (Excel 2003)

    Here is a formula-based solution, but it'll probably be slow on a large set of data.
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting rows (Excel 2003)

    How about this in H2 (I presume row 1 has a header)

    =IF(OR(B2,COUNTIF(C2:F2, "Yes")>0),"No", IF((COUNTIF($G$1:G1,"Yes")+1)/(COUNTA(F:F)-1)<=1/3,"Yes", "No"))

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Counting rows (Excel 2003)

    Steve,
    Absolutely perfect - thank you. I had to read the formula a few times before I understood it - I've never used COUNTA before but it certainly did the trick here.

    Hans, Jezza,
    Thanks for the suggestions

    StuartR

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting rows (Excel 2003)

    CountA counts non-blank cells, Count counts just numbers. I used CountA since none of the columns were numbers

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •