DigitalZugzwang
New Member
- Joined
- Jul 27, 2017
- Messages
- 20
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)
<tbody>
</tbody>
I have gotten this to work with the UNIQUEVALUES function from morefunc.
Since not all users will be able to access that add-in, I need a work around. I've tried:
Obviously, this does not address the need for the condition.
I've also tried:
Unfortunately, this lists every instance of the Bid number is returned
Final Attempt:
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!
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)
Job Number | Activity | Bid Number |
1 | Install | 1 |
1 | Decom | 1 |
1 | Install | 2 |
1 | Install | 3 |
2 | Decom | 1 |
3 | Install | 1 |
3 | Install | 2 |
3 | Decom | 2 |
3 | Install | 3 |
<tbody>
</tbody>
I have gotten this to work with the UNIQUEVALUES function from morefunc.
Code:
{=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:
Code:
{=TEXTJOIN(", ",1,IF(MATCH(BidTrack[Bid],BidTrack[Bid],0)=MATCH(ROW(BidTrack[Bid]),ROW(BidTrack[Bid])),BidTrack[Bid],""))}
I've also tried:
Code:
{=TEXTJOIN(", ",1,IF(BidTrack[Job]=B6,BidTrack[Bid],""))}
Final Attempt:
Code:
{=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!
Last edited: