Data Validation, Offset, and Duplicates

TWH

New Member
Joined
Jun 2, 2004
Messages
32
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
ABCD
1DepartmentGroupLevelJob
2LegalComplianceAnalystFederal Compliance Analyst
3LegalComplianceManagerFederal Compliance Manager
4LegalComplianceManagerCorporate Compliance Manager
5LegalGeneral CounselManagerAttorney
6HRBenefitsManagerBenefits Manager
7HRBenefitsAnalystBenefits Analyst
8HRRecruitingAnalystRecruiting Analyst
9FinanceAccountingManagerA/P Manager
10FinanceAccountingManagerA/R Manager
11FinanceAccountingAnalystA/R Analyst
12FinanceBudgetingManagerBudgeting 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
GHI
1Unique DepartmentGroupLevel
2LegalComplianceManager
3HRGeneral CounselAnalyst
4FinanceBenefits
5Recruiting
6Accounting
7Budgeting
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
ABCDEF
15InternalVendor
16NameJobDepartmentGroupLevelJob
17James BoothIntellectual Property CounselLegalGeneral CounselManagerAttorney
18John EdwardIRS Compliance CounselLegalComplianceAnalystFederal Compliance Analyst
19Tammy WilsonCollege Recruiting LeadHRRecruitingAnalystRecruiting Analyst
20Jennifer GrayReceipts ManagerFinanceAccountingManagerA/P Manager
21Adam LewisReceipts ManagerFinanceAccountingManagerA/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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Jun 2, 2004
Messages
32
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

New Member
Joined
Jun 2, 2004
Messages
32
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
FGHIJKLM
1Department3Group3Level3Department2Group2Unique Department
2LegalComplianceManagerLegalComplianceLegal
3LegalGeneral CounselManagerLegalGeneral CounselHR
4HRBenefitsManagerHRBenefitsFinance
5HRBenefitsAnalystHRRecruiting
6HRRecruitingAnalystFinanceAccounting
7FinanceAccountingManagerFinanceBudgeting
8FinanceAccountingAnalyst
9FinanceBudgetingManager
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

New Member
Joined
Jun 2, 2004
Messages
32
I did exactly that last night when I couldn't think of a better solution. Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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
Top