Filtering to rows of a list by inputting value of the first row

Hello All,

I have a large document with three collumns formatted like the following

A1 B1 C1
A2 B2 C2
A3 B3 C3

But much larger. I need to copy specific rows based on an order number which is in the first column. For example I would input the value of A2 and A3 and all it would leave is:

A2 B2 C2
A3 B3 C3

Is this possible in excel?

Thanks,
Sean

Why don't you just apply Autofilter to column A, and select the items you want from the filter drop-down?

Hope this helps.

Pete

Hi Pete,

Thanks for your response. I have tried this but it looks like you can only filter for one entry/row when I want to filter multiple rows? Is this possible with AutoFilter?

Cheers,
Sean

Using the filter drop-down on column A, which shows you all the values in that column, you can select any number of those values to be displayed.

Hope this helps.

Pete

Thanks again for your response Pete. It seems like you have to scroll down the list with the filter function and select which ones you want which is very slow when you have a massive list. Really I need to be able to input/type the numbers I want to filter, is that possible?

Sean

In that case it will probably be better to use a second sheet where you can list the values that you want to filter on and have the corresponding records displayed there.

It would help if you attached a sample Excel workbook, so I can set it up for you with a couple of formulae.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

That would be much appreciated, here it is

I've set this up for you in the attached file. I've added another sheet called Filter, and used the top 9 rows (coloured yellow) where you can enter any value that you want to filter, and in any order. The display below shows the appropriate records, and I have applied conditional formatting to this so that you can easily see which field matches with your selection above.

This is achieved by using a formula in a helper column on the Raw_data sheet - I've used column E, with this formula in E2:

=IF(OR(COUNTIF(Filter!$A$1:$A$9,A2),COUNTIF(Filter!$B$1:$B$9,B2),COUNTIF(Filter!$C$1:$C$9,C2)),MAX(E$1:E1)+1,"-")

This formula is copied down to the bottom of your data, and it basically sets up a sequential numbering against those records which match the selections that you have made in the other sheet.

I have used this formula in cell A12 of the Filter sheet:

=IFERROR(INDEX(Raw_data!A:A,MATCH(ROWS($1:1),Raw_data!$E:$E,0)),"")

This can be copied into cells B12:C12, and then those 3 formulae can be copied down as far as you think you may need (I've copied to row 25 in the attached file).

Just try entering some values in the cells at the top of the sheet, and see the display change accordingly.

Hope this helps.

Pete

Hi Pete,

This is perfect, any chance you can expand this to 150 rows on the Filter sheet and make output to the right rather than below? I've tried to do this myself but messed it up.

Thanks,
Sean

Okay, see attached file. The formulae in columns E to G of the Filter sheet have been copied down to row 150, so you need to copy these down further if you expect to have more data. (In theory, you could have 150 x 3 different entries in the yellow area, or 450 rows, but I don't know if you are likely to use this many).

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hope this helps.

Pete

Hi Pete,

Is there any way to add another collumn which is an exclude list? So we can put order numbers in this list and if they are entered in the input list they are not shown on the output?

Thanks,
Sean

Can you do another mock up of this? I'm not sure exactly what you are asking for.

Pete

Please find attached

So in the filter list we would specify order numbers we do not want to show in output list, in this case 43859
would not appear.

Cheers,
Sean

I can't see an attachment - did you forget to click on the Upload button? See instructions again in Post #6.

Pete

Can you see it now?

Yes, I can, but there is no data in the Raw_data sheet, so I won't be able to see if any revised formula works correctly.

Please re-submit with some representative data and expected outcomes.

Pete

Hi Pete,

This is for a business so I have purchased some commercial points (we have another question) can you move this post to the commercial forum?

I can't do that - you will have to ask a Moderator or Admin to do that for you. You can see a list of who is on-line at the moment by clicking on the Forum button at the top of the screen - send a PM to one of them.

Hope this helps.

Pete