Results 1 to 10 of 10
Thread: Counting rows (Excel 2003)

20070415, 21:14 #1
 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

20070415, 21:45 #2
 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

20070415, 21:48 #3
 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

20070415, 21:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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, DF 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.

20070415, 21:57 #5
 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

20070415, 22:02 #6
 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

20070415, 22:25 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Counting rows (Excel 2003)
Here is a formulabased solution, but it'll probably be slow on a large set of data.

20070415, 22:32 #8
 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

20070416, 05:16 #9
 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

20070416, 10:21 #10
 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 nonblank cells, Count counts just numbers. I used CountA since none of the columns were numbers
Steve