Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I ignore blanks within my list without losing references?
I have a list that contains 2 columns. Column A is the group name and Column
B is the members of that group. Therefore, Column A is repeating for each entry in Column B. I would like to be able to leave the duplicate entries in Column A blank and just create a list (for use as validation on another sheet), omitting all the blanks. Is this possible? Ex: GROUP MEMBERS Admin John Sue Carol Users Joe Missy Amy Managers Robert Deborah I want to be able to have a validation list that contains Admin, Users, Managers without blanks. |
#2
|
|||
|
|||
You could use the advanced filter function, which would work whether or not
you leave blanks in place of duplicate entries. Enter a heading for column A if you don't already have one (can be just a single character). Highlight the heading and the rest of the column A entries. From the Data menu, select "Filter", then "Advanced filter." Click the "copy to another location" radio button" and the "unique roecords only" check box. Click in the "Criteria Range" box and then click in the cell with your column heading. Click in the "Copy to" box and then click a cell of the spreadsheet that has enough blank cells below it for your list. Each unique entry will be listed and you can copy the list to your other sheet. "sessc" wrote: I have a list that contains 2 columns. Column A is the group name and Column B is the members of that group. Therefore, Column A is repeating for each entry in Column B. I would like to be able to leave the duplicate entries in Column A blank and just create a list (for use as validation on another sheet), omitting all the blanks. Is this possible? Ex: GROUP MEMBERS Admin John Sue Carol Users Joe Missy Amy Managers Robert Deborah I want to be able to have a validation list that contains Admin, Users, Managers without blanks. |
#3
|
|||
|
|||
I've tried that but the issue that I run into is that the filter is only
applied once. I need a method that will dynamically refresh the filtered list whenever new entries are made in the unsorted list. Any suggestions. "eider" wrote: You could use the advanced filter function, which would work whether or not you leave blanks in place of duplicate entries. Enter a heading for column A if you don't already have one (can be just a single character). Highlight the heading and the rest of the column A entries. From the Data menu, select "Filter", then "Advanced filter." Click the "copy to another location" radio button" and the "unique roecords only" check box. Click in the "Criteria Range" box and then click in the cell with your column heading. Click in the "Copy to" box and then click a cell of the spreadsheet that has enough blank cells below it for your list. Each unique entry will be listed and you can copy the list to your other sheet. "sessc" wrote: I have a list that contains 2 columns. Column A is the group name and Column B is the members of that group. Therefore, Column A is repeating for each entry in Column B. I would like to be able to leave the duplicate entries in Column A blank and just create a list (for use as validation on another sheet), omitting all the blanks. Is this possible? Ex: GROUP MEMBERS Admin John Sue Carol Users Joe Missy Amy Managers Robert Deborah I want to be able to have a validation list that contains Admin, Users, Managers without blanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
Vetical Filling blanks in a list | Excel Discussion (Misc queries) | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) | |||
validation list blanks | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |