Vlook up to return like identifiers with an associated value greater than or equal to specified amount.

[JMC57]

I have data in columns A-O. I am looking to build code that compares items with the same common value (classification code) in column B and returns the data in rows A-O for only those items that have a value in Column O of greater than or equal to $0.15 for the like value (classification code) in column B in another worksheet I have over 60,000 lines of data to evaluate monthly. Any help will be appreciated. Thank you in advance.

A</SPAN>	B</SPAN>	Data in Cols C-N</SPAN>	O</SPAN>
DIGOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.05</SPAN>
DIGOXIN 1000 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.12</SPAN>
DIGOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.20</SPAN>
DIGOXIN 1000 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.16</SPAN>
LANOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.22</SPAN>
LANOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.15</SPAN>
DIGOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.08</SPAN>
DIGOXIN 1000 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.10</SPAN>
DIGOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.25</SPAN>
LANOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.33</SPAN>
DIGOXIN 100 .125</SPAN>	18</SPAN>	Data in Cols C-N</SPAN>	$0.15</SPAN>
DIGOXIN 100 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.22</SPAN>
DIGOXIN 1000 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.15</SPAN>
DIGOXIN 1000 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.08</SPAN>
LANOXIN 100 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.10</SPAN>
LANOXIN 100 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.25</SPAN>
DIGOXIN 100 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.33</SPAN>
DIGOXIN 100 0.25</SPAN>	19</SPAN>	Data in Cols C-N</SPAN>	$0.15</SPAN>

<TBODY>
</TBODY>

</SPAN>

 

[bschwartz]

(additional columns left out to save space)
Excel 2010

	A	B	C
DIGOXIN 100 .125	Data in Cols C-N
DIGOXIN 1000 .125	Data in Cols C-N
LANOXIN 100 .125	Data in Cols C-N
LANOXIN 100 .125	Data in Cols C-N
DIGOXIN 100 .125	Data in Cols C-N
LANOXIN 100 .125	Data in Cols C-N
DIGOXIN 100 .125	Data in Cols C-N
DIGOXIN 100 0.25	Data in Cols C-N
DIGOXIN 1000 0.25	Data in Cols C-N

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]18[/TD]

</tbody>

Output​

I made two assumptions:

1) Worksheet with data is called Data Dump.

2) On the output worksheet, you are returning all records when column B of Data Dump is equal to whatever is in A1 (in this case 18).

Confirm this with Ctrl+Shift+Enter instead of just Enter. Then fill down as far as you need to go, and to the right:

=IF(ROWS(A$2:A2)<=SUMPRODUCT(--('Data Dump'!$B$2:$B$99999=$A$1),--('Data Dump'!$O$2:$O$99999>=0.15)),INDEX('Data Dump'!A$2:A$99999,SMALL(IF('Data Dump'!$B$2:$B$99999=$A$1,IF('Data Dump'!$O$2:$O$99999>=0.15,ROW('Data Dump'!$A$2:$A$99999)-ROW('Data Dump'!$A$2)+1)),ROWS(A$2:A2))),"")

 

[JMC57]

Thank you. Are you aware of VBA code that can do this?

 

[bschwartz]

Sorry, I'm not very familiar with VBA. But there are many others on here who are experts. I believe they can assist you with a VBA question.

As an aside, if you are only looking for a specific type of solution, aka VBA, you should note that in your post, so people do not waste time on an unacceptable type of solution.

 

[JMC57]

Thanks - I do apologize for not asking for the VBA in the original post. It came as am afterthought. Your response has allowed me to complete my project and it is very much appreciated and an easy solution. Thanks again for your help. </SPAN>

 

[bschwartz]

That's alright. I was just pointing out for next time. I'm glad I was able to help.