Display and Count values, in an array, that do not match values another array

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

@CARACALLA - I thought it was there, not sure what I did. Fixed. Thanks!

@sandy666: rather a formula. Thanks.

Originally Posted by guillaume0314

@sandy666: rather a formula. Thanks.

no problem
have a nice day

btw. this is not VBA

I would like to simplify even more here, to see if there is an acceptable solution doable for me. Version 3.

Lets imagine the data is not represented any longer with comma-separated strings but available directly in 3 columns. I can force the system to populate the data in different columns, instead of returning a comma-separated values list in a single column. This is possible, although not the best, but I can do it.

Then I imagine is much simpler to run a control on each of the 3 columns, extract the unmatched valued and count them. What do you think? If yes what would be the steps? Thanks!

Attachment 802283

Thanks Glenn.

That was my intention. However - if the rendered format is an issue then we can amend it - as long as I can see visually the missing term and how many times it is missing - it does not matter how the result is formatted.

That could possibly be for example this very basic sequence:

term1 count1 term2 count2 etc.

or

count1 term1 count2 term2 etc.

or even, if it is much easier for the formula:

totalcounts term1 term2 etc.

Thanks.

Thanks Ali! It looks like it works this way and I do not have to go through 3 columns like suggested in V3.

I will look into the details later today and make some tests - cannot look into the details right now.

Thanks Glenn it looks like it works. I will make some tests later today.

You guys @Glenn and @Ali are fantastic, your help is so much appreciated! And very fast answer.

@Glenn - it looks like the formula given by Ali gives the desired result too.

Thanks.

Thanks so much @Ali and @Glenn - it works like a charm.

I will stick with Ali's one as suggested.

Dear Ali:

I have been using this formula for different kind of arrays without troubles:

Originally Posted by AliGW

How about this?

=LET(t,TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,B6:B22),",")),f,FILTER(t,(ISNUMBER(MATCH(t,G6:G13,0)))=FALSE),uf,UNIQUE(f),ci,COUNTIF(B6:B22,"*"&uf&"*"),tj,TEXTJOIN(" ",,CHOOSE({1,2},uf,"("&ci&"),")),LEFT(tj,LEN(tj)-1))

However I have just discovered a limitation in one of my table. Actually it looks like if the array is large enough (e.g. B6:B1000 instead of B6:B22 in the initial example), and if each cell of this array contains lengthy data, then the formula will return a #CALC error.

Please see:

Attachment 804124

It is my understanding this function

Formula:

Please Login or Register  to view this content.

