Google Sheets UNIQUE Function Advanced Application Examples 3 - The Solution for Aggregations That QUERY Cannot Handle!

This is the third installment of the UNIQUE function series.

The previous note in the UNIQUE function series ↓

In this final installment of the series, we will cover super-advanced application examples for 'aggregation' using a combination of the UNIQUE function and XXIF (XXIFS) functions, as well as super-advanced application examples using strict row-by-row matching which is a characteristic of the UNIQUE function.

Last time, I took a one-week break from the UNIQUE function to write a note titled 'The {Magic} to Switch COUNTIF, SUMIFS, and FILTER Functions to (Relatively) Strict Matching' which introduced the looseness of matching in Google Sheets and its countermeasures.

Utilizing the UNIQUE function for aggregation

The process of creating unique values with the UNIQUE function can be used when generating pivot-table-like summary tables from data using formulas.

In Excel, the method of combining the UNIQUE function with aggregation functions like SUMIFS is popular, but in fact, even in Google Sheets which has the QUERY function, the method of using the UNIQUE function is useful when performing aggregations that the QUERY function cannot handle.

Let's understand this through a challenge format!

Q1. I want to aggregate the number of appearances (how many times) for each name in column A.

For example, suppose you have simple sales data like Name in column A, Order Date in column B, and Order Quantity in column C.

From this data, 'I want to aggregate the number of visits (repeat count) for each customer (name).' (I want to output a table like columns E:F)

In such a case, what kind of formula should be constructed?

Pivot tables or the QUERY function can be used, but this time

Let's add this constraint.

With this, it becomes difficult to aggregate using pivot tables or the QUERY function, which sort the data during aggregation.

Also, assuming that the header row E1:F1 is already entered,

We will use these as additional conditions.

Please use this data.

氏名	発注日	発注数
澤 孝行	2022/12/15	3
大内 卓也	2022/12/18	6
佐藤 彩子	2022/12/19	10
高杉 恭子	2022/12/22	3
大内 卓也	2022/12/23	2
中濱 賢	2022/12/24	8
佐藤 裕子	2022/12/25	5
安藤 直之	2022/12/27	9
早川 隆則	2022/12/28	10
澤 孝行	2023/1/4	1
高杉 恭子	2023/1/5	6
安宅 佐知子	2023/1/7	1
澤 孝行	2023/1/8	9
高杉 恭子	2023/1/10	8
早川 隆則	2023/1/11	8
安宅 佐知子	2023/1/12	4
佐藤 裕子	2023/1/13	6
早川 隆則	2023/1/14	2
岡田 敦	2023/1/15	7
高杉 恭子	2023/1/19	3
安宅 佐知子	2023/1/22	9
澤 孝行	2023/1/23	7
安宅 佐知子	2023/1/25	1
安藤 直之	2023/1/27	4
佐藤 彩子	2023/1/28	5
佐々木 達也	2023/1/29	7
高杉 恭子	2023/1/30	3
菅原 強	2023/2/1	7
松井 裕美	2023/2/2	3
大内 卓也	2023/2/4	10
安藤 直之	2023/2/6	7
安藤 直之	2023/2/7	5
大内 卓也	2023/2/8	5
佐々木 達也	2023/2/9	10
岡田 敦	2023/2/10	6
中濱 賢	2023/2/11	8
安宅 佐知子	2023/2/13	2
松井 裕美	2023/2/14	2
工藤 真那	2023/2/15	10
中濱 賢	2023/2/16	1
佐々木 達也	2023/2/17	9
岡田 敦	2023/2/19	1
安藤 直之	2023/2/23	8
高杉 恭子	2023/2/24	4
佐々木 達也	2023/2/26	1
大内 卓也	2023/2/28	6
安藤 直之	2023/3/3	10
菅原 強	2023/3/4	4
佐藤 彩子	2023/3/6	9

Just copy and paste it into cell A1.

*Names were created using Personal Information Test Generator.

This is a basic aggregation, but it might be a little difficult.
Let's think about it carefully!

↓↓
The answer starts here.
↓↓

A1. Aggregate the number of occurrences (how many there are) for each name in column A.

The logic will be explained as we provide the answer.

In the past, we had to prepare this part manually. Since it's by name, we need to make column A unique. It's time for the UNIQUE function!

