How to count cells in a dynamic range matching two criteria given in table headers
Category: [Excel] Demo Available
How to count cells in a dynamic range matching two criteria given in table headers
Requirement:
The user has a workbook with two sheets - say "core data" and "plot", which I get from an access database and has always different length (rows).
What the user does is basically copy column "N" of "core data" sheet, remove the duplicates and paste them in column "A" starting from cell "A2" of "Plot" sheet. The same I do with column "F" of "core data" sheet, but paste them in row 2, starting from cell "B2" of "Plot" sheet. A table I have now. The size of which depends on the database size.
What the user wants to do after this point is, for each cell in the table, to go through columns N and F of "Core Data" sheet and count cells that match both the value in A column and 1st raw of "Plot" sheet (which are the "headers?" of the table - The user has attached an image of how the worksheet "Plot" looks). The user can never know size of the table.
Solution:
Have you considered a pivot table? Column N as the column field, column F as the row field, and ID (count of) as the data field. This will automatically remove duplicates.
Programatically,
Code:
Sub x()
Dim ws As Worksheet, pvtCache As PivotCache, pvt As PivotTable
Dim StartPvt As String, SrcData As String
SrcData = "RMA Core data!" & Sheets("RMA Core data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Set ws = Sheets.Add
StartPvt = ws.Name & "!" & ws.Range("A3").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")
With pvt.PivotFields("Source")
.Orientation = xlRowField
.Position = 1
End With
With pvt.PivotFields("Error code")
.Orientation = xlColumnField
.Position = 1
End With
pvt.AddDataField ActiveSheet.PivotTables(pvt.Name).PivotFields("ID "), "Count of ID ", xlCount
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by StephenR.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count blanks if 1 of 2 conditions are met |
| How to count weekdays in a month excluding holidays |
| How to use Excel MID() but counting from Right |
| How to COUNTIF using input cell as range depth |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.