Non-blank cell extract into single column list

[chongbird]

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

<tbody>
</tbody>

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

 

[Matty]

Welcome to MrExcel.

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

Matty

 

[chongbird]

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

<tbody>
</tbody>

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

 

[Aladin Akyurek]

M1 must be left empty.

M2, control+shift+enter, not just enter, and copy down:

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