Index Match entire row descending order

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
I have rows of data I am trying to Index Match variable amounts of dates from. I saw a blog mentioned descending order can extract the highest, then lowest matches. Table example:

BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNone
Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNone
JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNone

I am trying to extract the date values as row entries to a different sheet like so :

Bob1/1/2020
Bob1/3/2020
Stanley1/10/2020
Jim11/7/2019
Jim12/15/2019


I have already generated a list of names with duplicate name entries for multiple dates per name. I need to figure out how to extract the largest (or smallest) date, and drag down next largest (smallest) date for repeated name.

Thanks!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
553
Office Version
2016
Platform
Windows
Hi Voltrader,

Does this do what you want?

Book1
ABCDEFGHIJKLMN
1BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1/1/2020
2Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneBob1/3/2020
3JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
4Jim11/7/2019
5Jim12/15/2019
Sheet1
Cell Formulas
RangeFormula
N1:N5N1=AGGREGATE(15,6,($B$1:$J$9)/($A$1:$A$9=M1),COUNTIF($M$1:$M1,M1))
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
Hi Voltrader,

Does this do what you want?

Book1
ABCDEFGHIJKLMN
1BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1/1/2020
2Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneBob1/3/2020
3JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
4Jim11/7/2019
5Jim12/15/2019
Sheet1
Cell Formulas
RangeFormula
N1:N5N1=AGGREGATE(15,6,($B$1:$J$9)/($A$1:$A$9=M1),COUNTIF($M$1:$M1,M1))

Hi Toadstool that works great! Thanks!
I'm just curious how the 15,6, after AGGREGATE work =(AGGREGATE(15,6,($B$1:$J$9)/($A$1:$A$9=M1),COUNTIF($M$1:$M1,M1)) , does this serve as an offset function? I am trying to apply the formula to a larger dataset then the example I posted on the thread. After the 5th row entry (on larger dataset) the formula returns "#NUM", I'm guessing I need to adjust the numbers directly after the AGGREGATE?

Thanks again!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
553
Office Version
2016
Platform
Windows
Hi Voltrader,

You said you already had the list of names with duplicates so I didn't try and create that but be aware if you miss one then it won't report the 2nd+ date entry. Also it must have that "None" in cells without a date because if you leave it null that would be treated as a Jan 1st 1900 date.

Yes, you need to change the range used if you have more data as my formula assume you start in row 1 and go to row 9. You can change that range to row 1 to row 9,999 without having much impact on speed of execution.

To return multiple values from a list you will usually use SMALL to get ascending and LARGE to get descending but these are array formulas so for quicker execution you can use AGGREGATE where the 15 says select its SMALL function and 6 says to ignore errors within the AGGREGATE as a mismatch on the selection gives a #DIV/0 error.

The $B$1:$J$9 part tells AGGREGATE where it will get the values from and the / ($A$1:$A$9=M1) tells it to divide each value by the logical check of the name matching the one currently looked at in your list, so if I'm looking in the Bob row for a Bob then it divides by the logical result Bob=Bob which is 1 so the date divided by the date = the date and it's returned. If I've reached the Jim row and I'm looking for Bob then the logical result is FALSE or zero so I get the date divided by 0 which generates a #DIV/0 error but AGGREGATE ignores that as we used the option 6 to suppress errors.

The last part of AGGREGATE is for which SMALL part you want, 1=smallest, 2=next smallest, etc. COUNTIF($M$1:$M1,M1) will return a 1 against the first list of Bob but when we get to the next Bob entry COUNTIF($M$1:$M2,M2) returns 2 so it looks for the next highest date.

I could also prevent the #NUM in the AGGREGATE function by telling it when to stop. If I do a COUNTIF of all cells greater than zero, so a date, and put that in a cell, in my example P2, then I can use an IF to display nulls after that many have been retrieved.

