Find two values in separate table

D

Dharris144

Board Regular
Joined
Jul 22, 2009
Messages
97
Hello everyone,
I have a spreadsheet "roles" Column A has user name Columns B:Z have their roles
Example
John Doe Display Edit Modify

I export another spreadsheet from our ERP a list of user and roles but it is only two columns so
John Doe Display
John Doe Edit
John Doe Delete

So how do I know if John Doe has a role that is not in the spreadsheet?
Example John Doe Delete is not in the "roles" spreadsheet.

maybe index match?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Sheet "ERP"

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]ROL[/TD]
[TD="align: center"]STATUS[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD]John Doe[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD]John Doe[/TD]
[TD]Delete[/TD]
[TD]Does not exist[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD]Frank smith[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD]Frank smith[/TD]
[TD]Modify[/TD]
[TD]Does not exist[/TD]
[/TR]
</tbody>[/TABLE]

Formula
[TABLE="class: grid, width: 900"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD]Cell		Formula
C2=IF(IFERROR(MATCH(B2,INDIRECT("roles!"& MATCH(A2,roles!A:A,0) & ":" & MATCH(A2,roles!A:A,0)),0), 0)=0, "Does not exist", "")

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet "roles"

roles

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD]Name[/TD]
[TD]Rol 1[/TD]
[TD]Rol 2[/TD]
[TD]Rol 3[/TD]
[TD]Rol 4[/TD]
[TD]Rol 5[/TD]
[TD]Rol 6[/TD]
[TD]Rol 7[/TD]
[TD]Rol 8[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Modify[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Delete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I don't care what anyone says. This deserves a hug!!!!!
DanteAmor said:
Sheet "ERP"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]ROL[/TD]
[TD="align: center"]STATUS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]John Doe[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]John Doe[/TD]
[TD]Delete[/TD]
[TD]Does not exist[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Frank smith[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Frank smith[/TD]
[TD]Modify[/TD]
[TD]Does not exist[/TD]
[/TR]
</tbody>[/TABLE]

Formula
[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Cell		Formula
C2=IF(IFERROR(MATCH(B2,INDIRECT("roles!"& MATCH(A2,roles!A:A,0) & ":" & MATCH(A2,roles!A:A,0)),0), 0)=0, "Does not exist", "")

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet "roles"

roles

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Rol 1[/TD]
[TD]Rol 2[/TD]
[TD]Rol 3[/TD]
[TD]Rol 4[/TD]
[TD]Rol 5[/TD]
[TD]Rol 6[/TD]
[TD]Rol 7[/TD]
[TD]Rol 8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Modify[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Delete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
315
Mouse7681
M
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
196
Peter_SSs
Peter_SSs
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
521
bh24524
B
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
W
  • Question Question
Autofill/size adjacent column next to a table
Replies
1
Views
183
plettieri
P

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top