Expert Solution - General Questions on Pivot Table

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.