Printable View
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
Can you show an example, I am a tad confused.
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
Any Clue on my request....
Any help on this pls..
Can anyone please look into my requirement… its really a concern for me..
Am breaking my heads…
-Sindhuja
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.
[VBA]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
[/VBA]
MD, what happens here if the criteria for the autofilter changes?
[VBA]Criteria1:=InputBox("Filter by")[/VBA]
I was going to leave this for the OP to figure out!
Thanks a lot..it worked out for me.. :)
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..??
[vba]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
[/vba]
-Sindhuja
Set up a variable abnd test it
[vba]
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)
[/vba]
If c Is Nothing Then
j = j + 1
msg = msg & cel & vbCr
End If
Next