# Extracting column text values

1. ## 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!!!

2. ## Re: Extracting column text values

How about on Sheet2 with a header on A1 that says Department

In A2 >> =IFERROR(LOOKUP(2,1/(COUNTIF(\$A\$1:\$A1,Sheet1!\$A\$2:\$A\$32)=0),Sheet1!\$A\$2:\$A\$32),"")

3. ## Re: Extracting column text values

if so you have the unique function

https://exceljet.net/excel-functions...nique-function

4. ## Re: Extracting column text values

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!

5. ## Re: Extracting column text values

No, not sure why, but check out this sample.

6. ## Re: Extracting column text values

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.?

7. ## Re: Extracting column text values

That's seems like a not so good design and will make like complicated and not I don't see a way to do that.

In the table on Sheet2, you will have to pull the formula down to get the new results off of the table on Sheet1.

Why not just put the total in C1 and pull the formula down to a distant row?

Sorry, I can't see how to do what you ask.

8. ## Re: Extracting column text values

This is an option, but it's not an option by putting the count below the range. It also uses a pivot table, but the caveat to a pivot table, you will have to right click the table and select refresh.

Only VBA can do that automatically.

9. ## Re: Extracting column text values

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)