Need to assess multiple values from an array, maybe a VLOOKUP, not really sure!

[MyGConnorD]

Hi All, looking for someone to help me out with this, as i haven't got a clue myself!

Here's the screenshot of the excel sheet.

For my work we have primary and QC work in which someone working on Primary, shouldn't work on QC side within the same "Program Name". Essentially what i want to do is for the cells in column D, if they are e.g. within the "L001" subset from column A, then I want to lookup the potential Values in column B for that subset, and ensure that (maybe through Data Validation or Conditional Formatting) flag up that these initials have already been entered in the "Primary" column (B).

I managed to get this to work, specifying "L001", however that would require me entering in manually in to hundreds of cells, and new row's will likely be added throughout my work (However, L001 will always be ordered in a group together, L002 etc.). So was looking to see if there was a general formula that could work for this.

Thank you in advance all, if i haven't made sense whatsoever, please let me know!

Using Office 365 btw.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F
1	Program Name	Primary Programmer	Date/Time Created	QC Reviewer	Date of QC Review	QC Comments
2	L001	CD	6/3/2022	TB	6/3/2022
3	L001	CD	6/9/2022	IE	6/9/2022
4	L001	CD	6/15/2022	DB	6/15/2022
5	L001	CD	6/22/2022	CD
6	L002	TB
7	L003	CD
8	L003	TB		CD
9	L004
10
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D	Expression	=COUNTIFS(A:A,A1,B:B,D1)	text	NO

 

[MyGConnorD]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F
1	Program Name	Primary Programmer	Date/Time Created	QC Reviewer	Date of QC Review	QC Comments
2	L001	CD	6/3/2022	TB	6/3/2022
3	L001	CD	6/9/2022	IE	6/9/2022
4	L001	CD	6/15/2022	DB	6/15/2022
5	L001	CD	6/22/2022	CD
6	L002	TB
7	L003	CD
8	L003	TB		CD
9	L004
10
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D	Expression	=COUNTIFS(A:A,A1,B:B,D1)	text	NO

Hi Eric, thanks for your quick response! Thank you, I'm very new, as you can probably tell haha!

So that works but within columns, whereas I think I require an extra step in that I need this rule to occur within a subset of e.g. "L001". So basically it's fine for the "CD" to be there in cell D8 in the previous screenshot as that relates to program name "L003".

Below i've got another screenshot now of how it should look when split by subsets:

Hope I've explained the issue to some degree, many thanks for your help!

 

[Eric W]

I'm afraid I don't understand the difference. D8 was highlighted in the previous example because the program on A8 was L0003, and CD worked on L0003 (row 7). That same situation appears in your most recent example (rows 10 and 8).

And no worries about being new! You'll get up to speed in no time. One suggestion I would make is that you look into the XL2BB add-in tool. This tool (which I used in post 2) allows you to post a mini-sheet that the helpers here can copy directly to Excel so they can work on it. It's far easier than having to retype everything from a screen print. See the link in my signature or the reply box. It's easy to download, install, and use.

 

[MyGConnorD]

I'm afraid I don't understand the difference. D8 was highlighted in the previous example because the program on A8 was L0003, and CD worked on L0003 (row 7). That same situation appears in your most recent example (rows 10 and 8).

And no worries about being new! You'll get up to speed in no time. One suggestion I would make is that you look into the XL2BB add-in tool. This tool (which I used in post 2) allows you to post a mini-sheet that the helpers here can copy directly to Excel so they can work on it. It's far easier than having to retype everything from a screen print. See the link in my signature or the reply box. It's easy to download, install, and use.

Ahhh right i see now, sorry my bad! Thank you for your help!

Yeah hopefully spend a lot more time on here now, got a couple of process improvements in mind but have nowhere near your excel genius to execute them haha

Awesome, i'll take a look at that now, thank you!

 

[Eric W]

There are a lot of very bright people here who are very generous with their time. I'm sure you can find some good improvements for your system.

Glad I could help!

 

[MyGConnorD]

Sorry Eric to keep on this thread, This works awesome for Conditional Formatting now, however was thinking of maybe putting it into a Data Validation so that it would pop up a warning, however it seems not to like that, is there a reason for this? Conditional formatting is working amazing now (thank you!) so no issue just wondering why it wouldn't work with Data Validation tool also?

 

[Eric W]

Same basic formula, you just need to tweak it a little for DV:

In essence you just need to reverse the TRUE/FALSE value, so that the DV formula says TRUE if there is no match, thus allowing it to be entered. You can put your own customer error messge on the Error Alert tab.

 

[MyGConnorD]

Same basic formula, you just need to tweak it a little for DV:

View attachment 67725

In essence you just need to reverse the TRUE/FALSE value, so that the DV formula says TRUE if there is no match, thus allowing it to be entered. You can put your own customer error messge on the Error Alert tab.

You are an Excel Wizard my friend haha, thank you very much!