Pull data if columns equal a range

**john0305** · 01-16-2012, 02:51 PM

Good Day everyone,

I've never been good at creating my own formulas so I was hoping someone might know what I'm trying to do. I'll attach a sample excel of an empty spreadsheet I'll be using (In a normal situation that spreadsheet would be filled with over 7000 line number)

What I'm looking to do is the following:

Look at column G and if it is less than or equal to 60 and if that same item (column B) has one over 200 and they are the same item based on column B then list it in another tab for each one that falls under that criteria.

So basically I'm trying to see which items are both fairly new within 60 days and if I already have an item that's fairly old over 200 days of that same description from column B.

I hope I'm making sense lol

**NBVC** · 01-16-2012, 03:13 PM

Try this:

In Sheet1, J2 enter helper formula:

=IF(AND(G2<200,COUNTIFS($A$2:$A$6,A2,$G$2:$G$6,">=200")>0),COUNT(J$1:J1)+1,"")

copied down

in K2 enter:

=Max(J:J)

Then in Sheet2, A2 enter:

=IF(ROWS($A$2:$A2)>Sheet1!$K$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$2:$A2),Sheet1!$J:$J)))

copied down as far as you need and across the columns to get all data.

Is that what you needed?

**john0305** · 01-16-2012, 04:26 PM

I think it's part, I got it doing something once but I think it's not exactly working for what I was thinking.

I attached a better example of the information I also included the formula you wrote down in it.

My normal working file can be up to 7500 line numbers so what I'm trying to do is have a formula that will analyze all the same items (Column B) and check which of the like ones are under 60 days and which are over 150 then print that list.

Do you think this would have to be done individually by each group of items? Can it check the entire worksheet and separate it all? I basically only want to see items that are less than 60 but also 200 IF it's the same item, because you'll notice on my new attachment some parts are 42 days old but that same part is also 600 days that info is what I want to capture but I want to capture it for item 1, 2, 3 and so forth. For each different part I want to capture the same info if it matches that criteria if not it won't show that part.