Unique List Generator Based on Prefix

BMcHale

New Member
Joined
Sep 26, 2017
Messages
18
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
ABCD
1ListPrefixList
2N12111N1N12111
3M14999N12987
4N12987N12555
5A33001
6N33444
7N12555
8A33888
9
10

<tbody>
</tbody>

Sheet14

Array Formulas
CellFormula
D2{=IF(D1="","",IFERROR(INDEX($A$2:$A$100,SMALL(IF(LEFT($A$2:$A$100,LEN($C$2))=$C$2,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($D$2:$D2))),""))}

<tbody>
</tbody>


<tbody>
</tbody>

</body>Hi All,

I'm using an array function to list values with a specified prefix, However the function I'm using will still obviously list duplicated values, Is there any way in which my formula can be edited, or indeed a new formula created that will give a list of unique values with a specified prefix.

Below is an example of what I'd be after and my currently used formula.


ABCD
1ListPrefixList
2N12555N12N12555
3M14999N12987
4N12987N12111
5A33001
6N12555
7A33888
8N12111
9M14648
10N12987


<tbody>
</tbody>

Sheet14

Array Formulas
CellFormula
D2{=IF(D1="","",IFERROR(INDEX($A$2:$A$100,SMALL(IF(LEFT($A$2:$A$100,LEN($C$2))=$C$2,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($D$2:$D2))),""))}

<tbody>
</tbody>


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,017
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top