Formula to list all matches excluding duplicates

[Imbizile]

Hello

Im looking for a clever way to combine lists and find each unique value for a specific lookup. I tried to simplify it by the following example with numbers and colors:

Different lists with some uniques (ie 4 only represented one time with the value Green), some duplicates (ie 2 represented several times with the value Red), and some with different values for the same lookup (ie 1 represented with both Blue, Green and Red):
25-11-2017 10-20-31.jpg

I need a formula to list this in a combined cell like below, where it will only list the duplicate matches once and separate different matching values with comma (or something similar):
25-11-2017 10-28-38.jpg

I attached a sheet with the example above.

I might add that I have several huge lists (15.000 lines each), so if possible at all, a performance friendly solution would be preferable.

Any help would be highly appreciated

[Glenn Kennedy]

You're probably best to use a UDF for that.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then use this array formula:

=concatall(IF($B$3:$H$5=B8,$C$3:$I$5,""),", ",TRUE)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

enable macros on opening...

[samba_ravi]

what is the maximum number of possible lists.

[shukla.ankur281190]

Try with a helper column.

F8

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across K column and down.

C8

Please Login or Register  to view this content.

Check the attached file.

[Imbizile]

Thanks so much!

I will try to experiment with the suggested solutions.

@nflsales: The lists are dynamic and in different sheets, but my guess is that 5 lists are maximum.

[Imbizile]

@Glenn Kennedy
Im trying to work with your solution, but can this be used if the lists were in other sheets, like in the example I uploaded below?

[Glenn Kennedy]

No. It won't work. That was a very significant fact that you left out of your original post!!

However, if you are OK with having the results in separate cells, this array formula will work.

Please Login or Register  to view this content.

Hopefully you can see that it's made up of separate bits that can be expanded easily to cope with 5 lists.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Imbizile]

Yeah I am very sorry, I should not have left out that fact, but I just tried to keep it simple, as the setup in my actual file is quite complicated. But I think I can work with your last solution

Really appreciate the help!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Imbizile]

Turns out I could actually use both solutions from you Glenn for different purposes, so your first effort was not wasted

But I would like to use the second solution several times throughout the columns, listing different columns from the different sheets. For instance a Color3 listing after the Color1 listing.

The problem I run into here, is the $B3:F3 part in the COUNTIF($B3:F3, List1!$D$2:$D$4)=0), as it should actually not include C-F columns for the second listing.

I attached a sheet to illustrate this and I added an empty column in the lists to point out that there could be several other columns with data in between.

Any way to solve this?

Again thanks so much for all the help - im learning so much

[Glenn Kennedy]

Just introduce a blank column (to allow the formula to have a starting point - one which has no potentially interfering data in it) and adjust the ranges as shown.

[Imbizile]

That will work - your help is highly appreciated!

[Imbizile]

Hello again Glenn

The first ConcatAll function you came up with is amazing - I use it for a bunch of things. One problem though - if any of the lists contains an error (ie #I/T) everything will fail. I tried messing around with some <>"#I/T" in the formula, but without success. Any workaround for this?

I attached a sheet to show what I mean.

[Glenn Kennedy]

Remove the error in the first place.

IFERROR(VLOOKUP(B13,B8:B11,1,FALSE),"")

[Imbizile]

Yeah, but my different lists might contain errors for several reasons that I rather not remove cause they are validating on something else.

But I just figured it out. Might not be the be way but this works (concating 4 columns for each row in this case and also removing 0 for empty cells):

Please Login or Register  to view this content.

Thanks again

[Imbizile]

Hello again Glenn

It seems that my solution for making it work with errors does not work in all cases, but I will try to mess a bit more with a sustainable workaround for that.

But I have noticed that while running other scripts, that does not involve any Concatall formula, it steps into into the Concatall formula and runs it through. For instance this sometimes happen when I run a sub that clears all contents in the some sheets, even sheets that does not contain any Concatall formulas. Can you tell me what exactly triggers the Concatall formula? Is it when it comes across any cells that previously had a Concatall formula or when it comes across cells with "||" or something else?

[Glenn Kennedy]

Can you post a sheet?

[Imbizile]

Attached a testsheet with some stuff from my main sheet.

Code is messy, will clean it up at some point, so please disregard that

But what you will notice, is that when running the sub Rolldata from Main module it will run into the Concatall, when it reaches the line below under Case Is = "Hent data"

Please Login or Register  to view this content.

I have a sheet with some Concatall formulas in, called concattest, but im not touching that sheet at all while running though Rolldata. Deleting the concattest sheet will stop it from running through it though, so im quite confused by what triggers Concatall.

I hope you get what I mean, if not I will post some screenshots to make it more clear