Can't extract data from a table

[chrisellis250]

Hello everyone,

I want to extract the names of employees from a table (as shown below). The names are in columns H, J and L, so is there a way I can simply have a single column table on the next sheet which displays the data extracted from columns H, J and L, but ignoring zeros and duplicates in the process?

Table 1.JPG

Many thanks,


Chris

[JeteMc]

This solution uses helper columns which may be hidden for aesthetic purposes. The first three helper columns use the following formula to copy the lists of names without the zeros:

Please Login or Register  to view this content.

The next array entered formula* creates a full list of names, with duplicates:

Please Login or Register  to view this content.

The next formula removes duplicates (Note that I added Gary Jones name again in column J to demonstrate):

Please Login or Register  to view this content.

The formula in column A filters out the blank cells from the final helper list:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

[Tony Valko]

This can be done with a single (albeit long) formula.

However, in your sample file there is some kind of unseen "junk" in the blank cells in columns H, J and L that is causing problems.

I'm guessing that there were formulas in these cells that returned formula blanks and the formulas were converted to constant values.

Can you comment on this?