Solved

How do I filter rows on excel sheet based on another sheet

Posted on 2011-03-15
11
312 Views
Last Modified: 2012-05-11
Hi,

I have two catalog sheets based on SKU (product id number) I also have another worksheet with some of these. How do I filter rows from the first one based on the skus from the second one.

thanks
0
Comment
Question by:jecommera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35138262
Sounds like you need an Advanced Filter using formulaic criteria. Can you post a sample showing the actual layout of the two?
0
 

Author Comment

by:jecommera
ID: 35138310
worksheet1

<id><value1>

worksheet2

<id><row>

So I need list of worksheet2 rows where same id in worksheet1 and value1=<null>

thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35138351
That's not enough info really. Can you post a small sample showing what you have and exactly what output you expect.
From what you've said it looks more like a lookup formula than any sort of filter currently.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jecommera
ID: 35138386
ID                      IMAGE_original_1
30004910      image.jpg
30004911

ID                     name
30004910      cap
30004911        jumper


      


0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35138456
When I say sample, I mean a sample workbook, so I can see what you want as the outcome. At the moment I'm thinking you just want a formula in the first sheet like this in C2:
=IF(B2<>"",IF(ISNA(MATCH(A2,Sheet2!$A:$A,0)),"",VLOOKUP(A2,Sheet2!$A:$B,2,False)),"")
0
 

Author Comment

by:jecommera
ID: 35138500
Sorry I cannot post the worksheet as it contains some private data.

I dont want a value in a cell.

I need rows from worksheet2 where the same ID in worksheet1 does not have an image (i.e. the value in the second column of the row is null
0
 

Author Comment

by:jecommera
ID: 35138509
also all the data in worksheet2 is contained in worksheet2
0
 

Author Comment

by:jecommera
ID: 35138559
woops also all the data in worksheet2 is contained in worksheet1
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35138589
Add a new column to worksheet2 using a formula like:
=INDEX(Sheet1!B:B,match(A2,Sheet1!A:A,0))=""

and then filter sheet2 using that column and filtering for TRUE values.
0
 

Author Comment

by:jecommera
ID: 35139210
Great Work !!!

thanks for your help
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139247
Glad to help. :)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question