Unique values in If driven mconcat equation

[Jimmydaux]

Hello all!

This site has been quite helpful in getting me to this point in my equation, but I have come to a personal impasse.

=SUBSTITUTE(MCONCAT(IF('Incremental Deliveries'!$J:$J=A4,"; "&'Incremental Deliveries'!$C:$C,"")),"; ","",1)

This yields all of the information that I need, presented correctly, but I need to display only the unique values. I have tried to use the formula =Uniquevalues in many different iterations and locations, but cannot seem to get it to do what I need. I generally just returns "#Value".

Can anyone help me with this formula?

 

[Jimmydaux]

Barry,

Both of the equations to exclude values including R, include the word FALSE at the beginning. Any idea why that is or more importantly, how to avoid it?

FALSE107992 ; 110402 ; 110429 ; 110456 ; 110462 ; 110467 ; 110472

 

[barry houdini]

OK, sorry, yes, add in a blank to prevent FALSE returns (in red)

=SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF('Incremental Deliveries'!$J$1:$J$2000=B5,IF(RIGHT('Incremental Deliveries'!$C$1:$C$2000)<>"R","; "&'Incremental Deliveries'!$C$1:$C$1000,""),""),1)),"; ","",1)

 

[Jimmydaux]

You beat me to it!

I found that on my own and got really excited that I was going to be able to post the solution that I found so i wouldnt look like such an excel ignoramous. I can never beat the expert

Thank you for your quick responses.

 

[Jimmydaux]

Barry,


I tried to apply the portion of the equation that limited results to those not containing "R"s to another equation but it doesnt seem to be working. In using the * as an "and" function, is it possible to do more than two criteria? The equation I am trying to use is:


=("Completed PO's: ")&SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF((INDIRECT("Receipts!$M$2:$M$1000")=B6)*(INDIRECT("Receipts!$P$2:$P$1000")=0)*(RIGHT(INDIRECT("'Incremental Deliveries'!$C$1:$C$2000"))<>"R"),"; "&INDIRECT("Receipts!$C$2:$C$1000"),""),1)),"; ","",1)

Is this equation getting too complicated?

 

[Aladin Akyurek]

Barry,


I tried to apply the portion of the equation that limited results to those not containing "R"s to another equation but it doesnt seem to be working. In using the * as an "and" function, is it possible to do more than two criteria? The equation I am trying to use is:


=("Completed PO's: ")&SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF((INDIRECT("Receipts!$M$2:$M$1000")=B6)*(INDIRECT("Receipts!$P$2:$P$1000")=0)*(RIGHT(INDIRECT("'Incremental Deliveries'!$C$1:$C$2000"))<>"R"),"; "&INDIRECT("Receipts!$C$2:$C$1000"),""),1)),"; ","",1)

Is this equation getting too complicated?

Why do you need INDIRECT here?

 

[Jimmydaux]

Why do you need INDIRECT here?

The macro that I run to format the raw data was throwing off those equations. I need it to point to those ranges and not dynamically adjust to the source data as the macro does its work.

 

[Aladin Akyurek]

The macro that I run to format the raw data was throwing off those equations. I need it to point to those ranges and not dynamically adjust to the source data as the macro does its work.

Could you name those INDIRECT bits and see whether runs correctly with them?

Example:

Mrange which refers to:

=INDIRECT("Receipts!$M$2:$M$1000")

If this appears feasible, the formula could be made more readable/manageable.

 

[Jimmydaux]

I woulndn't mind simplifying that equation at all Don't the named ranges dynamically update as well? When I name a range that includes columns A:E, once my macro runs it will change the range to B:AG. I basically need it to point to the "post macro" reference, which is why Barry suggested the "Indirect" so it wouldn't change the reference at all.

 

[Jimmydaux]

So, is it possible to use three sets of criteria separated by asterisks(*)?