Unique List based on Criteria...

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello,

I can't figure out how to do this and need some help.

Here's my data:

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 19px"><COL style="WIDTH: 102px"><COL style="WIDTH: 19px"><COL style="WIDTH: 89px"><COL style="WIDTH: 19px"><COL style="WIDTH: 96px"><COL style="WIDTH: 19px"><COL style="WIDTH: 141px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>ID</TD><TD>Check</TD><TD></TD><TD>Unique ID Count</TD><TD></TD><TD>Unique ID List</TD><TD></TD><TD>ID With Criteria</TD><TD></TD><TD>Unique ID With Criteria</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">123456</TD><TD>Yes</TD><TD></TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD style="TEXT-ALIGN: right">123456</TD><TD></TD><TD style="TEXT-ALIGN: right">123456</TD><TD></TD><TD style="TEXT-ALIGN: right">123456</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">123456</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123457</TD><TD></TD><TD style="TEXT-ALIGN: right">123456</TD><TD></TD><TD style="TEXT-ALIGN: right">123458</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">123457</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123458</TD><TD></TD><TD style="TEXT-ALIGN: right">123458</TD><TD></TD><TD style="TEXT-ALIGN: right">123461</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">123457</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123459</TD><TD></TD><TD style="TEXT-ALIGN: right">123458</TD><TD></TD><TD style="TEXT-ALIGN: right">123463</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">123458</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123460</TD><TD></TD><TD style="TEXT-ALIGN: right">123461</TD><TD></TD><TD style="TEXT-ALIGN: right">123465</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">123458</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123461</TD><TD></TD><TD style="TEXT-ALIGN: right">123461</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">123459</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123462</TD><TD></TD><TD style="TEXT-ALIGN: right">123463</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">123459</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123463</TD><TD></TD><TD style="TEXT-ALIGN: right">123463</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">123460</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123464</TD><TD></TD><TD style="TEXT-ALIGN: right">123465</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">123460</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">123465</TD><TD></TD><TD style="TEXT-ALIGN: right">123465</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">123461</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">123461</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">123462</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">123462</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">123463</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">123463</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">123464</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">123464</TD><TD>No</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">123465</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">123465</TD><TD>Yes</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>{=SUM(IF(FREQUENCY(IF(A2:A21<>"",MATCH("~"&A2:A21,A2:A21&"",0)),ROW(A2:A21)-ROW(A2)+1),1))}</TD></TR><TR><TD>F2</TD><TD>{=IF(ROWS(F$2:F2)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F1,A$2:A$21),0)),"")}</TD></TR><TR><TD>H2</TD><TD>{=IF(ROWS(H$2:H2)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H2))),"")}</TD></TR><TR><TD>F3</TD><TD>{=IF(ROWS(F$2:F3)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F2,A$2:A$21),0)),"")}</TD></TR><TR><TD>H3</TD><TD>{=IF(ROWS(H$2:H3)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H3))),"")}</TD></TR><TR><TD>F4</TD><TD>{=IF(ROWS(F$2:F4)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F3,A$2:A$21),0)),"")}</TD></TR><TR><TD>H4</TD><TD>{=IF(ROWS(H$2:H4)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H4))),"")}</TD></TR><TR><TD>F5</TD><TD>{=IF(ROWS(F$2:F5)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F4,A$2:A$21),0)),"")}</TD></TR><TR><TD>H5</TD><TD>{=IF(ROWS(H$2:H5)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H5))),"")}</TD></TR><TR><TD>F6</TD><TD>{=IF(ROWS(F$2:F6)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F5,A$2:A$21),0)),"")}</TD></TR><TR><TD>H6</TD><TD>{=IF(ROWS(H$2:H6)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H6))),"")}</TD></TR><TR><TD>F7</TD><TD>{=IF(ROWS(F$2:F7)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F6,A$2:A$21),0)),"")}</TD></TR><TR><TD>H7</TD><TD>{=IF(ROWS(H$2:H7)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H7))),"")}</TD></TR><TR><TD>F8</TD><TD>{=IF(ROWS(F$2:F8)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F7,A$2:A$21),0)),"")}</TD></TR><TR><TD>H8</TD><TD>{=IF(ROWS(H$2:H8)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H8))),"")}</TD></TR><TR><TD>F9</TD><TD>{=IF(ROWS(F$2:F9)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F8,A$2:A$21),0)),"")}</TD></TR><TR><TD>H9</TD><TD>{=IF(ROWS(H$2:H9)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H9))),"")}</TD></TR><TR><TD>F10</TD><TD>{=IF(ROWS(F$2:F10)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F9,A$2:A$21),0)),"")}</TD></TR><TR><TD>H10</TD><TD>{=IF(ROWS(H$2:H10)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H10))),"")}</TD></TR><TR><TD>F11</TD><TD>{=IF(ROWS(F$2:F11)<=D$2,INDEX(A$2:A$21,MATCH(0,COUNTIF(F$1:F10,A$2:A$21),0)),"")}</TD></TR><TR><TD>H11</TD><TD>{=IF(ROWS(H$2:H11)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H11))),"")}</TD></TR><TR><TD>H12</TD><TD>{=IF(ROWS(H$2:H12)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H12))),"")}</TD></TR><TR><TD>H13</TD><TD>{=IF(ROWS(H$2:H13)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H13))),"")}</TD></TR><TR><TD>H14</TD><TD>{=IF(ROWS(H$2:H14)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H14))),"")}</TD></TR><TR><TD>H15</TD><TD>{=IF(ROWS(H$2:H15)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H15))),"")}</TD></TR><TR><TD>H16</TD><TD>{=IF(ROWS(H$2:H16)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H16))),"")}</TD></TR><TR><TD>H17</TD><TD>{=IF(ROWS(H$2:H17)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H17))),"")}</TD></TR><TR><TD>H18</TD><TD>{=IF(ROWS(H$2:H18)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H18))),"")}</TD></TR><TR><TD>H19</TD><TD>{=IF(ROWS(H$2:H19)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H19))),"")}</TD></TR><TR><TD>H20</TD><TD>{=IF(ROWS(H$2:H20)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H20))),"")}</TD></TR><TR><TD>H21</TD><TD>{=IF(ROWS(H$2:H21)<=D$2,INDEX(A$2:A$21,SMALL(IF(B$2:B$21="Yes",ROW(A$2:A$21)-ROW(H$2)+1),ROWS(H$2:H21))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I want to create a unique list of values (i.e. no duplicates) that also considers a criteria in a different Column.

