Loopup Values greater than a set value and place data on another sheet

[ctigers]

Hello, have 2 sheets. On sheet B I have values listed in column K. On sheet A I need to enter a formula to lookup the values in column K on Sheet B and if there are any values in K greater than 100, list that data on sheet A. Tried a VLOOKUP but could get it to work.

	SHEET A
A	B	C	D	E	F	G	H	I	J	K
SC	6/2/2014 13:45	6/2/2014 21:08	6/3/2014 17:28	6/4/2014 17:21	JTRELA	6/5/2014 9:26	MSCSSC2	6/5/2014 19:30	DCOWAN	1250
SC	6/2/2014 11:20	6/2/2014 12:42	6/2/2014 15:10	6/2/2014 15:55	MHARRIS	6/2/2014 17:43	BHUMPHRE	6/3/2014 15:40	TEJONES	252
SC	6/2/2014 10:37	6/2/2014 11:39	6/3/2014 18:30	6/3/2014 19:04	PORTIZ	6/4/2014 14:09	CHART	6/4/2014 16:19	AGUILLEB	1100
SC	6/2/2014 13:45	6/2/2014 19:57	6/3/2014 12:45	6/3/2014 13:03	JTRELA	6/3/2014 16:52	SDAWSON	6/4/2014 20:09	DCOWAN	110
	SHEET B
A	B	C	D	E	F	G	H	I	J	K
SC	6/2/2014 12:29	6/2/2014 17:28	6/2/2014 18:02	6/3/2014 14:19	JTRELA	6/3/2014 15:53	SDAWSON	6/5/2014 9:00	AGUILLEB	98
SC	6/2/2014 11:20	6/2/2014 20:10	6/3/2014 8:46	6/3/2014 15:25	JTRELA	6/4/2014 15:58	SDAWSON	6/5/2014 15:15	AGUILLEB	70
SC	6/2/2014 11:20	6/2/2014 13:02	6/2/2014 18:48	6/3/2014 9:20	JTRELA	6/3/2014 9:29	JTRELA	6/3/2014 12:35	DPEPPERS	55
SC	6/2/2014 13:45	6/2/2014 21:08	6/3/2014 17:28	6/4/2014 17:21	JTRELA	6/5/2014 9:26	MSCSSC2	6/5/2014 19:30	DCOWAN	1250
SC	6/2/2014 11:20	6/2/2014 12:42	6/2/2014 15:10	6/2/2014 15:55	MHARRIS	6/2/2014 17:43	BHUMPHRE	6/3/2014 15:40	TEJONES	252
SC	6/2/2014 13:45	6/2/2014 19:57	6/3/2014 12:06	6/3/2014 14:07	JTRELA	6/3/2014 15:55	SDAWSON	6/5/2014 9:09	CMACOMSO	75
SC	6/2/2014 10:37	6/2/2014 11:39	6/3/2014 18:30	6/3/2014 19:04	PORTIZ	6/4/2014 14:09	CHART	6/4/2014 16:19	AGUILLEB	1100
SC	6/2/2014 11:20	6/2/2014 13:02	6/3/2014 14:53	6/3/2014 16:03	ACLINKSC	6/3/2014 16:09	BHUMPHRE	6/5/2014 10:58	LHAMMOND	11
SC	6/2/2014 13:45	6/2/2014 19:57	6/3/2014 12:45	6/3/2014 13:03	JTRELA	6/3/2014 16:52	SDAWSON	6/4/2014 20:09	DCOWAN	110

<tbody>
</tbody><colgroup><col><col span="4"><col><col><col><col><col><col></colgroup>

 

[sijpie]

No, VLOOKUP can only be used to get data in the range right of (and including) the column beeing looked up.

I am assuming you want to use a macro to move down column K and then transfer the row to Sheet A

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyOver100()<br>    <SPAN style="color:#00007F">Dim</SPAN> rF <SPAN style="color:#00007F">As</SPAN> Range, r1st <SPAN style="color:#00007F">As</SPAN> Range, rOut <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsA <SPAN style="color:#00007F">As</SPAN> Worksheet, wsB <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsA = Sheets("SheetA")<br>    <SPAN style="color:#00007F">Set</SPAN> wsB = Sheets("SheetB")<br>    <br>    <SPAN style="color:#007F00">'set the output range to the first empty row in Sheet A</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rOut = wsA.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rF = wsB.Columns("K").Find(what:="???")<br>    <SPAN style="color:#00007F">Set</SPAN> r1st = rF<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rF <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#007F00">'copy the row to sheet</SPAN><br>        rOut.Resize(1, 11).Value = rF.Offset(0, -10).Resize(1, 11).Value<br>        <SPAN style="color:#00007F">Set</SPAN> rOut = rOut.Offset(1, 0)<br>        <SPAN style="color:#00007F">Set</SPAN> rF = wsB.Columns("K").FindNext(after:=rF)<br>        <SPAN style="color:#00007F">If</SPAN> rF.Address = r1st.Address <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br>    <SPAN style="color:#007F00">'clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsA = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsB = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rF = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> r1st = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rOut = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Aladin Akyurek]

What values from SHEET A are you looking up?

 

[Fazza]

If you have headers in SHEET B, a query table is one way. Via menu ALT-D-D-N.

Or an advanced filter is handy for this sort of task but can only copy to the same sheet as the source data. It would require an extra step, or a VBA approach, to have the data go to sheet A.

HTH

 

[ctigers]

This works great, have one question. How can I get it run when I open the sheet?

 

[sijpie]

When in the VBA editor right click on Workbook under your workbook name in the top lefthand panel. The workbook module will open. In the left drop down menu above the edit area select workbook and in the right drop down select open.
A skeleton macro is written which you need to complete, so just put the name of the macro in here.
Press F5 to see if it works.
Then save close and open to see if no errors appear.

 

[ctigers]

Everything working great, thank you for all the help,Gene

 

[Fazza]

By the way, if using a query table no program code is required. You just set the query's property to run on file open. Right click from the table and look for the options, set to refresh on file open. cheers