PDA

View Full Version : Count of rows excluding a set of values in the another sheet



sindhuja
08-11-2009, 06:05 AM
Hello,

Get the counts of autofiltered values except specific values in a column C of sheet 3. For example sheet 2 with autofilter true in column 5. I need to get the count from column A (sheet2) except the values in the column C of Sheet3.

Am struck with comparing the column A value in sheet 2 with the column C of Sheet 3. If they are equal then the count should not be incremented.

-Sindhuja

Bob Phillips
08-11-2009, 09:00 AM
Can you show an example, I am a tad confused.

sindhuja
08-11-2009, 11:31 AM
Hi Xld,

Have certain criteria based upon which I need to get the counts excluding the Column C values in Sheet3.

For ex, filter “Retail” in column C of Sheet 1. Then in the column A check for the values and get the count excluding set of values in column C of sheet 3.
On the whole total count is 37 on filtering “Retail” out of which 3 should be excluded and the remaining value should be in the respective column (column F of sheet 3) of sheet 3 (37-3=34)

Attached is the sample spreadsheet for your reference.

-Sindhuja

sindhuja
08-13-2009, 06:45 AM
Any Clue on my request....

sindhuja
08-14-2009, 12:53 PM
Any help on this pls..

sindhuja
08-20-2009, 10:55 AM
Can anyone please look into my requirement… its really a concern for me..
Am breaking my heads…

-Sindhuja

Aussiebear
08-20-2009, 02:47 PM
I'm assuming that you require something which will count the required value of a filtered list( in this case Column C of Sheet 2), then subtract the number of occurrences in Column A of Sheet 2, any of the values contained within the list in Column C of Sheet 3. Am I right so far?

The counting of a value in a filtered list is simply =CountIf($C$2:$C$83,C2) to be entered in cell C84.

It is after here that maybe XLD will come in with a possible Sumproduct formula to count the number of occurences of any of the nominated values found in Column A.

mdmackillop
08-20-2009, 03:20 PM
Sub Macro1()
With Sheets("Sheet2")
.Range("$A$1:$I$83").AutoFilter Field:=3, Criteria1:="Retail"
For Each cel In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
Set c = Sheets("Sheet3").Columns(3).Find(cel)
If c Is Nothing Then
i = i + 1
Else
msg = msg & cel & vbCr
End If
Next
End With
MsgBox msg
MsgBox i
ActiveSheet.Range("$A$1:$I$83").AutoFilter
End Sub

Aussiebear
08-20-2009, 03:30 PM
MD, what happens here if the criteria for the autofilter changes?

mdmackillop
08-20-2009, 03:36 PM
Criteria1:=InputBox("Filter by")
I was going to leave this for the OP to figure out!

sindhuja
08-25-2009, 05:53 AM
Thanks a lot..it worked out for me.. :)

sindhuja
08-27-2009, 10:50 AM
Hi,
One more query…

If there is no items to dislay when autofilter on certain criteria it showing me error.

Invalid procedure call or argument in the below line

cel = Left(cel, Len(cel) - 8)

If there are no items to display it means the value is zero
Is there a way to do this..??

With Sheets("Sheet2")
.Range("$A$1:$l$1000").AutoFilter Field:=8, Criteria1:="Retail"
For Each cel In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
cel = Left(cel, Len(cel) - 8)

'MsgBox cel
Set c = Sheets("Sheet3").Columns(7).Find(cel)



If c Is Nothing Then
j = j + 1

msg = msg & cel & vbCr
End If
Next


-Sindhuja

Bob Phillips
08-28-2009, 01:58 AM
Set up a variable abnd test it



With Sheets("Sheet2")

.Range("$A$1:$l$1000").AutoFilter Field:=8, Criteria1:="Retail"
On Error Resume Next
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
On Error Resume Next
If Not rng Is Nothing Then

For Each cel In rng

cel = Left(cel, Len(cel) - 8)

'MsgBox cel
Set c = Sheets("Sheet3").Columns(7).Find(cel)




If c Is Nothing Then
j = j + 1

msg = msg & cel & vbCr
End If
Next