Book1
ABCDEFGHIJKLMNOP
1NameDate 1Date 2Date 3Date 4Date 5Date 6Date 7Date 8Date 9Bob1/1/2020Count of Dates
2BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1/3/20205
3Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley1/10/2020
4JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneJim11/7/2019
5Jim12/15/2019
6
Sheet1 (2)
Cell Formulas
RangeFormula
P2P2=COUNTIF($B$2:$J$9999,">0")
N1:N6N1=IF(ROW()>$P$2,"",AGGREGATE(15,6,($B$2:$J$10)/($A$2:$A$10=M1),COUNTIF($M$1:$M1,M1)))
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
OK I am not clear where the #NUM error is coming from? Is it because the Duplicate list is not exact?
My Duplicate list can be something like:

Bob
Bob
Jim
Dan
Dan
Dan

Where a Duplicate can go as high as 3, and there can also be no duplicates only 1 entry. Is the 1 entry causing the #NUM error?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
553
Office Version
2016
Platform
Windows
You'll get #NUM errors if you add data and don't change the range, or if you have a name in your name list without a matching date against that name in the data.
e.g. you have one Jim in your names but Jim has no dates, or you have three Bob's but Bob only has two dates.

What is the largest number of rows you'll ever have in the data set of dates?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
553
Office Version
2016
Platform
Windows
Hi Voltrader,

My insomnia suggested I take the sheet a little further...

Below, I hope, is a more complete solution:
  • It allows for 9,999 rows of your data.
  • It allows for 888 unique Names.
  • It supports up to 25,000 Name/Date extractions as columns P & Q are copied down to row 25,000.

  1. Column L extracts unique Names from column A.
  2. Column M counts how many dates there are for that Name in column L.
  3. Column N maintains a running total of those counts.
  4. Column P starts in P1 with the first Name from L2, then from P2 it uses the Running Total column N to decide the Name to use. Once the running total for that name is exhausted it gets the name from the next row down in column L.
  5. Column Q is the old formula to extract the next highest date for the name in column P.
  6. Cell S2 is a count of all dates and S3 is a check that it matches the highest number from the running total.

It takes a while to execute as I wasn't sure how many rows of data you had. You can adjust the $9999 and $888 values accordingly and copy down the L,M,N,P and Q formulae as far as required.

I hope this helps.

Book1
ABCDEFGHIJKLMNOPQRS
1NameDate 1Date 2Date 3Date 4Date 5Date 6Date 7Date 8Date 9NamesNo. of DatesRunning TotalBob1/1/2020Count of Dates
2BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1111Bob1/1/202029
3Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley516Bob1/1/2020
4JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneJim1026Bob1/1/2020
5BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneSam127Bob1/1/2020
6Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneSue229Bob1/3/2020
7JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneBob1/3/2020
8BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1/3/2020
9Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneBob1/3/2020
10JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneBob1/3/2020
11Bob2/2/20201/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob2/2/2020
12Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley1/10/2020
13JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
14BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneStanley1/10/2020
15Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley1/10/2020
16JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
17Sam1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneJim11/7/2019
18SueNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneJim11/7/2019
Sheet1 (2)
Cell Formulas
RangeFormula
P1P1=L2
Q1:Q18Q1=IF(P1="","",AGGREGATE(15,6,($B$2:$J$9999)/($A$2:$A$9999=P1),COUNTIF($P$1:$P1,P1)))
P2:P18P2=IF(ROW()>$S$2,"",IF(ROW()>INDEX($N$2:$N$888,MATCH(P1,$L$2:$L$888,0)),INDEX($L$2:$L$888,MATCH(P1,$L$2:$L$888,0)+1),P1))
L2:L18L2=IF(L1="","",INDEX($A$2:$A$9999,MATCH(0,INDEX(COUNTIF($L$1:$L1,$A$2:$A$9999),),0))&"")
M2:M18M2=IF(L2="","",SUMPRODUCT(($A$2:$A$9999=L2)*(ISNUMBER($B$2:$J$9999))))
N2:N18N2=IF(L2="","",SUM($M$2:M2))
S2S2=COUNTIF($B$2:$J$9999,">0")
S3S3=IF(S2<>MAX(N2:N888),"ERROR!","")
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
You'll get #NUM errors if you add data and don't change the range, or if you have a name in your name list without a matching date against that name in the data.
e.g. you have one Jim in your names but Jim has no dates, or you have three Bob's but Bob only has two dates.

