Extracting column text values

Hello all! Question here,

I have a data set with a lot of rows. Each row has a department. There are only 5 departments so a lot of rows have the same department.

If you were to put a filter on the department column you would see the Five departments. Is there a formula that can return the five departments in another sheet say in cells A1:A5?

Thanks!!!

jeffreybrown,

Thank you for your reply! This solution works, however, the first cell I put the formula in is returning a 0. After that is works just fine. Any idea why? Thanks!

jeffreybrown, the formula works perfect. I am attaching the same workbook that you provided but I have edited the Sheet2 to put the departments into a table format with a total row at the bottom. Is there a way to get the new table to update when a new department is entered?

For example: Sheet1 A33 we add 'Summer', then I want Sheet2 A14 to update to say 'Summer' and the total row to slide to A15.?

All good. Let me explain my situation a little better.

I have a giant data set (20,000+ rows) with a Department column. There are normally less than 20 departments in any given file size.
I use a pivot table to gather metrics on the giant data set based on the departments completion performance %. From that pivot table, I have another table
that updates with the pivot table and calculates the Department Performance %.

Now on the sheet we are working on, you will see what it is that I want to accomplish. I list ALL departments using the function you provided.
and for each period I use a VLOOKUP function to find the associated Performance % with that department from another table.

This whole table is then used to make a Chart to show each departments performance % trend for for each period as well as the TOTAL
TREND which is the average/each period. I have the chart made already. I now just need the table to update as new departments updates and to either
keep the TOTAL ROW or insert one somehow. See the attached file.