How to return ALL values for multiple identifying factors.

S

Sarahsodapop

New Member
Joined
Jul 19, 2011
Messages
17
Hello all,

I have a spreadsheet with two tabs --- one of which (named 2011 Schedule) has a list of names (column B) with an identifying # next to it (column A, numbers 1-14, or no number) and dates that correspond to the name (column C). The other tab has a subset of those identifying numbers (ex. 3,4,5,6) in one column.

Through an INDEX/array formula I have managed to return all names with the identifying value #3, but cannot manage to get it to return all names with the values 3,4,5, or 6. Current formula (entered as an array) is here: =INDEX('2011 Schedule'!A:B,SMALL(IF('2011 Schedule'!A:A=List!$E$27,ROW('2011 Schedule'!$A:$A)),ROW(1:1)),2)

Ideally, I would like it not only to return all names that have the identifying numbers within the set above it (i.e. 3,4,5,6), but also to return the names in date order (from column C on the first tab).

I was hoping to attach a spreadsheet that could illustrate better than I can explain, but can't see how to do that. I am using Excel 2007.

Any help would be greatly appreciated!!!

Thanks,
Sarah <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Sarahsodapop said:
Hello all,

I have a spreadsheet with two tabs --- one of which (named 2011 Schedule) has a list of names (column B) with an identifying # next to it (column A, numbers 1-14, or no number) and dates that correspond to the name (column C). The other tab has a subset of those identifying numbers (ex. 3,4,5,6) in one column.

Through an INDEX/array formula I have managed to return all names with the identifying value #3, but cannot manage to get it to return all names with the values 3,4,5, or 6. Current formula (entered as an array) is here: =INDEX('2011 Schedule'!A:B,SMALL(IF('2011 Schedule'!A:A=List!$E$27,ROW('2011 Schedule'!$A:$A)),ROW(1:1)),2)

Ideally, I would like it not only to return all names that have the identifying numbers within the set above it (i.e. 3,4,5,6), but also to return the names in date order (from column C on the first tab).

I was hoping to attach a spreadsheet that could illustrate better than I can explain, but can't see how to do that. I am using Excel 2007.

Any help would be greatly appreciated!!!

Thanks,
Sarah <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Click to expand...
In what date order? Ascending or descending?

Are there duplicate dates?
 
Upvote 0
Sarahsodapop said:
T -- there should not be any duplicate dates, and I'd like them sorted in ascending order.

Thanks!
Sarah
Click to expand...
Try this...

Book1
ABC
1IDNameDate
25Name110/14/2000
314Name22/11/2006
48Name312/22/2004
52Name43/8/2008
610Name53/13/2005
711Name62/16/2003
83Name710/22/2008
97Name86/17/2014
106Name94/9/2009
114Name108/25/2005
1213Name113/4/2007
139Name129/20/2013
1412Name134/19/2005
151Name1412/8/2004
Sheet1

In the formulas I use the following named ranges:
  • ID
  • Refers to: =Sheet1!$A$2:$A$15
  • Names
  • Refers to: =Sheet1!$B$2:$B$15
  • Dates
  • Refers to: =Sheet1!$C$2:$C$15
On some other sheet...

Enter this formula in A1. This will return the count of records that meet the criteria.

=COUNTIF(ID,">=3")-COUNTIF(ID,">6")

Enter this array formula** in A2. This will extract the names in date ascending order.

