Forum Discussion
Issue with Textjoin, multiple criteria. IF/AND function
Hi I have attached an example workbook with some example data. My goal is for someone to type in the product and the company category and be given a list of companies that match those two cri...
Hi
I tested it and it works great on the sample workbook I attached.
However I cannot get it to work on my main workbook. On my main workbook I have different worksheets with the different data. Is there an issue with the functions you suggested when taking data from different sheets?
Thanks
I tested it and it works great on the sample workbook I attached.
However I cannot get it to work on my main workbook. On my main workbook I have different worksheets with the different data. Is there an issue with the functions you suggested when taking data from different sheets?
Thanks
Different worksheet it not a problem. Most likely you have more than four companies. Then you have to adjust this part: {1.1.1.1} - which is for four companies (or four columns).
- I have tried it selecting only the first 4 Companies and 4 rows of products and it works fine.
How do I modify the function to allow for 30 columns and between 5 and 550 rows?Sorry, my fault.
{1;1;1;1} is for four product rows. More rows more 1's.
If you can change you whole layout it would be easier with Power Query.
One table: Company - Product
And the other table: Company - Category
And one table for the search criteria.
Join the first two tables and filter with the third table.
- Hi
I have sorted it out finally now. I added an IF(OR with ISEMPTY to only start the TEXTJOIN function if both search cells are not empty.
=IF(OR(ISBLANK(B5),ISBLANK($C$12))," ",TEXTJOIN(", ",TRUE,IF(($A$52:$AD$604=B5)*($A$50:$AD$50=$C$12),$A$51:$AD$51,"")))
Thanks for the help.
Jon
- I also tried it on my main worksheet (copy pasted all data into one work sheet to test).
I selected the first 4 columns so the {1,1,1,1} should be correct and it still returns#VALUE!
Is there a formatting issue with these functions? Is there anything else I need ensure is a certain way to use MMULT?
Thanks - Does the position of the Categories matter. I can't understand why it works every time in the example workbook, but I get #value everytime I try in my main one.
=TEXTJOIN(", ",TRUE,IF(MMULT({1,1,1,1},(A37:AD584=A4)*(A35:AD35=C2)),A36:AD36,""))
This is what I am trying to do. Assuming the data is where the formula says, is there any reason this shouldn't work? I tried it with 30 x ,1 but that didn't help.
Thanks again - I have 30 companies (30 columns) and thousands of products.
There are only around 5 categories spread over the companies.