compare 2 lists and extract the differencies without array formula / helper column

[Villalobos]

Hello,

I would like to ask some regarding extraction. I have 2 lists and my target is to compare and extract only the differencies but this should be done without array formula / helper column.

Please, see the expected result and my array solution in the attached sample file.

Thank you for any help you can offer!

[CK76]

You could use Get&Transform/PQ.

Load both ranges to query, filter out blanks.

Then on List1, use full outer join of List2.

Then do Text Filters->Equals->Advanced. And filter for List1 = null "OR" List2 = null.
This will generate following.
0.JPG

If you are only interested in what is missing in List2. Then you can use Left Outer Join instead of Full Outer Join.

[Haluk]

Without an array formula, you can create a list but there will be empty cells within...

You can insert the following formula in cell C5 and drag down to the last row of the lists.

Replace ";" with "," depending on your local settings of Excel.

Please Login or Register  to view this content.

[FlameRetired]

Villalobos when you say without array formula are you simply wanting to avoid Ctrl + Shift + Enter?

If so this does not require CSE, but it is still technically an array formula.

As Haluk has stated you may need to change ";" for "," in the argument separators. In B33 and filled down.

Please Login or Register  to view this content.

Does this do what you want?

[Villalobos]

@FlameRetired,
Yes, I wanted to avoid the Ctrl + Shift + Enter. Thank you very much for this solution and for the time that you spent!

Just one more question.
I thought that no array formula without CSE, may I ask you to explain the relationship between an array formula and CSE?

[FlameRetired]

Villalobos I have attempted to articulate this for myself let alone others over the years. I haven't done well.

There is apparent confusion on the topic and what is deemed an array formula.
The terms CSE and array formula are often used synonymously. Though powerful and fast array formulas are resource heavy. Too many of them can slow workbook performance. It may be the unstated reason for requesting a non array formula. That's why I often ask.

There really isn't a difference in how the two operate. Excel is written to recognize CSE as an array formula. The questions are: "Does the formula perform multiple calculations upon whole ranges or other calculated arrays?" or "Does it produce multiple returns?" If it does it's an array formula.

If you are not familiar with it there is a feature in Excel call 'Evaluate Formula'. It is in the Formulas ribbon. Clicking your way through a formula step by step often answers those questions.

You didn't ask, but some commonly used tools to avoid CSE are SUMPRODUCT, AGGREGATE, MMULT and wrapping nested calculations in INDEX. There are other constructions. (Exploration and experimentation are my best friends. LOL )

This is not likely a very satisfying answer, but I hope it helps.

[Villalobos]

Thank you for your explanation!

[FlameRetired]

You are welcome. Glad to help.

Thank you for the feedback, added rep and marking your thread Solved.