However, if you simply use UNIQUE(A2:A), it will also output blanks, so that won't work.

This was touched upon in the first part of the UNIQUE function series.

To get unique values while excluding blanks, combine it with the TOCOL function, which can easily remove blanks from a single-column data range.

The TOCOL function is a function that converts a range or array into single-column data, but it is useful because you can specify the second argument to remove blanks or errors!

With this formula, we were able to obtain unique name data as shown below.

If there were no restriction to a single formula, we could complete this by entering a formula that combines ARRAYFORMULA and COUNTIF into cell F2, but since we want to complete this with a single formula in E2 this time, let's temporarily assign the result of this UNIQUE function to a.

This is where the LET function comes in handy.

Next, we want to count how many times each individual value of a (unique name) appears within the original name range A2:A, so we create a formula like this:

We create a formula like this.

Since the condition a is an array (multiple values), ARRAYFORMULA is required to perform array processing to get the results for each COUNTIF.

Let's assign the result of this formula to b.

We have a, which is the unique names, and b, which is the result of using COUNTIF to get how many of each element of a are in A2:A; we just need to concatenate them horizontally at the end.

{ a , b }

HSTACK function can also be used, but in Google Sheets, concatenation using curly braces is easier.

So, the final formula is

It looks like this.

It might be a little difficult, but if you have the LET function, you can proceed step by step, so compared to the heavily nested formulas from before the LET function, it has become much easier to decipher, hasn't it?

By the way, if you want to sort by frequency in descending order, you can handle it by combining it with the SORT function at the end.

How to use the QUERY function and its weaknesses

As those who use Google Sheets regularly probably know, naturally, for this kind of aggregation, you often use the QUERY function.

Even if you want to sort by frequency, you can finish it with a single QUERY function without needing the SORT function.

Uniquing data with the UNIQUE function ... group by
Aggregating occurrence counts with the COUNTIF function ... count(A)
Sorting by frequency with the SORT function ... order by
Setting header rows ... label

Yeah, that's why the QUERY function is called the strongest lol

However, as I wrote at the beginning, the QUERY function has a specification where if you use group by and do not specify a sort order, it will be sorted in ascending order of the uniqued column.

Furthermore, the sort order of the QUERY function is based on Unicode values, so if the target column contains "Kanji" data, it has the weakness of resulting in a nonsensical sort order.

If you want to "maintain the order of appearance in the original data" (though I think such requirements are rare), it means that in some cases, it is better to combine the UNIQUE function with other aggregation functions rather than using the QUERY function.

Aggregation formulas using Excel's UNIQUE function

More than in Google Sheets, the place where you have more opportunities to use aggregation formulas with the UNIQUE function is Excel.

However, Google Sheets and Excel do not always have the same functions, and even the same functions can behave differently, so you cannot simply use Google Sheets formulas as they are.

For example, if you replace the Google Sheets formula above, which sorts by frequency, with one for Excel:

It looks something like this.

The DROP function is a useful function that hasn't been imported into Google Sheets. I hope it gets added eventually.

Migrating formulas is quite difficult because you need to be aware of points to watch out for and understand the differences in functions and behavior unique to Excel and Google Sheets.

It might be similar to programming conversion lol (though not quite to that extent).

However, the writing style above is how someone who has over-complicated Google Sheets like mir writes formulas.

I think most general Excel users would write it like this:

By turning the source data into a table (named SalesData),

Structured references allow you to get the column range of name data, eliminating the need to remove blanks or account for headers, allowing you to write a clean formula.

Furthermore, if sorting is not required,

F2# By using a spill operator like this, it can handle changes in the number of unique name data dynamically, so you can also write the formulas separately.

If you want to sort at the end, combine it into a single formula using LET and HSTACK like this:

Something like this, perhaps.

As of May 2024, Google Sheets does not have equivalents to Excel's Table feature or spill operators.

However, it has been announced that the Table feature is scheduled to be introduced to Google Sheets.

On the other hand, while Excel does not have a function equivalent to the Google Sheets QUERY function, there are plans to add GROUPBY functions, PIVOTBY functions—powerful aggregation functions—in the future. (Early users are already using them.)

Both are exciting!

Q2. I want to pivot-aggregate the number of orders per person and per year using the names in column A, dates in column B, and order quantities in column C.

