Unique list between two sides within a cell with formula

[shimon.amar]

Hi Fellows,

I have an issue trying to compse a formula to remove duplicates from a coulumn based on content on cells

for example -

I have a list contains something like that

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Combinations[/TD]
[/TR]
[TR]
[TD]B00001-C00003[/TD]
[/TR]
[TR]
[TD]B20002-C30003[/TD]
[/TR]
[TR]
[TD]C00003-B00001[/TD]
[/TR]
[TR]
[TD]C30003-B20002[/TD]
[/TR]
</tbody>[/TABLE]


I want to create by formulas a unique list while for me cell A2 and A4 are basically the same becuase it is just "shifting" between the sides distinguished by the "minus" sign. also cells A3 & A5 are the same.

Thanks in advance.

 

[jimrward]

Is it exactly the same format for the data 6 characters hyphen 6 characters throughout your data

 

[shimon.amar]

yes indeed

Is it exactly the same format for the data 6 characters hyphen 6 characters throughout your data

 

[Eric W]

Perhaps:

	A	B	C	D
Uniques
B00001-C00003
B20002-C30003

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Combinations[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]B00001-C00003[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]B20002-C30003[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]C00003-B00001[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]C30003-B20002[/TD]
[TD="align: right"][/TD]

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(COUNTIF($C$1:$C1,$A$2:$A$10)=0,IF(COUNTIF($C$1:$C1,RIGHT($A$2:$A$10,6)&"-"&LEFT($A$2:$A$10,6))=0,ROW($A$2:$A$10)-ROW($A$2)+1))),1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]