Compare 2 lists — Legacy Excel classic formulas

by | Jul 19, 2023 | Excel

Today, we want to com­pare two lists and find ele­ments that are on both lists as well as on only one. 

Com­pare 2 lists clas­sic for­mu­las Lega­cy Excel

We are going to use Clas­sic Excel, Lega­cy Excel and a clas­sic for­mu­la with the AGGREGATE func­tion. It means that this for­mu­la will work from 2010 and thanks to this, we won’t need to use the Crtl + Shift + Enter key combination.

Let’s start with find­ing ele­ments. We will be check­ing whether ele­ments from My son’s favorite movies list are on My daugh­ter’s favorite movies list. We can use the MATCH func­tion here. We are look­ing up ele­ments from my son’s list (list B) on my daugh­ter’s list (list A). We’re click­ing F4 key to lock both lists. Then we write zero to have the exact match and that’s it (Fig. 1)

=MATCH($C$2:$C$9,$A$2:$A$11,0)

MATCH function
Fig. 1 MATCH function

Excel has just giv­en us the results. I have the Dynam­ic Array Excel, which means that Excel spilled the results. We can see that How to train a Drag­on is in list B with num­ber 5 which means that it’s on posi­tion num­ber 5 in list A. We also have Spi­der-man on posi­tion num­ber 7. If we want to extract those num­bers, we have to use the AGGREGATE func­tion. In the first argu­ment of the func­tion, we have to use a func­tion num­ber 15, as this func­tion under­stands an array for­mu­la. Then, in the sec­ond argu­ment, we have to write 6 to ignore errors. At the end, in the k argu­ment let’s write ROWS from E2 to E2 and press F4 key to lock only the first one. This will allow the for­mu­la to expand while copy­ing down (Fig. 2)

=AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

AGGREGATE function
Fig. 2 AGGREGATE function

Now, we have the results, how­ev­er there are some errors. To remove that, we can add the INDEX func­tion and write list A in the first argu­ment, then press F4 key to lock it (Fig. 3)

=INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

INDEX function
Fig. 3 INDEX function

Now, we have the ele­ments that are on both lists. We can remove the errors. Let’s add the IFERROR func­tion (Fig. 4)

=IFERROR(INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))),“”)

IFERROR function
Fig. 4 IFERROR function

We have our results in the tar­get list, and there is still some back­up space in case we have some more results (Fig. 5)

Results with extra space
Fig. 5 Results with extra space

Now, we can cre­ate a for­mu­la that will look up ele­ments only from list B. Let’s start with giv­ing each ele­ment a num­ber. We’ll use the ROW func­tion here and write the whole list as the first argu­ment. Then F4 key to lock it (Fig. 6)

=ROW($C$2:$C$9)

ROW function
Fig. 6 ROW function

As we can see, Excel has spilled the results and each ele­ment has got its own num­ber. How­ev­er, we want to start from 1, not from 2 (Fig. 7)

Numbers for each element
Fig. 7 Num­bers for each element

To do this, we have to sub­tract the head­er row and press F4 key to lock it (Fig. 8)

=ROW($C$2:$C$9)-ROW($C$1)

Subtracting the header row
Fig. 8 Sub­tract­ing the head­er row

And we have all ele­ments num­bered from 1 to 8. Now, we want only num­bers of ele­ments that aren’t on list A. How can we do it? We can divide our list by the MATCH func­tion. In the first argu­ment, we write list B and press F4 key to lock it, then list A in the sec­ond argu­ment, as that’s the place where we will be look­ing up our val­ues. Then F4 key. As the final step, we write 0, as we want to have the exact match and the look­ing up process will start from the top (Fig. 9)

=(ROW($C$2:$C$9)-ROW($C$1))/MATCH($C$2:$C$9,$A$2:$A$11,0)

Dividing the list
Fig. 9 Divid­ing the list

Just like that we have num­bers only on those posi­tions that are on both lists. How­ev­er, we want a reverse sit­u­a­tion, where we will have num­bers in #N/A posi­tions. To to that we have to put our MATCH func­tion into the ISNA func­tion (Fig. 10)

=(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0))

ISNA function
Fig. 10 ISNA function

If the MATCH func­tion returns an error, the ISNA func­tion will change this error into TRUE. And a num­ber divid­ed by the val­ue of TRUE will change the TRUE val­ue into 1. If the ISNA func­tion returns FALSE, the math­e­mat­i­cal oper­a­tion will change FALSE into 0. Since we can­not divide by 0, we will have #DIV/0! errors (Fig. 11)

#DIV/0! errors
Fig. 11 #DIV/0! errors

Since we have a list with num­bers which are the posi­tions of ele­ments we want to extract and errors, it’s time to put our whole for­mu­la into the AGGREGATE func­tion. Just like before, we start from the small­est val­ue, so let’s write 15, then 6, as we want to ignore errors. At the end of our func­tion, we write the ROWS func­tion with G2 cell. Then F4 key to lock the first one (Fig. 12)

=AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2))

AGGREGATE function
Fig. 12 AGGREGATE function

Now, we have posi­tions with­out errors, which means that we can add the INDEX func­tion to our for­mu­la. As we take posi­tions from list B, we have to select the list, then press F4 key to lock it (Fig. 13)

INDEX function
Fig. 13 INDEX function

After press­ing Enter, or Ctrl + Enter, we have our results. Now, we can add the IFERROR func­tion to put emp­ty text strings in the places where the for­mu­la returns errors (Fig. 14)

