+ Reply to Thread
Results 1 to 9 of 9

Extracting column text values

  1. #1
    Registered User
    Join Date
    07-08-2019
    Location
    United States
    MS-Off Ver
    2019
    Posts
    29

    Question 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. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    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),"")
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,235

    Re: Extracting column text values

    Is your excel 365?
    if so you have the unique function

    https://exceljet.net/excel-functions...nique-function
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  4. #4
    Registered User
    Join Date
    07-08-2019
    Location
    United States
    MS-Off Ver
    2019
    Posts
    29

    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. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: Extracting column text values

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

  6. #6
    Registered User
    Join Date
    07-08-2019
    Location
    United States
    MS-Off Ver
    2019
    Posts
    29

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

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    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. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-08-2019
    Location
    United States
    MS-Off Ver
    2019
    Posts
    29

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate a column by extracting unique values from another column?
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM
  2. Populate a column by extracting unique values from another column?
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  3. Populate a column by extracting unique values from another column?
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1