Data Validation, Offset, and Duplicates

[TWH]

Hi All,

I've been struggling a bit with a way to solve this issue; I hope that I can explain it well enough. I have multiple lookup sheets (approx. 12) in a workbook that are similar to below (which go down for thousands of rows of data) with Col. D actually being the most unique value. Each worksheet references a different vendor so they must all be on separate sheets as vendors can be added or removed from year to year. However, you must go through the selections of Col. A then Col. B. then Col. C to get the appropriate entries for Col. D. Once the data values are in place there will be no new rows or columns added to the lookup sheets thus no need to make the tables dynamic.

Excel Workbook
	A	B	C	D
1	Department	Group	Level	Job
2	Legal	Compliance	Analyst	Federal Compliance Analyst
3	Legal	Compliance	Manager	Federal Compliance Manager
4	Legal	Compliance	Manager	Corporate Compliance Manager
5	Legal	General Counsel	Manager	Attorney
6	HR	Benefits	Manager	Benefits Manager
7	HR	Benefits	Analyst	Benefits Analyst
8	HR	Recruiting	Analyst	Recruiting Analyst
9	Finance	Accounting	Manager	A/P Manager
10	Finance	Accounting	Manager	A/R Manager
11	Finance	Accounting	Analyst	A/R Analyst
12	Finance	Budgeting	Manager	Budgeting Manager
Vendor1

Excel 2007

I've also created named ranges for the above table based on the column header name as well as below to retrieve the unique values.

Excel Workbook
	G	H	I
1	Unique Department	Group	Level
2	Legal	Compliance	Manager
3	HR	General Counsel	Analyst
4	Finance	Benefits
5		Recruiting
6		Accounting
7		Budgeting
Vendor1_Ref

Excel 2007

There is a data entry sheet in which the user will populate a worksheet like below which category the employee fall into. Col. C, D, E and F are all data validation fields populated from the above forms using OFFSET/MATCH formulas.

Col. C = Unique_Department
Col. D = OFFSET(department,MATCH(C17,department,0),1,COUNTIFS(department,C17),1)
Col. E = OFFSET(department,MATCH(C17&D17,department&group,0),1,COUNTIFS(department,C17,group,D17),1)
Col. F = OFFSET(department,MATCH(C17&D17&E17,department&group&level,0),1,COUNTIFS(department,C17,group,D17,level,E17),1)

Excel Workbook
	A	B	C	D	E	F
15	Internal		Vendor
16	Name	Job	Department	Group	Level	Job
17	James Booth	Intellectual Property Counsel	Legal	General Counsel	Manager	Attorney
18	John Edward	IRS Compliance Counsel	Legal	Compliance	Analyst	Federal Compliance Analyst
19	Tammy Wilson	College Recruiting Lead	HR	Recruiting	Analyst	Recruiting Analyst
20	Jennifer Gray	Receipts Manager	Finance	Accounting	Manager	A/P Manager
21	Adam Lewis	Receipts Manager	Finance	Accounting	Manager	A/R Manager
Data Entry Sheet

Excel 2007

My issues, if you have figured by now, is that the drop downs in Col. D & E on the Data Entry Sheet contain duplicate values based on the selection made in Col. C. Col. C and F work as to be expected. However, what's the best way for me to have the data validation only display the unique values for the selections made in Col. D & E?

Thanks in advance.

 

[Jerry Sullivan]

Hi,

I'm not aware of a formula-only way to have a validation list filter out duplicates. You stated

Once the data values are in place there will be no new rows or columns added to the lookup sheets...

Are you saying that the data doesn't change either? If that is correct, then could you just have a macro that you run when each sheet is setup that creates a validation list of unique values for Columns D & E and places those lists in Columns J & K?

 

[TWH]

Are you saying that the data doesn't change either? If that is correct, then could you just have a macro that you run when each sheet is setup that creates a validation list of unique values for Columns D & E and places those lists in Columns J & K?

Col. G, H & I contain the unique values. I'm not sure a macro is most efficient since all of the vendor sheets are entered all at the same time.

On the Data Entry Sheet, the selection made in Col. D, E and F are all dependent on the selection made in the column before which is populated by user selection from a drop down. I'm having trouble with the data validation for Col. D & E.

I've searched the message board, checked the contextures site, and the web and can't seem to find a solution or maybe I'm not entering in the correct search parameters.

 

[TWH]

Just want to clarify my last post, the user makes the selection in the data entry sheet (example 3) for Col. C to F. The vendor sheets (example 1 & 2) won't change.

 

[Jerry Sullivan]

I was a little slow to catch on, but now I follow what you are doing.

It looks like a really nice approach and as you point out, it is just missing that piece of eliminating the duplicates.

Would this be an acceptable workaround...

When you do your one time setup of your Vendor Sheets, you copy your data in Columns A:D to generate a lookup table for each of the 4 columns on your Data Entry Sheet that need validation lists. It just takes a minute to create these manually by applying removeduplicates on all fields for each table. In addition to the first image in your OP, you would add these 3 tables:

Excel Workbook
	F	G	H	I	J	K	L	M
1	Department3	Group3	Level3		Department2	Group2		Unique Department
2	Legal	Compliance	Manager		Legal	Compliance		Legal
3	Legal	General Counsel	Manager		Legal	General Counsel		HR
4	HR	Benefits	Manager		HR	Benefits		Finance
5	HR	Benefits	Analyst		HR	Recruiting
6	HR	Recruiting	Analyst		Finance	Accounting
7	Finance	Accounting	Manager		Finance	Budgeting
8	Finance	Accounting	Analyst
9	Finance	Budgeting	Manager
Vendor1

Excel 2007

Your Validation Lists formulas would now be:
Col. C =department1
Col. D =OFFSET(department2,MATCH(C17,department2,0)-1,1,COUNTIFS(department2,C17),1)
Col. E =OFFSET(department3,MATCH(C17&D17,department3&group3,0)-1,2,COUNTIFS(department3,C17,group3,D17),1)
Col. F =OFFSET(department4,MATCH(C17&D17&E17,department4&group4&level4,0)-1,3,COUNTIFS(department4,C17,group4,D17,level4,E17),1)

This example assumes naming your ranges to correspond to 1,2,3 or 4 column lookup tables, so your first image in your OP would include department4, group4, etc...

If this approach would be okay, it wouldn't be hard to make a macro that would take your 4 column lookup and spawn off the other 3 tables including defining the named ranges.

 

[TWH]

I did exactly that last night when I couldn't think of a better solution. Thanks for your help!