=IFERROR(AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2)),“”)

IFERROR function
Fig. 14 IFERROR function

And we have our results (Fig. 15)

Results
Fig. 15 Results

This way, we have two for­mu­las. The first one to extract ele­ments that are on both lists, and the sec­ond one to extract ele­ments that are only on one list. 

https://www.youtube.com/watch?v=gb7zo6lPZSs&t

Running Total in Pivot Table

by | Feb 10, 2023 | Excel

Some­times, you need to cre­ate a run­ning total in piv­ot tables. How to do it properly?

Run­ning Total in Piv­ot Table

Let’s start with cre­at­ing a piv­ot table. Select one cell from our data and go to the Insert tab and select the Piv­ot Table com­mand. A Piv­ot table from table or range win­dow will appear, where we have our range. We need to select the Exist­ing work­sheet radio but­ton and write the loca­tion, which, in our case, will be cell F1. Let’s click the OK but­ton (Fig. 1).

Creating a pivot table
Fig. 1 Cre­at­ing a piv­ot table

And there we have it. In our piv­ot table we need to drag the Date to the Rows labels. 

Dragging the date
Fig. 2 Drag­ging the date

Now, depend­ing on the Excel ver­sion, we have an option of Group by dates. I’m going to group our date by months and years (Fig. 3).

Grouping
Fig. 3 Grouping 

We have our table. Now, I want to add Income so let’s press the Income check­box (Fig. 4). 

Income checkbox
Fig. 4 Income checkbox

Now, I want to have the income and the run­ning total for the income, so let’s drag the Income two times to the Val­ues area. We can see that we have Sum of Income and Sum of Income 2, where we actu­al­ly want to have our run­ning total (Fig. 5). 

Income and Income 2
Fig. 5 Income and Income 2

Let’s right click any val­ue from Income 2, then select the Show Val­ues as and then the Run­ning Total In option (Fig. 6). 

Selecting the Running Total option
Fig. 6 Select­ing the Run­ning Total option

Now, we have to decide whether we want to base our run­ning total on Date or Years Field. Let’s take Date (Fig. 7).

Date Field
Fig. 7 Date Field

Now, we can see that in the Income 2 col­umn we have big­ger num­bers. Let’s for­mat them by press­ing any cell in the col­umn and choos­ing the Num­ber For­mat option (Fig. 8). 

Number Format option
Fig. 8 Num­ber For­mat option

In the For­mat Cell win­dow, let’s choose the Cur­ren­cy cat­e­go­ry, in Dec­i­mal places let’s write 0 and press the OK but­ton. Let’s do the same in the Sum of Income col­umn (Fig. 9). 

Format cell window
Fig. 9 For­mat cell window

Now, we can see that we are work­ing with mon­ey. In each month we have big­ger and big­ger num­bers which means that we have the run­ning total in this col­umn. We can change the name of Sum of Income 2 into Run­ning Total. Since we are using the Date Field as our ref­er­ence point, we have the run­ning total in 2020. In 2021 Excel counts from the start, which means that we have the run­ning total only for 2021. The same is with 2022. If we change the Date field into the Years field in the Show Val­ues as (Run­ning Total) win­dow, we will have the same val­ue in the first year and in the Sum of Income col­umn (Fig. 10).

Years
Fig. 10 Years

How­ev­er, in the next year, we have the the sum from Jan­u­ary 2021 and Jan­u­ary 2020. In Feb­ru­ary, we have the sum from Feb­ru­ary 2021 and 2020. In 2022, we have sums from three Feb­ru­ar­ies (Fig. 11).

Sums from three Ferbuaries
Fig. 11 Sums from three Ferbuaries

Let’s drag Man­ag­er into the columns head­er (Fig. 12).

Dragging Manager into headers
Fig. 12 Drag­ging Man­ag­er into headers

By doing so, we can show val­ue from the Man­ager’s per­spec­tive, which means a hor­i­zon­tal view. 

Horizontal view
Fig. 13 Hor­i­zon­tal view

How­ev­er, this type of data isn’t a prop­er one to show it this way, so let’s get back by press­ing Ctrl + Z two times and see that we can also cre­ate a per­cent run­ning total by going to the % Run­ning Total In option and choose the Date Field (Fig. 14). 

Percentage Running Total
Fig. 14 Per­cent­age Run­ning Total

Now, we can see the val­ues as per­cent­age (Fig. 15). 

Results
Fig. 15 Results

https://www.youtube.com/watch?v=ikou6toX11g

ROWS function, how many rows in a range

by | Nov 1, 2022 | Excel in a minute

When you want to count the num­bers of rows in a range, you can use the ROWS func­tion. This func­tion looks only at rows in a giv­en range, and in the end, it returns the num­bers of rows from the select­ed range. 

ROWS func­tion, how many rows in a range

=ROWS(B11:C16)

ROWS function for a given range
Fig. 1 ROWS func­tion for a giv­en range

The ROWS func­tion allows you also to cre­ate a sequence of num­bers. We just have to select a prop­er range (from cell C11 to C11) (Fig. 2), but we have to lock the first C11 cell with a $ sign.

=ROWS($C$11:C11)

Locking the cell
Fig. 2 Lock­ing the cell

Then, we copy the for­mu­la down. We can see that our range keeps expand­ing because the first cell stayed the same, and the sec­ond cell keeps mov­ing down (Fig. 3)

Expanding range
Fig. 3 Expand­ing range

https://www.youtube.com/shorts/_VNXxiwFqbE