would not work if it exceeds 32,767 characters (https://support.microsoft.com/en-us/...0exceeds,error.)

In the exemple attached, I see the total characters length of B6:B1000 is 48,079.

If I change B6:B1000 to B6:B500, then suddenly the TEXTJOIN works again. So I imagine TEXTJOIN is the culprit here.

Any ideas how to bypass this new issue? Thank you so much.

Dear Ali:

Your sample data was clearly so far away from what you are really working with that this has been a complete waste of time.

I am sorry, but what do you not understand in: "I have been using this formula for different kind of arrays without troubles"?

It means it was a good fit and very useful for the arrays I had to deal with on 10/25, correct?

Would this prevent me from having a NEW issue on 11/08 with a NEW kind of data which require a NEW formula, as the previous one is not working any longer? Certainly not.

Do you seriously believe one has to imagine all kind of difficulties that may happen in the future, just in case, before taking any action for the current problem, considering the current configuration and scenario? Should I discover it does not work with case-sensitive data later on, for example, I would not be authorized to ask, because it would risk you feel you wasted your time? Seriously? And I shall have imagined a potential case-sensitivity issue at the very beginning instead, although not needed at that time? Or I shall have imagined empty cells, which were not existing in the original data set? Or I shall have imagined any potential issues, just in case, not to upset you? As I read you, I understand it has to be a "for once and for all" scenario when you deliver a solution, which is, to me, completely out of reality. I do not know any problem-solver with such an approach. New solutions lead to new problems, and then new solutions, and etc.

Please be reassured - it was NOT a complete waste of time, it works for a dozen of tables - thanks again!

However, it was a big mistake on my side to imagine it was a good idea to ask you directly what do you think of this discovered and unexpected limitation. The fact is - my intention was very respectful to you! I could have ignored you completely and submit a new thread instead.

Quite a bad idea as it led to

I'm not going to do it again as it's a lot of effort only to be told the next day that it doesn't cope with something I knew nothing about.

.

I always appreciate your help, really. However, I deal with highly dynamical data from various sources, in terms of size, nature, complexity, multiple kind of changes happen very often and lead to new challenges. We are certainly not a good match considering this, and I do not want to be a pain. I am sorry for the inconvenience caused.

Thanks Glenn for the suggestion. I will look into it immediately.

@Glenn - I had a look into the details - it will be difficult IMO to insert helpers columns the way our workbooks are setup, there are already 100+ columns in the file I am working in and this functionality (display and count missing values in a column) has to be implemented on many columns to control their content, not a single one, which would require to remultiply the number of column helpers by the number of columns every time this formula is used: looks very complex to do.

Additionally, I cannot be certain how many terms could be comma-separated in each cell of the analyzed column, as a result the number of helpers, should we go this route, would have to become dynamic as well as the TOCOL array, or at least I would need to consider and use a large max (like 50), which would create many additional columns.

Thank you @Hans. It works well! The problem, each time we modify a single cell in the original range, it recalculates everything and takes about 20 seconds.

New data will append to the original range on a regular basis, so unfortunately it will not be very comfortable to use on a daily basis.

Additionally in terms of performance, as this formula would be used on multiple columns, controlling the values inside the cells, I cannot exclude a problem in terms of performance/reliability if everything is recalculating at the same time.

Thanks you so much for your input!

Thank you Hans.

It is not necessarily colors, it can be any list of values I need to control, against authorized values.

It were colors at the beginning and for the example, but I do not have many colors merged in single cells in my system so it worked perfectly fine, and fast.

Please see attached a copy of data. I have been stuck suddenly with this set of data, considering the large data and the limitation on TEXTJOIN - at least, this is my understanding.

You will notice I cannot extend the array to more than B400: it will falsely returns a validation, which is a no-go.

--

How many rows of colors do you usually have in your sheet?

It depends on workbooks. Usually 1k to 5k rows per sheets where this formula could be of interest. I think, no more than 5k. At least, did not happen yet.

How many colors are there on average in 1 cell?

Typically I deal with max 3, 4 elements per cells, comma-separated. Was the case for colors. But in the current case, the max is 13. I made a quick check on other arrays susceptible to use this function, currently none has more than 13 values that could be joint together. So, average 3-4, max 13.

And are there in practice many items (over 1000) that don't match?

This is very rarely the case - most of the time, data is valid, so it does not have to display anything except the submitted range is valid. I would say there could be 1 or 2 mismatches maximum (to display - not talking about the count). If we talk about the count, then it could rapidly be huge, if a wrong value is used in many rows (500+)


Also - I have a question.

1/ Would that make sense to set limitations on the counts (example: MISMATCHES: blue (10+) to say there are more than 10, we do not count over). Would this be a solution?

2/ Would it be much easier if we do NOT have to display the count? Because I imagine you could use at the very beginning of your LET the UNIQUE function to remove all kind of duplicates, which would drastically reduce the amount of data to deal with. Does this make sense?

If yes - I think it will be very acceptable to return the missing values instead of the initial request missing values + count. What do you think of this?

Thank you!

It works fine and it is extremely light and fast which is a crucial parameter to me- I will go this route and sacrify the counts at this stage.

Thank you so much for your continued support and for listening to me while I try to fix continuously multiple issues in Excel.