[MUSIC PLAYING]
Welcome to MSPTDA, number 27.
Yes, Microsoft Power Tools for Data Analysis.
Hey, in this video we gotta talk about a many
to many relationship, and guess what.
We're not going to use Power BI or DAX or Power Query.
We're going to see how to do it with an Excel array formula,
and we'll get to see the functions SUMPRODUCT
and SUMIFs.
Now next video, number 28, we'll see
how to build a bridge table if you're creating a BI solution.
And we'll see an amazing couple of tricks in Power Query
to make building this bridge table for a many to many
relationship easy.
But in this video we gotta check out this clever array formula
that requires no data modeling.
Now this is the Excel workbook you can download.
We're on the worksheet Array Formula Solution.
Now here's the problem.
This is our fact table, and we have a book ID.
So this report is not hard to create.
We can do a basic formula that looks up the correct book ID
and then does SUMIFs on this.
But what are we going to do here?
We want to also summarize by author.
Now here's the dimension table or lookup table for author.
But over here the problem is some
of the books have multiple authors, whereas the data
analysis book is authored by Bill Power Query Poet
Szysz and Excelisfun Girvin.
Good Data is authored by Mr. Excel and the Power Query Poet
Bill Szysz.
Now this looks like a one to many relationship
on the book side.
This is a unique list.
So we have the one side hooked up with more than one author.
So this direction, it's one to many.
But think about the other direction.
Right here, Mr. Excel.
This is the one side because we have
a unique list of author ID.
But Mr. Excel is listed many times over here.
So when we go this direction, it's
a one to many relationship.
Now anytime you have that we call this a many
to many relationship.
Now worksheet formula solutions don't
care about what type of relationship it is.
We just build our formulas based on whatever cells, whatever
tables, whatever data we want.
Now, two caveats about the formulas we're going to do.
The Power Query Power BI data modeling solution
is going to be easier.
But for those of you that hang out at the Excel Is Fun channel
and like to see things done with worksheet formulas--
Power Query, DAX, and Power BI--
this is going to be a cool formula.
Also, I'm not going to convert these
to Excel tables to make it simple on creating
the formulas.
But if you do run this as a solution,
you probably want to convert these to Excel tables,
because then when you add new records,
everything will update.
Now, we'll start with our total units by book title.
And the trick is here's our fact table.
We have book ID and units sold, but wait a second.
The condition or criteria flowing in is not book ID,
so we're first going to have to look up each book
title in this range right here and look up the book ID.
The perfect combination of functions for that
is, first, the INDEX function.
The array-- those are the items were we're looking up--
I'm going to hit the F4 key to lock it.
Comma.
Now we just need a row number-- a relative position.
The perfect function to look up the relative position, or row
number, is the MATCH function.
That's our lookup value as a relative cell reference, comma,
the lookup array argument.
We're trying to find the relative position in that range
right there.
F4 to lock it.
Comma.
We're definitely doing exact match,
trying to find exactly those characters in that range,
so we put a 0.
Close parentheses on match.
Now, notice match is sitting right in row number,
but it will deliver, in this case, the fourth position.
When it gets down to Control-Shift-Enter,
it'll deliver the third position.
That's perfect for row number inside of INDEX,
and then of course INDEX will get the value from that range.
Close parentheses.
Control-Enter to put the formula in the cell and keep the cell
selected.
And then I'm going to click and drag to copy the formula down,
click in the last cell, F2.
I need to verify that all the cell references and ranges are
correct, and they are.
Enter.
Now in the top cell I hit F2.
I need to look through this entire column
right here for ME60--
I see there's one there and there--
and then pick out the numbers and add.
Well, that's the perfect job for SUMIFs.
The sum range, well, I'm trying to add from this column right
here.
F4 to lock it.
Comma.
Criteria range-- well, here's all the book IDs
associated with those units.
F4 to lock it.
Comma.
And there's the condition or criteria
that will be used to find a match here,
pick out the numbers, and then SUMIFs.
Close parentheses.
Control-Enter will get the total.
If we click-- hold Control and click--
we can see down here, it's telling us
that those highlighted cells equal 72.
So it looks like it's working.
Double-click and send it down.
Go to the last cell, F2, to verify.
Everything is looking good.
Enter-Alt-Equals to invoke the SUM function.
The dancing answer, dancing around the correct range,
so Enter.
And there's our, first report.
How in the world, first, are we going
to deal with book ID and then, second, what in the world?
We have two columns.
Well, guess what?
In order for this formula to get the correct book ID--
well, for Mr. Excel, 1, 2, 3, 4.
It looks like four possible books.
So in this cell, I need to find these four books over
in the fSales and get the units.
And the way the logic is going to work since this
is a single cell here is, for that book ID,
I need to ask the question, hey, are you equal to GD14 or CSE1
or this one or this one.
So the first part of this is an OR logical test.
Now before I do this, I am going to have
to look up the author ID and then match it
with these two columns.
Now I could just mash it all together in one cell here,
but I'm going to do a little trick here.
I've hidden a column.
So there's E to G. I'm going to highlight it,
and then, with that cursor, right-click, unhide.
I first want to, based on that value, look up the author ID.
Then in this formula we'll refer to it,
because we're going to have to refer to it multiple times.
Same thing here.
I'm going to use INDEX and MATCH because I'm looking up
something in a range, and I need to find something to the left.
Well, some of you will say, well,
there's VLOOKUP with an array calculation with CHOOSE
and stuff like that.
But the most efficient formula I know is just to do INDEX.
These are the values we're trying to get.
F4, comma.
Row number, match.
We're looking up, and I can't click there,
so I'm going to click in author and down arrow.
That's a relative cell reference-- comma--
within that range right there.
F4, comma, 0, close parentheses, close parentheses,
Control-Enter.
Click and drag.
Last cell, F2.
All right, so we have our author IDs.
Now we can build our OR logical test to deal with two columns
to pick out the right book ID.
Equals, open parentheses.
I'm going to highlight the first column.
F4, are you equal to relative cell reference that author ID.
Close parentheses.
And when we're doing an OR logical test,
the math operator is plus.
If we're doing an AND logical test
the math operator is multiplying.
Open parentheses.
The second column, F4, to lock it.
Are you equal to the author ID?
Now the beauty of this construction right here is--
notice--
they're both going to be on the same row,
allowing us to pick out the book ID.
So I can ask the question, are you equal to Bill Jelen?
False.
Are you equal to Bill Jelen?
False.
But here I'll get a true, but in here I'll get a false.
And we're adding because our resultant array--
if there's a 0 here and a 1 here,
we want the resultant array to be equal to 1.
Now watch this.
I'm going to hit the F9 key, and you can see, for Bill Jelen,
in fact 1, 2, 3, 4.
The 1s are in the correct relative position
for that size array.
Control-Z.
Now I want to pick out the book ID,
so I'm going to use the IF function.
That's the logical test.
It's a bunch of 0s and 1s.
1 is true, 0 is false.
Comma.
What I want if it's true, I want that range right there.
F4.
And I'm not even going to put value of false in.
If I leave that argument empty, if we'll
put a false in where the condition is not matched.
Closed parentheses on the IF.
F9 to evaluate and, sure enough, I have what I want.
Those are the three conditions I'm
going to need to use in an OR logical test
to see if any of these rows are this one or this one
or this one or this one.
Control-Z.
And what am I interested in if I get a true from this OR
logical test?
Getting that number for however many rows there are and adding.
So the perfect function for adding with conditions
or criteria is the SUMIFs.
Sum range-- well, I'm going to highlight units.
F4.
Comma.
Criteria right here.
F4.
Comma.
Well, wait a second.
This is an OR logical test.
And, normally, we use SUMIFs with criteria
1, criteria 2, and so on for an AND logical test.
But the cool thing about what we did
is if I F9 to remind you there's multiple items here.
Anytime you put multiple items into criteria
1 inside of SUMIFs or, for that matter,
COUNTIFs, AVERAGEIFs, MAXIFs, it will instruct SUMIFs
to do an OR logical test.
Notice how many criteria do we have?
Six .
So this function argument array operation
will instruct SUMIFs to deliver six answers.
Control-Z. Close parentheses.
If I hit the F9 key, there are the six answers.
If we add those up, that'll be the total units for Bill Jelen.
Control-Z. Now the perfect function
to add a resultant array of answers is the SUMPRODUCT.
Now we could use the SUM function because that's just
an array of answers, but we'd have
to use a special keystroke, Control-Shift-Enter.
However, SUMPRODUCT is the perfect function
when your array of numbers you're trying to add
are delivered by an array operation.
That SUMPRODUCT, and that array one argument,
are programmed to add it correctly
without any special keystroke.
Close parentheses.
Control-Enter.
And I'm going to double-click and send it down
to the last cell and hit F2.
That is an amazing formula to get our total units
and deal with this many to many relationship,
or, for our formula, the fact that we had two columns
and had to run an OR logical test.
Now let's look at a couple other things.
I'm in Office 365, and I have the Insider Edition.
What that means is there's a new Excel calculation
engine which understands array formulas in any formula
we create.
So if you have Office 365 Insider Edition--
or, Microsoft says, all of Office 365
should have it sometime by the end of 2019--
then all we need is the SUM function.
Control-Enter, double-click, and send it down.
Now I'm going to Control-Z-Z. I want to leave it as SUMPRODUCT.
All right, that was a lot of fun with trying
to deal with this unusual adding with multiple conditions.
It just happened to be that this was
a many to many relationship.
But this OR logical test, the IF, SUMIFs, and SUMPRODUCT,
has no problem doing exactly what we want.
Now if we hide this right-click, hide, of course it still works.
And over here we did SUMIFs with INDEX and MATCH
to get total units by book title.
All right.
If you liked that video, be sure to click that thumbs up.
Leave a comment, and subscribe, because there's always
lots more videos to come from Excel
Is Fun We'll see ya next MSPTDA video 28.