Now, for a slightly more difficult challenge, using the same data as Q1, what formula should be constructed if you want to pivot-aggregate the order quantities by name and the year of the order date, as shown in the image?

Of course, without using the QUERY function, and with the constraint that the ranges must be open-ended (specified as A2:A, B2:B, C2:C).

Also, there is a requirement that you must append the word 'Year' to the years.

It is a bit difficult, or rather, a tedious formula, but those interested should give it a try! (Ideally, this task is better suited for a Pivot Table or the QUERY function.)

↓↓
The answer starts here.
↓↓

A2. A formula to pivot-aggregate the order quantity by person and by year, using the names in column A, dates in column B, and order quantities in column C.

Let's go through the answer while explaining the logic again this time.

First, we will create the vertical and horizontal axes for the aggregation.

The vertical axis is the same as in Q1.

This time, let's set this as 'c' using the LET function since it's a column.

As for the horizontal axis, the year, this might be a bit tricky.

You could use the YEAR function like YEAR(B2:B)&"year", but this time let's use the TEXT function like TEXT(B2:B,"YYYY年").

The ARRAYFORMULA is required for array processing.

However, both the YEAR function and the TEXT function treat blanks as 0 (a serial value of 0 is December 30, 1899, in Google Sheets), so blank cells will return 1899.

To avoid this, it seems better to remove blanks before passing them to the TEXT function, and since we need to arrange them horizontally in the end rather than vertically...

This time, we will use the TOROW function to convert them into blank-removed + horizontal direction, and then by specifying TRUE(1) as the second argument of the UNIQUE function, we will perform horizontal unique processing.

It turned into this formula.

Now that the vertical and horizontal axes are ready, it is finally time for the aggregation part in the middle.

Since the aggregation part is the total number of orders this time, it seems best to use the SUMIFS function.

Instead of thinking in terms of arrays right away, let's try thinking about using the vertical and horizontal axis data that we output once as a range for clarity.

In cell F2, the top-left of the table content, enter:

Just enter a formula like this and fill it to the right and down, and you are done.

To determine if the order year matches, I want to use an array converted to YYYY year using the TEXT function for B2:B, so I am combining it with ARRAYFORMULA.

Writing this SUMIFS in a single formula without filling might actually be the most tricky part, lol.

The SUMIFS function is a function that does not spill with the ARRAYFORMULA function, so processing combined with the MAP function is required here.

It has become quite complicated, hasn't it? lol

I output it once for the sake of the future, but I set vertical (name) as c and horizontal (year) as r using the LET function, and I am making SUMIFS spill using the MAP function,

but the beginning of the MAP function formula is written as

like this.

What am I doing here? I am generating two arrays of the same size as the desired result and passing them to the MAP function.

The first array IF(r<>"",c) is an array where c (name) is repeated by the length (number of columns) of r (year)

The second array IF(c<>"",r) is an array where r (year) is repeated for the height (number of rows) of c (name)

as follows.

From these two arrays of the same size, the MAP function is used to take one value from the same position in each, and use them as cv and rv for the SUMIFS condition, thereby spilling the SUMIFS function.

I have also introduced this method in a past note.

Now that the parts are ready, there is a point to note when connecting at the end.

Whether you use curly brackets, the VSTACK function, or the HSTACK function, you cannot connect arrays vertically and horizontally all at once.

Connect vertically and then horizontally, or connect horizontally and then vertically, you will have to process it in steps like this.

Also, when connecting, basically

is the rule. So the problem becomes

this top-left part.

