VBA to match large blocks of data?

[zakynthos]

I have:

Briefings ID Tracker.xlsx

• About 10,000 (variable, 9 digit) employee no’s in cols A, K and U
• 25 unique briefing no’s in col F (5 variable, 5 digit)

Briefings ID Tracker.xlsm

I need to :

• check each briefing no. against each employee no in the Briefings ID Tracker.xlsx worksheet
• return a ‘1’ in Col C in this (.xlsm) worksheet and a ‘0’ in column D if employee no.s in A and K match
• return a ‘00’ in Cols C and D if the if the employee no.s in A and K do not match.

So each of these 25 unique (variable) briefing no’s will need to be tested against the full list of approximately 10000 employee no’s – i.e 250,000 rows of data!
I have compiled a programme in VBA which will input all the vlookups etc to match the results from the xlsm worksheet to avoid the problem of refreshing all these vlookups!

My problem is: What would be the program steps to run the above checks?

Many thanks

 

[Sektor]

Can you upload some example of your workbook?

 

[zakynthos]

A C D K L U
702950849 1 0 702950849 36451 702950849


Where:
A = employee no
C = 1 (or blank) - read and understood briefing (=1) not read briefing (blank)
D = 0 (or blank) - read and not understood briefing (0), not read briefing (blank)

K = employee no (=A)
L = briefing no
U = employee no (=A and K)

Example: there may be say 5,000 rows for briefing no 36451 in Col L
I need to test each of these rows against all employee no's (the list will vary, but will be between 10000-15000).

There could be, say 6500 rows for briefing no, say 36452, again to be tested against all 10000-15000 employee no's.

I need to return (somewhere on this sheet in 2 columns: 'read+understood', 'read and not understood'

a '1' if A=K and C = 1
a '0' if A=K and D = 0
a '00' if A=K and C= blank and D = blank (ie briefing was not looked at)

I hope this makes it clearer

 

[Sektor]

And where's the list of 15000 Employees? Where it's located?

 

[zakynthos]

The list of employees (variable, up to 15000 max) is located in Cols A, K and U

The 9 digit no's given in my example represent the 'employee list'

 

[Sektor]

So, I take employee in A and extract all his briefings marks? Correct?

 

[zakynthos]

Correct

 

[Sektor]

Could you upload some excerpt of your data?

 

[zakynthos]

I'm not sure how I could upload data to the site. I would if I knew how to

The example above:

A - C- D- K- L- U

702950849 - 1 - 0 - 702950849 - 36451 - 702950849

A employee no (702950849)
C read and understood - (1)
D read and not understood (0)
C/D blanks represent not read the briefing
K -employee no (702950849)
L briefing ID no (36451)
(column U can be ignored was used previously, not relevant to this query)