VBA Counting Unique Values

[Flash']

Morning

Im having trouble counting unique records from my data sheet

on my 'Summary' sheet i have the below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

Month​

[/TD]
[TD]

Year​

[/TD]
[TD]

Records Uploaded​

[/TD]
[TD]

Unique Records Uploaded​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

Oct​

[/TD]
[TD]

15​

[/TD]
[TD]

500​

[/TD]
[TD="align: center"]?
[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

Nov​

[/TD]
[TD]

15​

[/TD]
[TD]

400​

[/TD]
[TD="align: center"]?
[/TD]
[/TR]
</tbody>[/TABLE]

On my 'URData' Sheet i have Columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]

B​

[/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[/TR]
[TR]
[TD]

Account Numbers​

[/TD]
[TD]

Month​

[/TD]
[TD]

Year​

[/TD]
[/TR]
[TR]
[TD]467162761[/TD]
[TD]Oct[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]156776464[/TD]
[TD]Oct[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


I have used the below code which works great for Counting Records Uploaded on my 'URData' Sheet which meet the criteria (Q =month & R =Year)

Sub RecordsUploaded()
Dim Month As String
Dim Year As Integer
Dim Answer As Long
Dim i As Long
Dim EnNum As Long
Dim lastrow As Long

'end range of months
EnNum = (Worksheets("Summary").Cells(Rows.Count, "B").End(xlUp).Row) - 14

'Endrange of data
lastrow = Worksheets("URData").Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To EnNum

Month = Worksheets("Summary").Cells(14 + i, 2).Value
Year = Worksheets("Summary").Cells(14 + i, 3).Value

Answer = WorksheetFunction.CountIfs(Worksheets("URData").Range("Q2: Q" & lastrow), Month, Worksheets("URData").Range("R2: R" & lastrow), Year)
Worksheets("summary").Cells(14 + i, 4).Value = Answer

Next
End Sub

But i need to also count the unique account numbers in column B, with the criteria in Column Q and R

im trying looking all over online but cant seem to be able to find a VBA solution. Any help would be highly appreciated

Thank you

 

[oldbrewer]

a pivot table applied to just column B will give you the count of unique numbers

 

[Flash']

An Account number could appear twice in one month and once in another month. But i would need it to count as a unique value for both months

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]129013096[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]286008162[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]129013096[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]129013096[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]
Oct 15 - 2 unique
Nov - 1 unique

Hope this makes sense?

 

[Flash']

sorry to bump

 

[kalak]

sorry to bump

Perhaps if you can give a better example of just what you want ...

I guess you know your problem well, but for me your outline is a bit too sketchy and too much guesswork needed.

 

[oldbrewer]

ok pivot table looking at account numbers and dates only will list the unique numbers for each month - change your B,Q,R to acnum, monnth, year

and make a pivot - if you have several years data make an additional column called monyr and put in that column = Q1&R1 then pivot on acnum and monyr

 

[Flash']

Thanks for response all, sorry for bother. but figured it out in the end by creating a unique list first then using a seperate sub

Unable to close this thread, for some reason it does not give me the option. and when clicking on my profile it directs me to another users profile with similer user '<dd class="userprof_module">Flash2002's</dd>'

Admin issue?