finding exact matches

F

ft1986

I have 2 system-generated reports that look like this:

A B C D

1 User ID Name Email Role
2 abc Jane [email protected] Approving Officer
3 abc Jane [email protected] Procurement Officer
4 abc Jane [email protected] Receiving Officer
5 def Tim [email protected] Receiving Officer
6 def Tim [email protected] Procurement Officer
..... etc

Each person can have more than 1 role.I want to cross-check against these 2
reports and identify those unmatched entries (i.e. entire row of data is
available in 1 file but not the other). How do I go about doing it? v-lookup
doesn't help as there's no unique ID. Can I use access to do it?
 
G

Gunnar Lysaker

Hi,
Why don't you just make a unique value in another colum. Take the first to
letters of the name and three first letters of the role name. Or convert the
different roles into numbers.
Then, you can easily check two arrays via look ups or index match.

A B D E
F

1 User ID Role
2 abc Approving Officer 1
ABC1
3 abc Procurement Officer 2
ABC2
4 abc Receiving Officer 3
ABC3
5 def Receiving Officer 3
DEF3
6 def Procurement Officer 2
DEF2
 
F

ft1986

It's not very possible/ it would be tedious to do so as they are a few
hundred entries. Moreover, there'll be a new report every month, I can't
possible do the same for all the files monthly. Are there any other ways?
 
G

Gunnar Lysaker

I don`t agree with you.I`ve had some of the same problems before.
Lets assume that you have 100 different roles. Build a database/array with
the different roles and write a number in the column besides. Then, when you
have a new set of data you want to check just use lookup against the database
to get the numbers.(put them in column G) Furthermore, you use left command
and put them together with G. Then you have a unique value.

=left(a1;3)&G
Where A= names and G is number

I guess this operation is done in like 2minutes.
You might wanna send me a file, so I can work it out for you.
There are free add-ins that can solve this problems for you as well.
Last option is VBA.
G.
 
Top