Forum Discussion

MrRAMMount's avatar
MrRAMMount
Copper Contributor
Oct 29, 2019

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...
TEXTJOIN
MrRAMMount's avatar
MrRAMMount
Copper Contributor
Oct 29, 2019
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?
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Oct 29, 2019

MrRAMMount 

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.

 

  • MrRAMMount's avatar
    MrRAMMount
    Copper Contributor
    Oct 30, 2019
    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
    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Oct 30, 2019

       

      MrRAMMount wrote:
      Thanks for the help.

      Jon

      You're welcom Jon.

       

       

  • MrRAMMount's avatar
    MrRAMMount
    Copper Contributor
    Oct 30, 2019

    Detlef_Lewin 

     

    Hi

     

    Thanks again for your help. 

     

    I am not very savvy with more complex functions in excel like MMULT and power querys.

     

    Could you suggest how the scaled up formula might look?

     

    At the minute I have about 30 columns. Each column 1st row is a Company, each column second row is that company's category and each company has between 5 and 550 products in that column.

     

    I can put the search criteria anywhere.

     

    Considering this, is there a way to still use the formula you suggested before?

     

    Thanks

     

    • MrRAMMount's avatar
      MrRAMMount
      Copper Contributor
      Oct 30, 2019

      Hi

       

      I seem to have sorted it out. It works mostly as I intend.

       

      Here is a link to an example workbook where it functions properly.

       

      https://docs.google.com/spreadsheets/d/1AKp5Ajg77tpwvCYECbnkg9fSls6SrdbYANrIA305trg/edit#gid=0

       

      My last question is what can I add to the working formula in the doc above that will not return any values if either of the search criteria are empty?

       

      Thanks

Resources