Array Formula - Doesn't Repeat

[laurenm]

[TABLE="width: 0"]
<tbody>[TR]
[TD]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>[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Formula[/TD]
[TD]Current Results[/TD]
[TD]Results I want[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]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>
[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]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>
[/TD]
[TD]Georgia[/TD]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]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>
[/TD]
[TD]South Carolina[/TD]
[TD]South Carolina[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]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>
[/TD]
[TD]Tennessee[/TD]
[TD]Tennessee[/TD]
[/TR]
[TR]
[TD]South Carolina[/TD]
[TD]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>
[/TD]
[TD]California[/TD]
[TD]Oregon[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

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>
[/TD]
[TD]<strike></strike>Oregon
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tennessee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"][TABLE="width: 1512"]
<tbody>[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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