Removing duplicates from value

[Kalts]

Hello
Is there a way to remove duplicates from formula outcome. I use concatifs function, but it lists a warehouse name every time the criteria matches, but one mention would be enough.

The formula I use:

[dilipandey]

Hi Kalts,

ConcatIfs appear to be a UDF .. could you upload your excel workbook to enable forum members to have more clarity on this query. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kalts]

Here's my sample.test.xlsx

[Cutter]

Just guessing (because I'm not sure what result you want) but maybe this in G9:

=IF(COUNTIF(E$4:E8,E9),"",ConcatIfs(",",$A$3:$A$9,$B$3:$B$9,E9,$C$3:$C$9,F9))

[Kalts]

Hi Cutter!
Thanks for your reply. The formula you provided is not exactly woring for me. I'll try to explain once more.
There are multiple outcomes with concatif formula in cell G8. I want only unique warehouse names appeared in G column. I need to see in which warehouse the item is situated. Items may be in different warehouses and I only need to know one warehouse name, but now it shows me warehouse name every time the criterias match, like in cell G8 where there are two occurances of warehouse 1.

[Cutter]

As Dilipandey said, your Concatifs function is a UDF (User Defined Function). We can't do anything about the results of a UDF without seeing the source code for it. I, myself, won't be able to help you with it.

[Kalts]

UDF-s source code is here.

Please Login or Register  to view this content.

[Andy Pope]

try

Please Login or Register  to view this content.

[Kalts]

Hello Andy, thanks for reply! How can I change the name of the function? I tried concatifs2, but then it generates "compile error: argument not optional"

[abousetta]

If you mean changing the name of the function that Andy provided then you have adjust the function name he used. So something like:

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

[Kalts]

Yes, I tried that but then I got the error. And all the top part of formula was marked yellow

[Andy Pope]

You would not only need to change the function signature but all references to the function variable within the routine.

Attached includes both orignal and revised functions

[Kalts]

Hello Andy! Your solution seems to be working very well in the sample document. Only problem is that I cannot get it started in my document. I keep on getting "Argument not optional" error. What should I do?
Should I place your funtion on top of the concatifs functions like in the sample page or what?

[Andy Pope]

You should just have been able to replace existing function with revised one.

It's not clear what you have done that is causing the Error. You would need to post a file so we can see.

[Kalts]

I copied your function and created a new module.

[Andy Pope]

Then that would have given you a duplicate function name issue rather than Arguments not optional. Assuming you did not delete you original function.

You know the code works as you have tested the example. So it can only be something you did in preparing your actual workbook, unfortunately we can not see that nor did we see what you actually did. Hard to diagnose this remotely.

[Kalts]

How can you take a look what I'm doing wrong? I don't want to load up my worksheet because it contains too much classified information.

[Andy Pope]

We can't 'take a look' that's the problem. All we can do is describe what you need to do, which was to simply replace your existing code with the new version.

Or you could edit your existing code and add the new INSTR test.

[Kalts]

Ok, I tried to create same function in the sample document and it generates same Compile error:Argument not optional.

[Andy Pope]

Then post that file so we can see and investigate the error.

[Kalts]

Here you go.

832534_2.xlsm

[Andy Pope]

Your ConcatIfs2 function contains referances to the function ConcatIfs.
This is in fact an error as you do not want to call the ConcatIfs function but to continue appending text to the ConcatIfs2 variable.


Looks like you copied my first code posting then altered the function name without reading or doing what I suggested you would need to do in post #12.

You would not only need to change the function signature but all references to the function variable within the routine.

[Kalts]

I got it working! Thank you very much for your help Andy!