****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]List[/TD]
[TD="align: right"][/TD]
[TD]Prefix[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]N12111[/TD]
[TD="align: right"][/TD]
[TD]N1[/TD]
[TD]N12111[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]M14999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12987[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12555[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A33001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]N33444[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]A33888[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
</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.
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]List[/TD]
[TD="align: right"][/TD]
[TD]Prefix[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD]N12[/TD]
[TD]N12555[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]M14999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12987[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12111[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A33001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]A33888[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]N12111[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]M14648[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]List[/TD]
[TD="align: right"][/TD]
[TD]Prefix[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]N12111[/TD]
[TD="align: right"][/TD]
[TD]N1[/TD]
[TD]N12111[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]M14999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12987[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12555[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A33001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]N33444[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]A33888[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet14
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
</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.
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]List[/TD]
[TD="align: right"][/TD]
[TD]Prefix[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD]N12[/TD]
[TD]N12555[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]M14999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12987[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]N12111[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A33001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]N12555[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]A33888[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]N12111[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]M14648[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]N12987[/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet14
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]