Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values are all values merged into one distinct value, in other words, the list contains no duplicates.
What's on this page
1. Extract unique distinct values A to Z from a range and ignore blanks - Excel 365
Excel 365 formula in cell B8:
Explaining formula
Step 1 - Rearrange values
The TOCOL function lets you rearrange values in a 2D cell range to a single column.
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:E5)
becomes
TOCOL({"Blackberry", 0, 0, 0;0, "Watermelon", "Apple", "Peach";0, "Kiwifruit", 0, "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"})
and returns
{"Blackberry"; 0; 0; 0; 0; "Watermelon"; "Apple"; "Peach"; 0; "Kiwifruit"; 0; "Blackberry"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"}
Step 2 - Extract a unique distinct list
The UNIQUE function lets you extract both unique and unique distinct values and also compare columns to columns or rows to rows.
UNIQUE(array, [by_col], [exactly_once])
UNIQUE(TOCOL(B2:E5))
becomes
UNIQUE({"Blackberry"; 0; 0; 0; 0; "Watermelon"; "Apple"; "Peach"; 0; "Kiwifruit"; 0; "Blackberry"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"})
and returns
{"Blackberry"; 0; "Watermelon"; "Apple"; "Peach"; "Kiwifruit"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"}
Step 3 - Sort values
The SORT function sorts values from a cell range or array.
SORT(array, [sort_index], [sort_order], [by_col])
SORT(UNIQUE(TOCOL(B2:E5)))
becomes
SORT({"Blackberry"; 0; "Watermelon"; "Apple"; "Peach"; "Kiwifruit"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"})
and returns
{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0}
Step 4 - Logical test
To remove 0 (zeros) we need to identify values not equal to zero. The less than and larger than characters combined is the same as not equal to.
SORT(UNIQUE(TOCOL(B2:E5)))<>0
becomes
{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0}<>0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE}.
Step 5 - Filter values not equal to 0 (zero)
The FILTER function extracts values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(SORT(UNIQUE(TOCOL(B2:E5))),SORT(UNIQUE(TOCOL(B2:E5)))<>0)
becomes
FILTER({"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0},{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE})
and returns
{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"}
Step 6 - Shorten formula
The LET function names intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
FILTER(SORT(UNIQUE(TOCOL(B2:E5))),SORT(UNIQUE(TOCOL(B2:E5)))<>0)
SORT(UNIQUE(TOCOL(B2:E5))) is repeated twice in the formula, I will name this intermediate calculation x.
LET(x,SORT(UNIQUE(TOCOL(B2:E5))),FILTER(x,x<>0))
2. Extract unique distinct values A to Z from a range and ignore blanks
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from range in excel
Question: This is exactly what I've been looking for... almost. It breaks if there are blank cells in the named range. Is there a way to get this to work if there are blanks in the range?
Answer: There are two things you can consider.
(1) Fill the blanks with some text
- Select the range
- Press F5
- Press with left mouse button on "Special..."
- Press with left mouse button on "Blanks"
- Press with left mouse button on OK!
- Type A
- Press Ctrl + Enter
All blanks are filled with the letter A. Remember that your new unique distinct list will contain A.
See this post: How to automatically fill all blanks with missing data or formula
or
(2)
Array formula in B8:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Then copy cell B8 and paste it down as far as necessary.
How to implement array formula to your workbook
If your list starts at, for example, F3. Change $B$7:B7 in the above formulas to F2:$F$2.
Explaining array formula in cell B8
Step 1 - Count previous values that are ignored
The COUNTIF function counts cells based on a condition, however, in this case, I am using it to check that no duplicates are returned.
COUNTIF($B$7:B7,$B$2:$E$5)
returns {0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}.
The array above contains only 0's (zeros), which means that no value has yet been shown. Cell range $B$7:B7 expands as the formula is copied to cells below, this makes sure that all previous values are checked.
Step 2 - Identify blank cells
The ISBLANK function returns TRUE if cell is empty and FALSE if it contains at least one character.
ISBLANK($B$2:$E$5)
returns {FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}.
The image above shows the array entered in cell range B7:E10, boolean values TRUE corresponds to the empty values in cell range B2:E5.
Step 3 - Add arrays and compare to zero
COUNTIF($B$7:B9, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0
becomes
{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}+{FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}=0
becomes
{0,1,1,1;1,0,0,0;1,0,1,0;0,0,0,0}=0
and returns
{TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE}
The array above keeps track of blank cells and prior values.
Step 4 - Replace boolean values with numbers based on their relative position if they were sorted
IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,"")
becomes
IF({TRUE,FALSE, FALSE,FALSE;FALSE,TRUE, TRUE,TRUE;FALSE,TRUE, FALSE,TRUE;TRUE,TRUE, TRUE,TRUE},{3,1,1,1;1,10,1,8;1,7,1,3;9,5,6,2},"")
and returns
{3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}
Step 5 - Get smallest value in array
SMALL(IF(SMALL(IF(COUNTIF($B$7:B9, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)
becomes
SMALL({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}, 1)
and returns 1.
Step 6 - Replace smallest value with row number
IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=IF(ISBLANK($B$2:$E$5), "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(1=IF(ISBLANK($B$2:$E$5), "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(1={3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
=IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1;2;3;4})
and returns
{FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}
Step 7 -Get smallest value
SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1)
becomes
SMALL({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE},1)
and returns 2. This is the row number we need to get the correct value.
Step 8 - Extract smallest value
The following steps calculate the column number needed to get the correct value.
MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1))
becomes
MIN({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2})
and returns 1.
Step 9 - Extract array from the correct row
Get array needed in the first argument in the INDEx function.
INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)
becomes
INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)
The following calculation returns the row number, I have already shown that calculation in steps 1 to 7.
INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)
becomes
INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},2,,1)
and returns {"", 10, 1, 8}
Step 10 - Calculate relative column number
MATCH(MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1),0)
becomes
MATCH(1,{"", 10, 1, 8},0)
and returns 3. This is the column number needed to get the correct value.
Step 11 - Get value
=INDEX($B$2:$E$5,SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),MATCH(MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1),0),1)
becomes
=INDEX($B$2:$E$5,2,3)
and returns "Apple" in cell B8.
Get excel *.xls file
extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-4.xls
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Excel categories
23 Responses to “Extract unique distinct values A to Z from a range and ignore blanks”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Awesome resource! Using Excel 2003 SP3 if I blank out any of the first 6 values (left to right / top to bottom) it turns all the cells to ZERO. Any suggestions?
Thanks! I have updated the formula and the attached excel file.
I'm using this code with Excel 2003 SP3 and i'm getting #NUM! in the fields where it should be blank.
The column where the content is being pulled from has been defined as a list (Property) and it also has data validation to pull information from a list in a different sheet so that the users can't input a "property" that isn't on the list.
Is this causing the #NUM! error in the distinct list? Is there a way around it?
Below is the modified code.
=INDEX(Property, SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "0, "", COUNTIF(Property, "<"&Property)+1)), INDEX(IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), , 1), 0), 1)
darzon,
I am not sure if wordpress removed any "greater than" or "less than" signs from your code.
Here is what it should look like:
=INDEX(Property, SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), MATCH(MIN(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)>0, "", COUNTIF(Property, "<"&Property)+1)), INDEX(IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), , 1), 0), 1) The code above does not remove #NUM errors. I can´t create a formula that removes #NUM error in excel 2003. I have more than seven levels of nesting. If you upgrade to excel 2007 or later versions you can use IFERROR() function.
hi,
I want to know how to find out frequency of each word from a group of sentences.
Eg.
Amit is a good boy.
He works with XYZ.
Anil is good boy.
Here we have 3 sentences. Result should be something like this:
Amit -1
is - 2
a -1
good - 2
boy -2
He -1
Works -1
with - 1
XYZ -1
Please help me out to get this result. after looking your multiple post i thought i split sentences into different columns (txt to columns option with blank)and define tbl range and work accordingly but not getting proper results.
Please advise.
Thanks,
Amit
Amit,
read this post:
Excel udf: Word frequency
Thank you so much Oscar.
Regards,
Amit
Hello,
I know this formula works to create a unique list and it works extermely well. However, in my situation, I have some duplicate values in my range and I would actually like to create a list of all the values sorted. (if they are duplicates, they can just be listed twice or thrice). Would you be able to help me with that?
Thank You,
Harsh
Harsh,
Array formula:
=INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF(tbl, tbl)<=1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1))=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF(tbl, tbl)<=1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1)), INDEX(IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), SMALL(IF(SMALL(IF((COUNTIF(tbl, tbl)<=1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1))=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1) Get the Excel file extract-duplicates-sorted-alphabetically-removing-blanks-from-a-range.xls
Hello Oscar,
Thank you for your help! However, I think I was not clear in my earlier inquiry. What I meant to ask was i wan't a complete list of all the values in the array (not just the duplicates). and if banana exists twice in the array, then in the produced list banana would be listed twice. Would that be possible?
Thank You,
Harsh
Harsh,
Read this:
https://www.cpearson.com/excel/MatrixToVector.aspx
Is there way to modify this formula so it sorts by occurrence versus alphabetically?
Thank you for your assistance!
I should add descendingly.
Very useful material here. Thanks!
However, my range name is discontinuous (e.g. tbl = A1:A6 and C1:C6). It seems the formula does not work in this case.
Would e brilliant if it would work.
Marius
Marius,
Check out the Filter unique distinct values from multiple sheets add-in.
https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#addin
It can use discontinuous ranges but unfortunately the add-in does not sort the values.
Hi Oscar, super tutorials as always.
Can you please update the formula to show blanks "" where iserror (to hide #NUM! cells)?
My application: I have 3 sheets with blanks and different abbreviations.
In a separate sheet I want to generate a Legend table (an alphabetic list of unique values, no blanks, dynamically updated).
I think this is a good start as a formula for my project.
Also, after having generated the "Legend" of all abbreviations used in those 3 sheets, I wish to have each abbreviation explained in the adjacent column (from an existing comprehensive list, which is defined as a named range).
Should a simple Vlookup do the trick?
Hi OP,
I have can see that this formula works well for the purpose its intended, how ever is it possible to replace #NUM! with a blank i.e. "". I tried the usual iserror method but it does not work is a array formula.
As there's been a few asking how to resolve same issue, I just thought I post my method, it involves a helper column I know.. I know) but at lease I was able to move on with the work I needed to do.
I created a the unique sorted list using the formula in this article, and next to it added a helper column that gave a true or false to the value in cell B8.
in Cell C8 I put in:
=IF(ISNONTEXT(B8)=TRUE,"",B8)
This will now list all test values sorted as I originally wanted.
Excellent Formula Oscar.
Hello Everyone,
Oscar, thank you for making this guide! I'm having some difficulty altering this formula to read a row of data. I figured I could replace instances of ROW( with COLUMN( but this is not working. Does anyone have any thoughts?
Thanks for the work on these formulas. I have this working on range over 6 columns, however I only need the unique values of data in this range if another column matches data entered into a cell.
This is a formula that worked for a single column (but not 6 columns)
=INDEX(WLDRI, MATCH(0, IF((PKG=$C$2),COUNTIF($R$2:R2,WLDRI), ""), 0))The key here is I want unique values returned for columns B3:G if column A3:A matches data in C2.
Thanks again.
Man you're awesome. Your formula work pretty soft. Something I would like to ask is wether you can give some piece of advice about how to learn Excel. Of course I have to practice utterly a lot, but you can tell me like certain steps (you have to start by this, and after that you have to do this, etcetera); something general (I would appreciate to you Mr Cronquist).
Take care
Regards
Sergio Bautista
In testing the formula shown in this article for "Extract a unique distinct list sorted alphabetically and ignore blanks from a range" shown below, I found that the formula breaks if you have more than 2 entries of numbers even though they're entered as '1, '2. As soon as you enter a third such value in the tbl, the next value repeats in the distinct list from that point to the end of the list array. If that can be solved, that is exactly what I need.
Thank you!!
=INDEX(tbl, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "0, "", COUNTIF(tbl, "<"&tbl)+1)), INDEX(IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1) + CTRL + SHIFT + ENTER