Scanning down a column and making a list of items matching given criteria

[BMcHale]

Hi, I'm trying to create a way to look at a column of Alpha-numeric product codes and fill another column with those items from the first one that that satisfy a particular condition

For example if the "population" column has a series of cells containing.....

N12111
M14999
N12987
A33001
N33444
N12555
A33888 etc.

and I want to pick out in the first cell the first item in the column that begins with "N12", then in the next cell I want the 2nd one, and so on.

The result in that case would be a list ...

N12111
N12987
N12555

and if I changed the criteria to looking for "A33" I'd get

A33001
A33888

any ideas?

 

[Eric W]

Welcome to the board.

2 ideas.

First look at this sheet:

	A	B	C	D
List	Prefix	List
N12111	N1	N12111
M14999	N12987
N12987	N12555
A33001
N33444
N12555
A33888

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]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]
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]



Put your list in column A, and the prefix you want in C2. Then put "List" in D1, and enter the formula in D2. Change the ranges to match your sheet, confirm it by pressing Control+Shift+Enter, then copy down the column. Your list will dynamically change when you change C2.


Option 2: Look at this sheet:

	A	B	C	D
List	Prefix	Prefix
N12111	N12	A33
M14999	M14
N12987	N12
A33001	A33
N33444	N33
N12555	N12
A33888	A33

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet15​

[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"]=LEFT(A2,LEN($D$2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Your list is in column A again. Now put "Prefix" in B1 and D1. Put the B2 formula in and drag down. Put the prefix you want in D2. Now select columns A:B, click on the Data tab, Advanced Filter, select Filter in place or Copy to another location, and put $D$1:$D$2 in the Criteria Range box. That should also work.

Hope this helps!

 

[BMcHale]

Great,

Thank you so much, I must have spent 2 hours yesterday trying to find a solution to this.

Billy.

 

[BMcHale]

Great,

Thank you so much, I must have spent 2 hours yesterday trying to find a solution to this.

Billy.

 

[Eric W]

You're welcome.