So my list is in Cells A1:A21, including the headers, and I want a formula to create a list of unique IDs where Column B equals "Yes".

I know how to get a unique count, generate a unique list, as well as to create a list based on a criteria, but I can't get the syntax right to create what I have manually created in Column J.

Can anyone help, please?

Thanks,

Matty
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
As stated, use a pivot table,

Otherwise, if you are dead set on that methodology
use an if statement combined with the sumproduct, have the sumproduct look for anything with only 1 number.

=if(sumproduct(--(A:A=A1))=1,A1,"")
This will check A:A for A1 and if there is only 1 instance of it, ie: unique, list it, otherwise leave blank.

~jc
 
Upvote 0
Thanks for the replies, but perhaps I wasn't specific enough with my request.

I do not want to extract just the uniques (there aren't any!); I want to create a list of uniques from a list that has duplicates, but only where Column B equals "Yes".

Hope this is clear.

Thanks,

Matty
 
Upvote 0
Doing what I just stipulated,
I tossed both ID and check into the column and unselected No for Check from my data set.
In doing so, I was left with
123456
123458
123461
123463
123465

Regards,
jc
 
Upvote 0
Appreciate your help, but I need a formula for this, not a Pivot Table or VBA (not that anyone has suggested VBA yet).

I will continue playing with this tomorrow myself, but if anyone comes up with something in the meantime, it'd save me a job.

Thanks,

Matty
 
Upvote 0
Matty said:
Appreciate your help, but I need a formula for this, not a Pivot Table or VBA (not that anyone has suggested VBA yet).

I will continue playing with this tomorrow myself, but if anyone comes up with something in the meantime, it'd save me a job.

Thanks,

Matty
Click to expand...

<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=281><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2702" width=76><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=71>ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=70>Check</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=76 align=right>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>D-List</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123456</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123457</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123458</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123457</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123461</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123458</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123463</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123458</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>123465</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123459</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123459</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123460</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123460</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123461</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123461</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123462</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123462</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123463</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123463</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123464</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123464</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123465</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=71>123465</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

Let A1:B21 on Sheet1 house the sample, the headers included.

Define Rvec as referring to:

=ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1

D1, control+shift+enter, not just enter:
Code: 
=SUM(IF(FREQUENCY(IF(A2:A21<>"",IF(B2:B21="Yes",
    MATCH("~"&A2:A21,A2:A21&"",0))),Rvec),1))

D3, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($D$3:D3)<=$D$1,INDEX($A$2:$A$21,
    SMALL(IF(FREQUENCY(IF($A$2:$A$21<>"",IF($B$2:$B$21="Yes",
     MATCH("~"&$A$2:$A$21,$A$2:$A$21&"",0))),Rvec),Rvec),
      ROWS($D$3:D3))),"")

P.S. Please try not to reproduce so many formulas when using excel-jeanie.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Comparing 2 worksheets with different rows with conditions
Replies
9
Views
107
Country_Calc_2
C
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
96
strugglingaccountant
S
M
  • Solved
Average cells based on a date criteria, with blanks cells in various rows...
Replies
5
Views
117
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
100
JMC57
J
G
  • Question
VBA or Power Query or combo to unpivot my table
Replies
1
Views
94
mse330
M

Forum statistics

Threads
1,214,599
Messages
6,120,449
Members
448,966
Latest member
DannyC96

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