Help with conditional TEXTJOIN for distinct values

[DigitalZugzwang]

Hi all,

I recently posted a problem and Marcelo Branco gave me the perfect solution. Now I have a related issue and unfortunately, I can't seem to take that solution and tweak it for my new issue.

In the table below, there are bid numbers which always start at 1. A job will always have 1 bid but can have many more. Also, the bid numbers can repeat for the same job number if there are multiple activities being preformed. I need to TEXTJOIN the bid numbers based on a selected Job Number. For example if Job Number 1 is entered into the selection cell the result would be: 1, 2, 3 but if Job Number 2 was selected it would be 1. (notes below table on formulas tried)



[TABLE="width: 200"]
<tbody>[TR]
[TD]Job Number[/TD]
[TD]Activity[/TD]
[TD]Bid Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Install[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Decom[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Install[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Install[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Decom[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Install[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Install[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Decom[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Install[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I have gotten this to work with the UNIQUEVALUES function from morefunc.

{=TEXTJOIN(", ",1,UNIQUEVALUES(IF(BidTrack[Job]='WIP Update'!$B$6,BidTrack[Bid],""),1))}

Since not all users will be able to access that add-in, I need a work around. I've tried:

{=TEXTJOIN(", ",1,IF(MATCH(BidTrack[Bid],BidTrack[Bid],0)=MATCH(ROW(BidTrack[Bid]),ROW(BidTrack[Bid])),BidTrack[Bid],""))}

Obviously, this does not address the need for the condition.

I've also tried:

{=TEXTJOIN(", ",1,IF(BidTrack[Job]=B6,BidTrack[Bid],""))}

Unfortunately, this lists every instance of the Bid number is returned

Final Attempt:

{=TEXTJOIN(", ",1,IF(BidTrack[Job]=B6,IF(MATCH(BidTrack[Bid],BidTrack[Bid],0)=MATCH(ROW(BidTrack[Bid]),ROW(BidTrack[Bid])),BidTrack[Bid],""),""))}

This last shot results in blanks for any bid number that isn't wholly unique other than the first occurrence. So bid 1 will only return as a result when job 1 is selected.

I want to thank the community here, it has been extremely helpful and educational. You all are making me look good for my boss, thank you!

 

[barry houdini]

Try this version

=TEXTJOIN(", ",1,IF(FREQUENCY(IF(BidTrack[Job]='WIP Update'!$B$6,MATCH(BidTrack[Bid],BidTrack[Bid],0)),ROW(BidTrack[Bid])-MIN(ROW(BidTrack[Bid]))+1),BidTrack[Bid],""))

 

[DigitalZugzwang]

Barry, you're awesome, it works perfectly. Thank you so much!

 

[barry houdini]

or this one's a bit shorter.....

=TEXTJOIN(", ",1,IF(BidTrack[Job]='WIP Update'!$B$6,IF(MATCH(BidTrack[Bid],BidTrack[Bid],0)=ROW(BidTrack[Bid])-MIN(ROW(BidTrack[Bid]))+1,BidTrack[Bid],""),""))