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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Job[/TD]
[TD]Worker 1[/TD]
[TD]Worker 2[/TD]
[TD]Worker 3[/TD]
[TD]Worker 4[/TD]
[/TR]
[TR]
[TD]CR003[/TD]
[TD]Joe [/TD]
[TD]Tim[/TD]
[TD]Simon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE004[/TD]
[TD]Simon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DF051[/TD]
[TD]Jay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CR002[/TD]
[TD]Minnie[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CR023[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE123[/TD]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE345[/TD]
[TD]Lisa[/TD]
[TD]Fred[/TD]
[TD]Simon[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]DF657[/TD]
[TD]Minnie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CR005[/TD]
[TD]Minnie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE456[/TD]
[TD]Lisa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DF567[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE304[/TD]
[TD]Mike[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CR567[/TD]
[TD]Angus[/TD]
[TD]Joe[/TD]
[TD]Lisa[/TD]
[TD]Simon[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 200"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]Unique Worker list[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[/TR]
[TR]
[TD]Jay[/TD]
[/TR]
[TR]
[TD]Minnie[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[/TR]
</tbody>[/TABLE]
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.