For example, if you try to connect the name c and the SUMIFS result (let's call it x) horizontally first, and then connect it vertically with r (year)

the year will shift to the left like this, and in the first place, the sizes of the connection surfaces are different, so you cannot connect them with curly brackets.

To resolve this, before vertical connection, you need to connect r horizontally with a blank (empty string) on the left side, and then finally perform the vertical connection. *You could also use "Name"

Putting it all together

=ARRAYFORMULA(
  LET(
    c,UNIQUE(TOCOL(A2:A,1)),
    r,UNIQUE(TEXT(TOROW(B2:B,1),"YYYY年"),1),
    x,MAP(IF(r<>"",c),IF(c<>"",r),
      LAMBDA(cv,rv,SUMIFS(C2:C,A2:A,cv,TEXT(B2:B,"YYYY年"),rv))),
    {{"",r};{c,x}}
  )
)

it becomes a formula like this.

ARRAYFORMULA should be placed on the very outside because you want the array effect to apply to the whole thing.

That is quite a heavy formula...

This can also be done if you use the QUERY function.

In this way, you can write it relatively simply using only the QUERY function.

The mysterious sort orderand the fact that the 0 parts become blankare a bit bothersome, though lol

Q3. I want to expand the ordered products horizontally so that I can see them for each name in column A.

Seeing the aggregation task that the UNIQUE function and other functions worked hard together to solve get one-shotted and defeated in seconds by the QUERY functionthat appeared at the end makes me feel somewhat helpless lol

However, the QUERY function is not omnipotent, so there are aggregations that are better handled by combining the UNIQUE function with other functions.

For example, when you have data like this with a product column added to column C, and you want to output the products (unique data) that each person has ordered in the past horizontallyto the right.

If you are requested to perform such an aggregation that is not really an aggregation, it becomes difficult to handle with the QUERY function.

Basically, the group by and pivot of the QUERY function must be used in combination with aggregation functions (SUM, COUNT, MAX, etc.).

In other words, it cannot be used for irregular aggregationsthat do not involve summarizing numerical values.

To handle this case, the UNIQUE function and its fun group of functionsneed to work together.

氏名	発注日	発注商品	発注数
澤 孝行	2022/12/15	いちご	3
大内 卓也	2022/12/18	りんご	6
佐藤 彩子	2022/12/19	りんご	10
高杉 恭子	2022/12/22	りんご	3
大内 卓也	2022/12/23	ばなな	2
中濱 賢	2022/12/24	りんご	8
佐藤 裕子	2022/12/25	いちご	5
安藤 直之	2022/12/27	ばなな	9
早川 隆則	2022/12/28	ばなな	10
澤 孝行	2023/1/4	ばなな	1
高杉 恭子	2023/1/5	ばなな	6
安宅 佐知子	2023/1/7	いちご	1
澤 孝行	2023/1/8	いちご	9
高杉 恭子	2023/1/10	めろん	8
早川 隆則	2023/1/11	めろん	8
安宅 佐知子	2023/1/12	いちご	4
佐藤 裕子	2023/1/13	めろん	6
早川 隆則	2023/1/14	めろん	2
岡田 敦	2023/1/15	めろん	7
高杉 恭子	2023/1/19	めろん	3
安宅 佐知子	2023/1/22	いちご	9
澤 孝行	2023/1/23	ばなな	7
安宅 佐知子	2023/1/25	りんご	1
安藤 直之	2023/1/27	いちご	4
佐藤 彩子	2023/1/28	めろん	5
佐々木 達也	2023/1/29	めろん	7
高杉 恭子	2023/1/30	りんご	3
菅原 強	2023/2/1	りんご	7
松井 裕美	2023/2/2	りんご	3
大内 卓也	2023/2/4	めろん	10
安藤 直之	2023/2/6	いちご	7
安藤 直之	2023/2/7	ばなな	5
大内 卓也	2023/2/8	ばなな	5
佐々木 達也	2023/2/9	めろん	10
岡田 敦	2023/2/10	いちご	6
中濱 賢	2023/2/11	ばなな	8
安宅 佐知子	2023/2/13	りんご	2
松井 裕美	2023/2/14	りんご	2
工藤 真那	2023/2/15	めろん	10
中濱 賢	2023/2/16	いちご	1
佐々木 達也	2023/2/17	ばなな	9
岡田 敦	2023/2/19	めろん	1
安藤 直之	2023/2/23	りんご	8
高杉 恭子	2023/2/24	いちご	4
佐々木 達也	2023/2/26	めろん	1
大内 卓也	2023/2/28	ばなな	6
安藤 直之	2023/3/3	りんご	10
菅原 強	2023/3/4	ばなな	4
佐藤 彩子	2023/3/6	いちご	9

Now, let's copy the data above and try the challenge!

You only need to enter the formula in one cell, F2.

Let's give it a try!

If you have time, try adding the condition to sort the data so that people with more types of ordered products come to the topand the products are organized as well!

↓↓
The answer starts here.
↓↓

A3. Formula to expand the ordered products horizontally so that they can be seen for each name in column A

Here is the answer.

There are many functions involved, but it is not that difficult.

UNIQUE(TOCOL(A2:A,1)) Just like before, create data that makes the names unique and removes blanks, and set this as c

MAP(c,LAMBDA(cv, Using the MAP function just like in Q2, extract each name from c as cv

FILTER(C2:C,A2:A=cv) Get the data in column C where column A matches cv using the FILTER function

UNIQUE(FILTER(C2:C,A2:A=cv)) Make it a unique value with the UNIQUE function

SORT(UNIQUE(FILTER(C2:C,A2:A=cv))) Align the order with SORT

TOROW(SORT(UNIQUE(FILTER(C2:C,A2:A=cv)))) Finally, use TOROW to make it a single horizontal row

{c,x} and finally, the name and results are horizontally joined to complete it.

There are no tricky techniques, and while it's a bit tedious, I think the formula is relatively straightforward. (It might not be very exciting, though lol)

By changing the final TOROW part to TEXTJOIN, you can also output the results in the column to the right of the name, separated by commas.

I included a bonus challenge asking those who have the capacity to 'sort it so that people with more types of products appear at the top,' but has anyone tried it?

There are several ways to do this, but

BYROW(x,LAMBDA(r,counta(r)))

I think it's relatively easy to get the number of elements per row using the BYROW function like this, and then use that as the key to sort in descending order, with the leftmost column of the products in the second column as the second key.

Alternatively, replacing the BYROW function part with the MMULT function is also a choice for enthusiasts.

Also, there is a method using COUNTUNIQUEIFS, which appeared in the second installment of the UNIQUE function series, but

Using MAP twice makes the formula a bit long.

In any case, for aggregations that output strings rather than sums, counts, or averages, it is not the time for the QUERY function. (It's not impossible if you force it, though.)

After preparing unique condition keys with the UNIQUE function, you process them one by one within the formula using LAMBDA helper functions like MAP or BYROW, then use FILTER, SORT, and UNIQUE again..

For this type of aggregation, it is useful to be able to write a formula like this.

By the way, in Excel, Q2 would spill with SUMIFS, but it cannot take an array for the criteria range, and for Q3, there is a constraint that it cannot return an array within MAP or BYROW, making it more difficult.

That was an advanced application example of the UNIQUE function in aggregation. (I feel like the parts other than the UNIQUE function were the difficult ones, though...)

Utilizing the UNIQUE function for strict row-by-row matching

The UNIQUE function is a rare function that can determine strict matches on a "row-by-row" basis.

Let's introduce an advanced application example using this characteristic.

This is where the third argument of the UNIQUE function, "exactly_once", comes into play.

※ "Exactly once" sounds like a chuunibyou word, which is cool.

How to use the UNIQUE function for row-by-row matching

For example, if you have data A and data B, each consisting of a set of 4 items side-by-side (4 columns by 1 row) as shown in the image, what kind of formula should you write to determine whether these two rows match? (The order must also match.)

=AND(A2=A6,B2=B6,C2=C6,D2=D6)

Of course, that is the way to think about it, but since you are writing the formula one by one, it is time-consuming and lacks versatility.

Here, you can write it like this:

You can write it this way.

However, as mentioned last time, this equal match in Google Sheets is a loose match that does not distinguish between hiragana/katakana or full-width/half-width characters.

To determine this strictly, you can use:

This results in a formula that uses the EXACT function to check the results and finally uses the AND function to determine if everything is TRUE. formula.

However, there is also a solution using the UNIQUE function, which is the theme of this article.

One method uses the UNIQUE function's ability to make duplicate rows unique characteristic.

This formula.

It takes the data (rows) to be compared, stacks them vertically into a single array, puts it into the UNIQUE function, and uses the ROWS function to get the row count.

It takes advantage of returning results like this.

Another method using the UNIQUE function is

using the 3rd argument TRUE (1) method.

This also stacks the data (rows) to be compared vertically into a single array and gets the row count with ROWS after putting it into the UNIQUE function, but by having the UNIQUE function return only rows that appear exactly once (no duplicates),

it determines.

The first method seems simpler, but actually, the second method is more versatile.

Let's think about this in a quiz format by changing the case.

Q4. I want to determine if a matching row exists within multiple rows of data

Now, does a row that matches a single row A exist in a range of multiple rows B? Let's think of a formula to determine this.

If you think about it straightforwardly

A formula that uses BYROW to compare matches row by row with AND conditions, and finally uses the OR function to group them if at least one TRUE exists, or

While not very versatile, you could consider a formula like this that writes out COUNTIFS conditions column by column.

If you encounter false positives where "hiragana/katakana" or "half-width/full-width" characters are treated as identical, you can use the {magic} (array conversion) introduced last week.

This time, the challenge is: Let's try doing this with the UNIQUE function!

Note that we are assuming a match including the order, rather than just a match of the combination.

Here is the data.

データA
りんご	ばなな	いちご	めろん

データB
すいか	ばなな	なし	ぶどう
みかん	いちご	めろん	なし
りんご	ばなな	いちご	めろん
いちご	めろん	ばなな	りんご

Let's think about it!

↓↓
The answer starts here.
↓↓

A4. UNIQUE function formula to determine if a matching row exists within multiple rows of data

Here is the answer. There are two main ways.

The first is a formula that uses the UNIQUE function in the standard way.

Since you also need to consider duplicates within range B, the formula looks like this.

This works by using the fact that if there is duplicate data, UNIQUE({A6:D9;A2:D2})) and UNIQUE(A6:D9) will have the same number of rows, but

if there is no duplicate data, UNIQUE({A6:D9;A2:D2})) will have one more row of data than A2:D2 does.

The other way is to use the third argument of the UNIQUE function to extract data without duplicates.

You can write it like this.

The key point is

{A2:D2;A6:D9;A6:D9}

this part. By connecting the range B (A6:D9) vertically twice, the data in B will always have at least two rows of the same data.

If you set the third argument of the UNIQUE function to TRUE, everything with duplicates will be cleared out, but if you also concatenate A2:D2 here,

This

technique serves as the foundation for solving the subsequent advanced problems.

And another technique to utilize is the one that appeared in the first UNIQUE function example, a formula to extract only the duplicate rows (data) within a range

is this one.

Now, let's move on to further advanced application examples using these!

Q5. I want to compare two lists and extract data from List A that overlaps (or does not overlap) with List B

If you have List A (A2:B) and List B (E2:F) as shown in the image above, I want to extract data from List A that overlaps (matches) with List B.

Also, conversely, I want to extract data from List A that does not overlap (match) with List B.

In such a case, what kind of formula should be constructed?

Finally, we are dealing with many-to-many comparisons at the row level.

However, since both lists may increase in rows, let's assume we use range specifications that do not designate the final row (A2:B, E2:F).

Furthermore, as a condition, since the data consists of English letters as shown above, strict matching judgment is required to distinguish between uppercase and lowercase letters. (banana and BANANA are not considered a match).

This is the challenge (advanced application example).

The data is here.

list A	
BANANA	OKINAWA
melon	HOKKAIDO
melon	KUMAMOTO
orange	WAKAYAMA
apple	NAGANO
Apple	AOMORI
melon	IBARAKI
banana	OKINAWA
banana	OKINAWA
りんご	青森産
melon	IBARAKI
banana	KAGOSHIMA
Apple	NAGANO
BANANA	OKINAWA
orange	WAKAYAMA
MELON	IBARAKI
apple	AOMORI
orange	EHIME
orange	WAKAYAMA
orange	EHIME

list B	
banana	OKINAWA
melon	HOKKAIDO
MELON	KUMAMOTO
melon	EHIME
apple	NAGANO
apple	AOMORI

Since it is necessary to distinguish between uppercase and lowercase English letters, the {magic} that appeared last week, array creation and COUNTIF cannot be used.

There is a way to work hard with the EXACT function here, but let's think about it in the direction of how to use the UNIQUE function?

↓↓
The answer starts here.
↓↓

A5. Formula to compare two lists and extract data from List A that overlaps (or does not overlap) with List B

Here is the answer.

It is easier to understand if you think about non-overlapping data first.

Using the LET function, we set the unique data from List A, created by UNIQUE(A2:B), as a, and set E2:F from List B as b.

Then, by doing UNIQUE({a;b;b},,1)), since b will always appear at least twice, it will be eliminated by specifying 1 (TRUE) as the third argument of the UNIQUE function. However, in that process, if any data in a overlaps with b, it will get caught up and be eliminated.

As a result, you end up extracting the data in a that does not overlap with b, which is data from List A that does not overlap with List B.

On the other hand, if you want to extract data from List A that overlaps with List B,

Use this data in List A that does not overlap with List B UNIQUE({a;b;b},,1)) to do so.

