Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to consolidate a list of names based on lowest number. Each name is associated with an account and a date. For each account I need only the lowest date to show up. I am using a pivot table.
Solved by C. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 18/09/2018 - 01:32
Hello, I understand that you need help in showing the minimum date per account, right?
User 18/09/2018 - 01:32
Sort of. I will show you an example.
Excelchat Expert 18/09/2018 - 01:35
I see. Thank you. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 18/09/2018 - 01:35
I may have a few quick question to help me fully understand your requirement.
User 18/09/2018 - 01:35
All set. It is in the spreadsheet. I want a single name to show up for all of the accounts with the same name based on the one with the date closest to today' s date.
Excelchat Expert 18/09/2018 - 01:35
Do you need the solution in Pivot Table? I'm only asking because you mentioned pivot table in the description but chose formulas as the category so I, a formula expert, claimed the session.
User 18/09/2018 - 01:36
And yes. Eventually I will put this value into a pivot table.
Excelchat Expert 18/09/2018 - 01:36
Do you want the result to be a Pivot table or are you just going to use the result as a data source for the Pivot Table?
User 18/09/2018 - 01:37
I am going to use the result in a pivot table. I just need to consolidate the different names to only produce a single name next to all of the accounts
Excelchat Expert 18/09/2018 - 01:38
Ah I see. That makes much more sense. Thank you. I'll be working on this locally on my end to ensure that it works for Excel. You are using Excel, right?
User 18/09/2018 - 01:38
Correct
Excelchat Expert 18/09/2018 - 01:39
Okay, please allow me a few minutes to work on this. I'll first be working on an array formula that extracts a unique list of the account and then work from there.
Excelchat Expert 18/09/2018 - 01:42
Did your expected result change?
Excelchat Expert 18/09/2018 - 01:42
I thought you only want to see the minimum?
Excelchat Expert 18/09/2018 - 01:43
Are you saying that you want the result to be the same number of rows as the table to the left?
User 18/09/2018 - 01:44
Yes. See how those are two different accounts? ABC is associated with the account John Doe and QRS is associated with the account Jen Smith.
Excelchat Expert 18/09/2018 - 01:44
Okay, I was working on something different earlier as you provided a different expected output before this. I'll need a few more minutes to make the changes.
Excelchat Expert 18/09/2018 - 01:47
Please extend the session when prompted.
Excelchat Expert 18/09/2018 - 01:49
Please download this file.
[Uploaded an Excel file]
Excelchat Expert 18/09/2018 - 01:49
You can test it out by changing the date in the Maturity column D to see how it'll affect the result.
User 18/09/2018 - 01:50
This seems pretty complicated. My data is formatted in a table and there are hundreds of accounts so I won't be able to hardcode the rows I want it to search through
Excelchat Expert 18/09/2018 - 01:51
There's really no hardcoding involved here, just set the rows to a theoretical max and the formula takes care of the rest.
Excelchat Expert 18/09/2018 - 01:52
So if you expect your data to be 1000 rows long then just change 8 to 1000.
Excelchat Expert 18/09/2018 - 01:52
By the way, the formulas used are array formulas so they need to be confirmed by using CTRL+SHIFT+ENTER instead of just ENTER. You'll know that you've done it correct if the formula gets automatically enclosed in { }
Excelchat Expert 18/09/2018 - 01:52
Of course the columns need to be properly referenced but I can only provide you a solution based on how you presented your data so we got this solution.
User 18/09/2018 - 01:53
Is there a simpler solution to this problem?
Excelchat Expert 18/09/2018 - 01:55
I don't think there is one. The requirement is pretty complex so Excel does not have a built-in way to accomplish this without using the array formula I presented. What makes this difficult is that we need to first identify the lowest date per account so with that alone we already have to use an array formula MIN(IF()).
User 18/09/2018 - 01:55
How I started to tackle it was I converted the maturity date into a string as they are found in excel. For example today's date is represented as 43361. I subtracted the maturity date string from today's string to get a value. Then I was going to compare those to find the smallest one in each group of accounts then associate the account with the name that has the lowest number value
Excelchat Expert 18/09/2018 - 01:56
That's actually the idea I applied but removed the additional step you are doing subtracting from today's date.
User 18/09/2018 - 01:56
Got it
Excelchat Expert 18/09/2018 - 01:57
The formula is directly calculating which date is the smallest one and then uses a lookup to find based on the name and the resulting date.
Excelchat Expert 18/09/2018 - 01:58
MIN(IF()) is the only way for us to accomplish this unless you have an active subscription to Office 365 in which case you can use MINIFS() which is the same.
Excelchat Expert 18/09/2018 - 01:58
So your proposed solution is actually much more complicated as it involves an extra step.
User 18/09/2018 - 01:58
Can you walk me through the formula then so I can understand it better? Does not seem that intuitive
Excelchat Expert 18/09/2018 - 01:58
It even uses extra columns if I'm correct.
Excelchat Expert 18/09/2018 - 01:59
I'll be honest, it's not intuitive and it's among the most complex formulas we give here at GotIt as it involves complex array formulas.
Excelchat Expert 18/09/2018 - 01:59
=IFERROR(INDEX($C$1:$C$8,SMALL(IF(($B$3:$B$8=F3)*($D$3:$D$8=MIN(IF(($B$3:$B$8=F3),$D$3:$D$8,FALSE))),ROW($C$3:$C$8),FALSE),1)),"")
Excelchat Expert 18/09/2018 - 02:01
Here's the formula I'm going to try and break it down but I won't feel bad if you won't understand it within the allotted time as even some experts here at GotIt won't be able to understand the formula in such a short time. And I'm not saying that to belittle you, I'm only saying this to show you how complex this formula really is and unfortunately there's not simple alternative to it.
Excelchat Expert 18/09/2018 - 02:01
This formula is what we'd call a multi-criteria lookup.
Excelchat Expert 18/09/2018 - 02:02
The usual lookup formulas can only use a single criteria to lookup a value.
Excelchat Expert 18/09/2018 - 02:02
Since your requirement needs to lookup both the account and the lowest date, we can't use a simple lookup and we need a multi-criteria lookup.
Excelchat Expert 18/09/2018 - 02:03
What makes this extra complex is that the formula is a nested multi-criteria lookup which means it's a multi-criteria lookup inside a multi-criteria lookup.
Excelchat Expert 18/09/2018 - 02:04
The first thing that needs to be done is to get the earliest date per account.
Excelchat Expert 18/09/2018 - 02:04
And that is accomplished with this part:
Excelchat Expert 18/09/2018 - 02:04
MIN(IF(($B$3:$B$8=F3),$D$3:$D$8,FALSE))
Excelchat Expert 18/09/2018 - 02:04
This checks the name in column F and compiles all the dates for that specific name.
Excelchat Expert 18/09/2018 - 02:05
Then the MIN that surrounds them is what makes it possible to get the earliest date.
Excelchat Expert 18/09/2018 - 02:05
So in the case of G4, it is compiling the list of dates for John Doe.
Excelchat Expert 18/09/2018 - 02:05
9/20/2018 9/20/2025 9/20/2044
Excelchat Expert 18/09/2018 - 02:05
Then we use MIN with those dates.
Excelchat Expert 18/09/2018 - 02:05
MIN(9/20/2018 9/20/2025 9/20/2044)
Excelchat Expert 18/09/2018 - 02:06
This results to 9/20/2018
Excelchat Expert 18/09/2018 - 02:06
From there we have the 2 criteria we need, we have the name of the account which is John Doe and we have the earliest date which is 9/20/2018
Excelchat Expert 18/09/2018 - 02:06
This is where the multi-criteria lookup is necessary.
Excelchat Expert 18/09/2018 - 02:07
We are now trying to lookup John Doe with a date of 9/20/2018
Excelchat Expert 18/09/2018 - 02:07
Since those 2 criteria is only found in Row 4, we can return whatever Name is in C4.
Excelchat Expert 18/09/2018 - 02:08
The part of the formula that tells the column which to return is the first INDEX.
Excelchat Expert 18/09/2018 - 02:08
(INDEX($C$1:$C$8
Excelchat Expert 18/09/2018 - 02:08
That part tells Excel where to get the result from.
Excelchat Expert 18/09/2018 - 02:08
While the rest of the part is calculating where to find the row where both our criterias are satisfied.
Excelchat Expert 18/09/2018 - 02:08
,SMALL(IF(($B$3:$B$8=F3)*($D$3:$D$8=MIN(IF(($B$3:$B$8=F3),$D$3:$D$8,FALSE))),ROW($C$3:$C$8),FALSE),1)),"")
Excelchat Expert 18/09/2018 - 02:09
I know it's a lot to take in. It took my literally years to fully understand how array formulas work.
User 18/09/2018 - 02:10
Got it. Thank you for the explanation. What do I have to do to enter this formula again?
Excelchat Expert 18/09/2018 - 02:10
CTRL+SHIFT+ENTER
User 18/09/2018 - 02:11
Thank you
Excelchat Expert 18/09/2018 - 02:11
You are welcome. Would there be anything else that I can help you with regards to the original question?
Excelchat Expert 18/09/2018 - 02:14
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.