Thread: VBA code that lookups, stores, and counts unique numbers

Hi, I'm new to the forum and I'm new to coding in VBA. I'm working on a file for work and I'm stumped on how to write the middle steps.

I'm using a raw data spreadsheet from "Workbook A" that generates daily and I need to automate the calculations for the data. Some calculations are in "Workbook A" and "Workbook B".

I have a nested IF function in "Workbook A" that determines if the date in Col H is passed due, has an upcoming due date, or is due much later. The function returns a value of 1 (passed due), 2 (upcoming due date), or 0 (due much later) and stores it the corresponding cell in Col S.

I also have a function in "Workbook B" that gives me what I want from Col N of "Workbook A": =SUMPRODUCT(--(FREQUENCY([Workbook_A.xls]Sheet1!$N:$N,[Workbook_A.xls]Sheet1!$N:$N)>0)). This function returns a count of all the unique items stored in Col N of "Workbook A".

What I need is a function or code (probably code) that will do this:
For cells in "Workbook A",Col S that = 1, look up their corresponding identity in Col N, store the identities in an array, and count the number of unique items in that array. Return the count value in Cell A1 of "Workbook B".

The same thing would be repeated for items in Col S that = 2 but return the count value in Cell A2 of "Workbook B".

I suspect VLookup would be used (or the VBA-equivalent), but I don't know if that can store the value in an array. If necessary, the columns can be re-arranged so Col S becomes Col A so a database function can be used.

Any help would be greatly appreciated! Thanks in advance!