By setting this as x and using UNIQUE({a;x;x},,1),

you can obtain the result.

It is quite complex, so make sure to build the formula yourself and understand it thoroughly.

Q6. I want to use conditional formatting to determine row matches with strict judgment and highlight rows in List A if they match rows in List B.

Now, let's try the next conditional formatting challenge.

Using the same List A and List B as before, and again specifying ranges without a final row,

I want to highlight a row (data) in List A if it matches (overlaps with) any row (data) in List B.

In this case, what custom formula should be used for conditional formatting? (Think about how to use the UNIQUE function.)

Let's give it a try! (You can use the same data from Q5.)

↓↓
The answer starts here.
↓↓

A6. Custom formula for conditional formatting that determines row matches with strict judgment and highlights rows in List A if they match rows in List B

Here is the answer. There are two ways to do this using the UNIQUE function.

The first is a method using the formula

NOT(ROWS(UNIQUE({$A2:$B2;$E$2:$F;$E$2:$F},,1)))

which is based on the formula that appeared in Q4.

With this formula, rows in List A that match (duplicate) List B will be TRUE.

However, since blank rows also become TRUE, I added the condition $A2:$A<>"" and used the AND function to apply conditional formatting by setting rows that satisfy both as TRUE.

The other is a method that uses this formula as a custom formula, which applies the formula that extracts only data that appears two or more times (duplicates) using UNIQUE