What is the largest number of rows you'll ever have in the data set of dates?
It is weird that I am getting the #NUM error because the duplicated entries match the total amount of dates.
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
Hi Voltrader,

My insomnia suggested I take the sheet a little further...

Below, I hope, is a more complete solution:
  • It allows for 9,999 rows of your data.
  • It allows for 888 unique Names.
  • It supports up to 25,000 Name/Date extractions as columns P & Q are copied down to row 25,000.

  1. Column L extracts unique Names from column A.
  2. Column M counts how many dates there are for that Name in column L.
  3. Column N maintains a running total of those counts.
  4. Column P starts in P1 with the first Name from L2, then from P2 it uses the Running Total column N to decide the Name to use. Once the running total for that name is exhausted it gets the name from the next row down in column L.
  5. Column Q is the old formula to extract the next highest date for the name in column P.
  6. Cell S2 is a count of all dates and S3 is a check that it matches the highest number from the running total.

It takes a while to execute as I wasn't sure how many rows of data you had. You can adjust the $9999 and $888 values accordingly and copy down the L,M,N,P and Q formulae as far as required.

I hope this helps.

Book1
ABCDEFGHIJKLMNOPQRS
1NameDate 1Date 2Date 3Date 4Date 5Date 6Date 7Date 8Date 9NamesNo. of DatesRunning TotalBob1/1/2020Count of Dates
2BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1111Bob1/1/202029
3Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley516Bob1/1/2020
4JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneJim1026Bob1/1/2020
5BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneSam127Bob1/1/2020
6Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneSue229Bob1/3/2020
7JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneBob1/3/2020
8BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob1/3/2020
9Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneBob1/3/2020
10JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneBob1/3/2020
11Bob2/2/20201/1/2020NoneNoneNone1/3/2020NoneNoneNoneBob2/2/2020
12Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley1/10/2020
13JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
14BobNone1/1/2020NoneNoneNone1/3/2020NoneNoneNoneStanley1/10/2020
15Stanley1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneStanley1/10/2020
16JimNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneStanley1/10/2020
17Sam1/10/2020NoneNoneNoneNoneNoneNoneNoneNoneJim11/7/2019
18SueNoneNoneNone11/7/201912/15/2019NoneNoneNoneNoneJim11/7/2019
Sheet1 (2)
Cell Formulas
RangeFormula
P1P1=L2
Q1:Q18Q1=IF(P1="","",AGGREGATE(15,6,($B$2:$J$9999)/($A$2:$A$9999=P1),COUNTIF($P$1:$P1,P1)))
P2:P18P2=IF(ROW()>$S$2,"",IF(ROW()>INDEX($N$2:$N$888,MATCH(P1,$L$2:$L$888,0)),INDEX($L$2:$L$888,MATCH(P1,$L$2:$L$888,0)+1),P1))
L2:L18L2=IF(L1="","",INDEX($A$2:$A$9999,MATCH(0,INDEX(COUNTIF($L$1:$L1,$A$2:$A$9999),),0))&"")
M2:M18M2=IF(L2="","",SUMPRODUCT(($A$2:$A$9999=L2)*(ISNUMBER($B$2:$J$9999))))
N2:N18N2=IF(L2="","",SUM($M$2:M2))
S2S2=COUNTIF($B$2:$J$9999,">0")
S3S3=IF(S2<>MAX(N2:N888),"ERROR!","")
Wow Amazing! Let me work on this!
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
Toadstool,thanks so much this method works great!Tested it over the weekend and its awesome, I really appreciate the time you put into it!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,461
Messages
5,414,651
Members
403,541
Latest member
J0hnJ

This Week's Hot Topics

Top