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?

 

[barry houdini]

Hello Jimmydaux,

I don't think you can use whole columns with UNIQUEVALUES, try restricting the ranges like this

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

confirmed with CTRL+SHIFT+ENTER

That will give you the unique values sorted ascending, adjust ranges as required

 

[Jimmydaux]

Well that worked like charm! Thank you for your help.

I had selected the whole colums since my data can get upwards of 20,000 lines long. Once my macro runs, that data is shrunk to approximately 800 lines. I set the rows included to 2000 and it seems to work just fine.

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

Now I just need to figure out how to refresh the equations since running the macro throws off cell references, even if they are locked.

Thank you for your help, once again.

Jeremy

 

[Jimmydaux]

Ok, same base formula but on a different sheet. I would like to add an "and" clause to the equation.

=SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF(AND(Receipts!$M$2:$M$1000=B5,Receipts!$P$2:$P$1000=0),"; "&Receipts!$C$2:$C$1000,""),1)),"; ","",1)

Do you see anything in this equation that would keep it from working correctly? It returns "#VALUE!". YES I am hitting Ctrl+Shift+Enter to complete the formula.

 

[barry houdini]

Typically you can't use AND in these sort of formulas (because it returns a single result rather than an array), use * in place of AND like this

=SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF((Receipts!$M$2:$M$1000=B5)*(Receipts!$P$2:$P$1000=0),"; "&Receipts!$C$2:$C$1000,""),1)),"; ","",1)

 

[Jimmydaux]

You are a veritable genius!!!! That worked perfectly!

Thank you again for your help

Jeremy

 

[Jimmydaux]

Now, one last one, or hopefully its the last one

If I have that formula in place, pointing to the data on the respective worksheets, the formula changes when I run the macro to format the data even though the cell references are locked. Is there any way to prevent this from happening?

 

[barry houdini]

If you really never want the ranges to change you can use INDIRECT and the range in quotes, e.g. where you have Receipts!$M$2:$M$1000 in the formula replace with

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

 

[Jimmydaux]

Ok I will give that a shot.


Now, I sheepishly have another question.

Is it possible to add another criteria to the following equation in order to exclude results containing the letter "R"?

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

The results from this equation are as follows:
100550R; 107992 ; 110402 ; 110429 ; 110456 ; 110462 ; 110467 ; 110472 ; 116452R; 116453R; 117013R

I would like to exclude the results that include the letter R.

Barry, if you have a tip jar please tell me where it resides

 

[barry houdini]

Barry, if you have a tip jar please tell me where it resides

No problem - the wife keeps it.....


If the "R" is always at the end you can use RIGHT function like this

=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)

or for "R" anywhere....

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

Note that FIND is case-sensitive so that will exclude entries containing "R", if you want to exclude "r" too then change FIND to SEARCH.....