Results 1 to 4 of 4

Thread: Non-blank cell extract into single column list
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Non-blank cell extract into single column list

    Hi

    First time poster, and an average (at best) excel user.

    I have a table (see below) that lists jobs and who were the corresponding workers . As you would appreciate, some names appear across multiple jobs.

    I would like to create (in a new worksheet called Backdata) a single column list that extracts non-blank cells and only unique text (no duplicates). Is this possible?

    F G H I J
    Job Worker 1 Worker 2 Worker 3 Worker 4
    CR003 Joe Tim Simon
    DE004 Simon
    DF051 Jay
    CR002 Minnie Joe
    CR023 Jack
    DE123 Fred
    DE345 Lisa Fred Simon Joe
    DF657 Minnie
    CR005 Minnie
    DE456 Lisa
    DF567 Mike
    DE304 Mike Joe
    CR567 Angus Joe Lisa Simon


    I am using Excel for Max 2008 so unfortunately cannot do VBA macro. Is it possible to achieve this via a formula? Cheers

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Non-blank cell extract into single column list

    Welcome to MrExcel.

    Could you show us what the list you want should look like give the example table posted?

    Matty

  3. #3
    New Member
    Join Date
    Feb 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Non-blank cell extract into single column list

    Hi Matty,

    Thanks for the reply. I want on a separate worksheet (called Backdata) a list of unique worker names. Ideally it would look something like this.

    A
    Unique Worker list
    Joe
    Tim
    Simon
    Jay
    Minnie
    Jack
    Fred
    Lisa
    Mike
    Angus

    The original data is on a worksheet called "Master". On this worksheet, every day I am adding additional rows of new jobs and assigning workers names to the jobs. I have about 3000 rows of jobs.

    It would be great if I could get a unique list of all the workers, which dynamically updates every time I add a new job with a new worker to the Master worksheet. I suspect it may be possible with an ISERR and INDEX formula, but I haven't quite got my head around these yet and would appreciate some help!

    Cheers

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Non-blank cell extract into single column list

    M1 must be left empty.

    M2, control+shift+enter, not just enter, and copy down:
    Code:
    =IFERROR(INDEX(tbl_text,MIN(IF(COUNTIF($M$1:M1,tbl_text&"")=0,
      ROW(tbl_text)-MIN(ROW(tbl_text))+1)),MATCH(0,COUNTIF($M$1:M1,INDEX(tbl_text,
      MIN(IF(COUNTIF($M$1:M1, tbl_text&"")=0,ROW(tbl_text)-MIN(ROW(tbl_text))+1)),0)),0)),"")
    
    The name tbl_text stands for G2:J14. Note that the definition can be made dynamic. As the name also implies, the data in G2:J14 is expected to be text with length > 0.

    Source. Unique distinct values from multiple columns using array formula | Get Digital Help - Microsoft Excel resource
    The basic MATCH/COUNTIF has been attributed to Eero (a contributor at the now defunct MS newsgroups). I removed the area argument in the INDEX calls and "textified" tbl_text further with adding the &"" bit.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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