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

BMcHale

New Member
Joined
Sep 26, 2017
Messages
18
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the board.

2 ideas.

First look at this sheet:

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>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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:

ABCD
1ListPrefixPrefix
2N12111N12A33
3M14999M14
4N12987N12
5A33001A33
6N33444N33
7N12555N12
8A33888A33
9
10

<tbody>
</tbody>
Sheet15

Worksheet Formulas
CellFormula
B2=LEFT(A2,LEN($D$2))

<tbody>
</tbody>

<tbody>
</tbody>




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!
 
Last edited:
Upvote 0
Great,

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

Billy.
 
Upvote 0
Great,

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

Billy.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top