Compare Ranges to Another Range, Extract Unique Numbers

Question

I've got formulas to compare two lists/columns and extract unique numbers, but what would the formula be when it's comparing multiple ranges to another range and then extracting the unique numbers (not unique to both sets but numbers found in one group that are not found in the other, and vice versa)?

Needs to be without the functions UNIQUE and FILTER nor with a pivot table.

Example file provided for you here.

Thank you in advance.

EDIT:

I'm not able to get the right-side "Result" to work with different sheets, but it kind of does within the same sheet. Numbers outside the boxes but still within the range $B$3:$L$18 still show up in the results. It also appears to work best on 365 but requires CSE apart from that.

So far for the left "Result" column:

=IFERROR(LARGE(IF(COUNTIF($R$4:R4,$N$3:$O$16)+NOT(ISNUMBER($N$3:$O$16))+(COUNTIF($B$3:$D$18,$N$3:$O$16)+(COUNTIF($F$3:$G$8,$N$3:$O$16)+(COUNTIF($I$5:$L$14,$N$3:$O$16))))=0,$N$3:$O$16,""),1),"")

For the right "Result" column:

=IFERROR(LARGE(IF(COUNTIF($T$4:T4,$B$3:$D$18:$F$3:$G$8:$I$5:$L$14)+NOT(ISNUMBER($B$3:$D$18:$F$3:$G$8:$I$5:$L$14))+(COUNTIF($N$3:$O$16,$B$3:$D$18:$F$3:$G$8:$I$5:$L$14))=0,$B$3:$D$18:$F$3:$G$8:$I$5:$L$14,""),1),"")

For INDEX/MATCH, this compares the left side of the "list" to the 3 sets and extracts the numbers in the list that aren't found in the 3 sets:

=IFERROR(INDEX($N$3:$N$16,MATCH(0,INDEX(COUNTIF(R$4:R4,$N$3:$N$16)+NOT(ISNUMBER($N$3:$N$16))+(COUNTIF($B$3:$D$18,$N$3:$N$16)+(COUNTIF($F$3:$G$8,$N$3:$N$16)+(COUNTIF($I$5:$L$14,$N$3:$N$16)>=1))),), 0)),"")

(For the right, one could just change $N$3:$N$16 to $O$3:$O$16.)

Answer 1

Hi @RyanL2019

The other is different, being just about the possibility of a continuous range that includes a reference to another sheet (without it turning into a '3-D Reference'). That one has been answered with 'not possible'. This thread is about extracting, via formula, unique numbers from multiple ranges that are not found in another range (not just a column/list), and vice versa

Actually the problematic is quite similar in the sense that you cannot make a UNION of several ranges (Set A - Set C in this thread) via formula - Recently discussed in this thread that offers a way to do it but there's a limitation

Below pics. truncated

• Works with Errors and/or zero(s) in the ranges
• For readibility, named the various ranges SetA, SetB, SetC & List
• CheatSheet (can be hidden) with tblNumbers aggregates the unique numbers in SetA-SetC & List
• A few funky formulas in Name Manager to avoid the former IFERROR(...., "") that hide unecessary array calcs
• In Result sheet, a control (+6 in above pic.) that indicates if the # rows provisionned in tblNumbers is OK - cell font turns red otherwise

EDIT A +/- little optimization is still possible in tblNumbers...

Corresponding workbook avail. here - Inc. a Power Query option based on the same named ranges (SetA-SetC & List):

• Enter dummy data in SetA-SetC and List
• Right-click on each blue Table > Refresh

Efficient with large ranges and easy to customize to accept additional named ranges if necessary

Answer 2

Hi RyanL2019,

You can check the sample file provided by Herbert using Power Query which is applicable for a scenario with comparing ranges in Excel. For your reference: Introduction to Microsoft Power Query for Excel - Excel

After opening the file https://www.mediafire.com/file/rd8ehrjtx5g0edz/03_08_21.xlsx/file, click the value range and you will able to see the Query > Edit button on the top launch bar.

Here is a similar thread in Power Query Forum and hope it could offer a little help. Solved: Compare a number with a range of numbers(from to) ... - Microsoft Power BI Community

Best Regards,

Mia

Answer 3

Hi @RyanL2019

To help others who search this site for existing solutions it would be smart of you to mention which of the 2 options (XL formulas vs. Power Query) solves the issue - Thanks for providing feedback anyway

If you opted for XL formulas, as mentioned in the (late) EDIT, a few optimizations are possible if efficiency matters

Answer 4

Thank you, Lz. I've got to sit down and try to wrap my head around your response/solution. I've had some deadlines this week and haven't been able to make the time. This weekend should be better for me.