This is the formula to use for the custom formula.

It's a full course of the UNIQUE function (Dinner Song), isn't it lol

What we are doing here is that custom formulas in conditional formatting are automatically processed as arrays, so

For example, in the case of processing the 3rd row of List A

With this, we first combine the 3rd row of List A with List B to obtain unique data,

Furthermore, by using this formula to concatenate the unique data from List B with the 3rd row of List A and then obtaining the truly unique data,

We are creating a difference like this.

We could just take the difference in the number of rows between these two, but

In this case, blank rows also become 1 (TRUE), which feels similar to the first answer and lacks finesse.

Therefore, by further concatenating these two and obtaining the true unique values with the UNIQUE function,

By using COUNTA, which does not count blanks, instead of the ROWS function, which leaves only rows that match data in List B and also counts blank rows, we can determine it as 0 (FALSE) in the case of blank rows.

Naturally, since this is a custom formula for conditional formatting, absolute references are also important.

Using strict row-by-row matching with the UNIQUE function in the FILTER function

Since this has become a bit long and the method is almost the same, I will only provide an explanation rather than a challenge, but strict row-by-row matching using the UNIQUE function can be utilized not only in conditional formatting but also in the FILTER function.

For example, if you want to check for duplicates based on 2 columns but extract 3 columns from A2:C, it is impossible with the UNIQUE function alone, so you need to combine it with the FILTER function.

Furthermore, since the UNIQUE function is a function that does not support ARRAYFORMULA, when using it within a FILTER function, you will need to combine it with BYROW to extract List A one row at a time and perform a match check using UNIQUE.

You can handle it with a formula like this.

This was an advanced application example utilizing the strict row-by-row match determination of the UNIQUE function.

The Four Heavenly Kings of the UNIQUE function have finally been cleared... what remains is!

Well, I inserted some related topics along the way, but the 3-part series on the UNIQUE function is now finished.

Having finally cleared the UNIQUE function,

the Four Heavenly Kings of array functions now only have the QUERY function remaining.

As was the case this time, the Demon King QUERY function has shown overwhelming power in the FILTER and SORT function sessions as well.

While I want to cover it soon, I also feel like "With your current power, you cannot defeat the QUERY function!", so I haven't been able to get around to it yet lol

When I get the motivation, I'll do my best to write it!

Next time, I think I'll write a follow-up (supplement) to Filter Views, which have had quite a few changes since I last wrote a note about them.