lookup compare return list

[96HawkCnvt]

I need some help. Going to try to explain the best way that I can. Refer to the attached example sheet which represents an inventory of different parts made by different companies.

What I want to pull from this spreadsheet is a list of identical parts(size, color, material match) that are made by company A AND/OR company B IF they have >250 pcs in stock AND IF Company C has qty. zero in stock. The result should show the part number or better yet, all info in that row.

So referring to the attached sheet, row 10 (company A) shows a part that matches the part in line 30 (company C). Company A has 500 pcs in stock and Company C has 0 in stock. So, that should highlight row 10 or populate a list with data from row 10 since it meets the criteria.

I suspect this is relatively easy for the experts here, but since I don't have much experience with this, I'm not sure the best way to go about this. The real spreadsheet that I need to sort out is thousands of rows so the ability to pull this data out would be very helpful.

Thanks!
Todd

[teylyn]

Hello,

there are probably other ways to do this, but have a go at this:

In G3 enter the formula

Please Login or Register  to view this content.

Copy down. Enter a column label in G2. Turn on Autofilter and show only the rows with a 1 in column G.

cheers,

[96HawkCnvt]

Hello,

there are probably other ways to do this, but have a go at this:

Thanks! I'll give it a shot.

Todd

[96HawkCnvt]

OK, I apologize for the false start but really I need to come up with something more comprehensive to really get what I want. I've attached a test file that is much closer to the real data that I will be reviewing.

I would like to be able to do the following with the attached spreadsheet:

I want to be able to find parts from company A, B or C that are either not found in the spreadsheet at all for Company D or they show up but have a significantly higher quantity than Company D. I would like to have a form that would allow me to select which columns are compared to determine if there is a match. And on this form I would also like to be able to select a quantity greater than X for Company A, B or C and less than X for Company D. Note: The spreadsheets that I will be working with will vary from several hundred rows to over 30,000 rows.

As an example, I might select in the form:

1.) Match column D, E and K (I would want to have the option of selecting any columns).
2.) Quantity for Company A, B or C must be at least 10,000
3.)Quantity for Company D must be less than 1,000
4.)Check box to return results if #1 & #2 above are true, but there is not a row found with matching data for Company D. I would like 3 choices here. A.) Yes, B.) No and C.)Results Only If this is true.

Note for #1 above - If the company in Column C is Company A, B or C, then look for a match of the selected columns with another Row that has Company D in Column C.

Ultimately I want the result to be some way of highlighting the matching rows or outputting them to another sheet or something like that.

I hope that is clear. If not, please ask questions. Thanks in advance for help!
I do also have Access if that would work better to import this data. But I know very little about working in Access.

Todd