Unique Value Nightmare!

[MDX2013]

Hi All! I am having trouble with formulas. One formula i have wont work, and I need a creative formula.

Columns A and B: I have a long list of values, some will be repeated up to three times, some wont be repeated at all. The number of values in the list will vary every tie I use the spreadsheet, so I have chosen formula to look up to cell A99. I need to create a new column of just the unique values. This formula I am having trouble with. See below.

Column C: After I get the unique values, then I need to put them in ascending order. (I have this figured out)

Column D: NEED FORMULA: I need a new column with two rows in between the ascending values. I have no clue how to do this part.

Column A:
17091780-02A
17091555-01A
17091555-01A
17091555-01A
17091555-01ADUP
17091555-01ADUP
17091555-02A
17091555-02A
17091559-01A
17091559-01A
17091559-02A
17091559-02A
17091559-02A

Column B:
17091780-02A
17091555-01A
17091555-01ADUP
17091555-02A
17091559-01A
17091559-02A
Formula used as an array: B2=
=IFERROR(INDEX($A$2:$A$99,MATCH(SMALL(NOT($A$2:$A$99="")*IF(ISNUMBER($A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99)+SUM(--ISNUMBER($A$2:$A$99))),ROWS($A$2:A2)+SUM(--ISBLANK($A$2:$A$99))),NOT($A$2:$A$99="")*IF(ISNUMBER($A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99)+SUM(--ISNUMBER($A$2:$A$99))),0)),"")Trouble with the formula: it is not returning the first value from column a "17091780-01A". I am not sure if this because it is already a unique value or if there is a error in the formula.




Column C:
17091555-01A
17091555-01ADUP
17091555-02A
17091559-01A
17091559-02A
17091780-02A

Final Column:
17091555-01A


17091555-01ADUP


17091555-02A


17091559-01A
(And so on...)

I know this is a lengthy question, but I hope someone will be able to step to the challenge

 

[Rick Rothstein]

Sure, consider:

	A	B
Column A	Column B
a	a
a
b
b	b
c
c
c	d
d
d

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=INDEX(A:A,3*INT(ROWS($B$1:$B2)/2)-1)&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I gave a similar, but slightly different answer to this question in the new thread the OP started on it here...

https://www.mrexcel.com/forum/excel-questions/1038206-removing-rows-data.html#post4983483

 

[Eric W]

GMTA!