How to effectively subtract one unique list from another?

[DigitalZugzwang]

Hi Everyone!

So I've got an issue that I can't seem to find a solution for. What I need is to generate a cell that shows the Open Jobs (non-closed). With TextJoin function I can put them all in the same cell, however I'm struggling to generate the unique list because I need to remove Closed Jobs. I can make a unique list of Started Jobs and Closed Jobs (using uniquevalues function from morefunc). Unfortunately, I can't seem to subtract one from the other...so to speak.

Below we have a table showing how we track our Work In Progress (WIP). As you can see each Job has a unique number and multiple activities. How do we make a list of the Started Jobs and remove the Closed Jobs? Also, I don't think using the advanced filter is an option as I need this to update as users populate the status of jobs. VBA is an option but because of other code being used, it could very well act funky.

Thank you very much in advance.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Job Number[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start Job[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WIP[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Close Job[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Start Job[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WIP[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Start Job[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Start Job[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]WIP[/TD]
[/TR]
</tbody>[/TABLE]

 

[DigitalZugzwang]

Should I assume because there have been no replies that there is no good way to compare two groups of distinct values to generate a list where there are differences? To accomplish a list of WIP do I need to rework the logic of the entire sheet?

 

[Aladin Akyurek]

What is the desired output for the sample you posted?

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Job Number​

[/td][td]

Activity​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

Start Job​

[/td][td][/td][td]

2, 3, 4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

WIP​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1​

[/td][td]

Close Job​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2​

[/td][td]

Start Job​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

2​

[/td][td]

WIP​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td]

Start Job​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

4​

[/td][td]

Start Job​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

3​

[/td][td]

WIP​

[/td][td][/td][td][/td][/tr]
[/table]

Array formula in D2
=TEXTJOIN(", ",1,IF(FREQUENCY(A2:A9,A2:A9),IF(ISNA(MATCH(A2:A9,IF(B2:B9="Close Job",A2:A9),0)),A2:A9,""),""))
Ctrl+Shift+Enter

M.

 

[DigitalZugzwang]

The output for the above table should be 2, 3, 4 as Marcelo has it in his table because only Job Number 1 is closed. When I attempt Marcelo's formula, it lists the jobs completed along with every instance of the job number as well as leaving blanks for the ones removed. So for the above example it looks like:

1, 1, 1, , , , , , ,

Now the above result occurs when I do this as an array. If I do it as a normal formula, it simply lists every job number one time. I believe part of the issue is that every job number will have 1 Start, 1 or more WIP, and eventually 1 Close statuses. So when we search the Job numbers one row might show Close but other rows will have different statuses.

This does feel like a good start. Would it matter that I'm using table references (e.g. WIPtrack[job]) rather than A2:A9?

Thank you

 

[Marcelo Branco]

The output for the above table should be 2, 3, 4 as Marcelo has it in his table because only Job Number 1 is closed. When I attempt Marcelo's formula, it lists the jobs completed along with every instance of the job number as well as leaving blanks for the ones removed.

It's an array formula. Have you confirmed the formula with Ctrl+Shift+Enter simultaneously (not just Enter)?

M.

 

[Marcelo Branco]

Using structerd references (table references)

Array formula
=TEXTJOIN(", ",1,IF(FREQUENCY(WIPtrack[Job Number],WIPtrack[Job Number]),IF(ISNA(MATCH(WIPtrack[Job Number],IF(WIPtrack[Activity]="Close Job",WIPtrack[Job Number]),0)),WIPtrack[Job Number],""),""))
Ctrl+Shift+Enter

M.

 

[DigitalZugzwang]

Marcelo Thank You!!!!

I miss-typed one of my ranges from your original formula and that is why it wasn't working. This is perfect!

 

[DigitalZugzwang]

*accidental duplicate post*

 

[Marcelo Branco]

You are welcome. Glad to help

M.