=IF(ROWS(A$2:A2)>A$1,"",INDEX(Names,MATCH(SMALL(IF(ID>=3,IF(ID<=6,Dates)),ROWS(A$2:A2)),IF(ID>=3,IF(ID<=6,Dates)),0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.
 
Upvote 0
I'm afraid I left out a critical piece of this. My apologies -- your formula works fantastic, except for what I forgot to tell you. There will be more than one instance of the ID # at left. I added to the chart below to illustrate.

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=211><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl73 height=18 width=64>ID


</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl74 width=64>Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl74 width=83>Date</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>5</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>10/14/2000</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>14</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>2/11/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>8</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name3</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>12/22/2004</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name4</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>3/8/2008</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>10</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name5</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>3/13/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>11</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>2/16/2003</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>3</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name7</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>10/22/2008</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>7</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name8</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>6/17/2014</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name9</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>4/9/2009</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>4</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name10</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>8/25/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>13</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name11</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>3/4/2007</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>9</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name12</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>9/20/2013</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl71 height=18 width=64>12</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 width=64>Name13</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl77 width=83>4/19/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>3</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name14</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>12/8/2004


</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>6</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name15</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>3/21/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>3</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name16</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>3/9/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>7</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name17</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>9/25/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>5</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name18</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>9/8/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>12</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name19</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>3/27/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>4</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name20</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>4/6/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl75 height=18 width=64>8</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl76 width=64>Name21</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl78 width=83>12/10/2005</TD></TR></TBODY></TABLE>

In that instance, the result I would like to get is:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2795248 class=xl73 height=18 width=64>Name 14</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 16</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 15</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 10</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 18</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 20</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 7</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=18>Name 9</TD></TR></TBODY></TABLE>

since that shows the names, in ascending date order, that have an ID of 3, 4, 5, or 6.

So sorry that I left out that critical piece -- I often assume that what's inside my head makes it outside just as clearly.... and I'm often wrong.

Sarah
 
Upvote 0
Sarahsodapop said:
I'm afraid I left out a critical piece of this. My apologies -- your formula works fantastic, except for what I forgot to tell you. There will be more than one instance of the ID # at left. I added to the chart below to illustrate.

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=211 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>ID



</TD><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name</TD><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>Date</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>5</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name1</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>10/14/2000</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>14</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name2</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>2/11/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>8</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name3</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>12/22/2004</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>2</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name4</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>3/8/2008</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>10</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name5</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>3/13/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>11</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name6</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>2/16/2003</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>3</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name7</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>10/22/2008</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>7</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name8</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>6/17/2014</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>6</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name9</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>4/9/2009</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>4</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name10</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>8/25/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>13</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name11</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>3/4/2007</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>9</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name12</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>9/20/2013</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>12</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=64>Name13</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: white" width=83>4/19/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>3</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name14</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>12/8/2004



</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>6</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name15</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>3/21/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>3</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name16</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>3/9/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>7</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name17</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>9/25/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>5</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name18</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>9/8/2005</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>12</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name19</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>3/27/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>4</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name20</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>4/6/2006</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>8</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Name21</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=83>12/10/2005</TD></TR></TBODY></TABLE>

In that instance, the result I would like to get is:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 id=td_post_2795248 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18>Name 14</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 16</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 15</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 10</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 18</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 20</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 7</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Name 9</TD></TR></TBODY></TABLE>

since that shows the names, in ascending date order, that have an ID of 3, 4, 5, or 6.

So sorry that I left out that critical piece -- I often assume that what's inside my head makes it outside just as clearly.... and I'm often wrong.

Sarah
Click to expand...
Ok, these are the results I get based on that posted sample data:

Name1
Name14
Name16
Name15
Name10
Name18
Name20
Name7
Name9

You don't have Name1 in your list. Name1 meets the criteria.

:confused:
 
Upvote 0
That is amazing. I don't pretend to understand all of it, but it absolutely works!

Two follow-up questions:
1. If I only need it to return values for ID #4, say, is there a way to simplify that formula?
2. If (and this isn't needed yet, but...) I have a need to return values for non-consecutive IDs, like 3, 5, 9, how would I reflect that?

Thank you for all of your help so far! This is outstanding!

Sarah
 
Upvote 0
Sarahsodapop said:
That is amazing. I don't pretend to understand all of it, but it absolutely works!

Two follow-up questions:
1. If I only need it to return values for ID #4, say, is there a way to simplify that formula?
2. If (and this isn't needed yet, but...) I have a need to return values for non-consecutive IDs, like 3, 5, 9, how would I reflect that?

Thank you for all of your help so far! This is outstanding!

Sarah
Click to expand...
With question 1 is becomes slightly easier but with question 2 it becomes more complicated!

For question 1:

Count formula:

=COUNTIF(ID,4)

Extraction formula**:

=IF(ROWS(A$2:A2)>E$1,"",INDEX(Names,MATCH(SMALL(IF(ID=4,Dates),ROWS(A$2:A2)),IF(ID=4,Dates),0)))

For question 2:

For non-consecutive IDs it would be better to use cells to hold those:

F1:F3 = 3, 5, 9

Count formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(ID,F1:F3,0))))

Extraction formula**:

=IF(ROWS(A$2:A2)>E$1,"",INDEX(Names,MATCH(SMALL(IF(ISNUMBER(MATCH(ID,F$1:F$3,0)),Dates),ROWS(A$2:A2)),IF(ISNUMBER(MATCH(ID,F$1:F$3,0)),Dates),0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
186
sijpie
sijpie
W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
718
wsr429
W
P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
653
DanteAmor
DanteAmor
P
  • Solved
Find cell "X" and return Header
Replies
4
Views
439
Fluff
Fluff
K
  • Solved
Identifying the duplicate id names with latest date by formula or VBA
Replies
2
Views
493
kismatstha
K

Forum statistics

Threads
1,224,600
Messages
6,179,835
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top