Array Formula - Doesn't Repeat

[laurenm]

I found an array formula online that helped me get a result for the 1st, 2nd, 3rd, etc. non-blank cell in a column. Formula: =INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr>A$80)+(100*(A$1:A$80="")), 1))&"" (the "1" at the end of the formula determines which non-blank result to output). I used this formula 10 times to get the first 10 non-blank cells in column A. However, I don't want to get any repeats - for example, if the 3rd non-blank cell and the 5th non-blank cell are both "California" then I don't want the formula to register the 5th non-blank cell, and it will move on to the next non-blank cell. Is there a way to update this array formula for this? I can't figure out how to upload the doc, but the column data, formula, and results I get/want are below (don't want California to show up because it is a repeat): <strike></strike> Data Formula Current Results Results I want California INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr style="color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; font-size: small; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial;">A$80)+(100*(A$1:A$80="")), 1))&""<strike></strike> California California Georgia INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr style="color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; font-size: small; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial;">A$80)+(100*(A$1:A$80="")), 2))&""<strike></strike> Georgia Georgia INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr style="color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; font-size: small; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial;">A$80)+(100*(A$1:A$80="")), 3))&""<strike></strike> South Carolina South Carolina INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr style="color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; font-size: small; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial;">A$80)+(100*(A$1:A$80="")), 4))&""<strike></strike> Tennessee Tennessee South Carolina INDEX(A$1:A$80,SMALL(ROW(A$1:<wbr style="color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; font-size: small; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial;">A$80)+(100*(A$1:A$80="")), 5))&""<strike></strike> California Oregon INDEX(A$1:A$80,SMALL(ROW(A$1:​ <wbr style="background-color: rgb(255, 255, 255); color: rgb(34, 34, 34); font-family: Arial,Helvetica,sans-serif; font-size: 13.33px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; widows: 2; word-spacing: 0px;"> A$80)+(100*(A$1:A$80="")), 6))&""​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike> <strike></strike>Oregon Tennessee California Oregon <tbody> </tbody>	<strike></strike>
<tbody> </tbody>

<tbody>
</tbody>

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B
1	California	California
2	Georgia	Georgia
3		South Carolina
4		Tennessee
5	South Carolina	Oregon
6
7
8	Tennessee
9
10	California
11
12	Oregon
Sheet

 

[Fluff]

Another option

Excel 2013/2016
	A	B
1	Data	Results
2	California	California
3	Georgia	Georgia
4		South Carolina
5		Tennessee
6	South Carolina	Oregon
7
8
9	Tennessee
10
11	California
12
13	Oregon
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(INDEX($A$2:$A$13, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$13&"") + IF($A$2:$A$13="",1,0), 0)), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Courtesy of AbleBits https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/

 

[laurenm]

Thanks for the help!

 

[Fluff]

You're welcome