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

Excel 365 Pro Plus with Power Query.

For all the "Me Too" s

With PQ Append/Merge.

No formulas, no VBA macro.

https://www.mediafire.com/file/q7ax4s10t5egbwy/03_08_21.pdf/file

https://www.mediafire.com/file/rd8ehrjtx5g0edz/03_08_21.xlsx/file

Answer 3

Hi RyanL2019,

Thanks for posting in our forum and sharing the sample file.

Since you want to further develop the function of the IFERROR formula, to better meet the scenario, we may need more time to do the test and investigate on this.

Moreover, we would like to invite all community members to share their valuable suggestions on this scenario. Their suggestions may provide more efficient formulas or ways to realize your demand and help you better since they have a lot of experience in Excel formula.

Thanks for your patience and we will let you know if we have any updates.

Best Regards,

Mia

Answer 4

Hi RyanL2019,

It seems like you have figured out the solution and update it in the original post. Also, you can update the great answer to post a reply and I will mark your reply as an answer so that more community members here will be more easily to have a reference or attempt with your idea if they face with the similar situation as you provided.

We highly appreciate your effort on this scenario and you as well as other members are always welcome to share more ideas with formulas in our forum.

Hope you have a nice day and stay safe.

Best Regards,

Mia