VBA or Excel to Auto Filter

J

JMC57

Board Regular
Joined
Apr 24, 2012
Messages
118
I have a list of numbers referencing an item in column B of sheet 2 that can be found in col A sheet1. I want to auto filter column A in Sheet 1 for rows that contain this number in col A Sheet 1. The number can appear multiple times in column A Sheet 1. <o:p></o:p>
I tried VLook up. However, it does not return each occurrence of the number. There are 6000 rows in sheet 1. An item can appear up to 200 times or not at all. <o:p></o:p>
Hopefully someone can help with a VBA code or excel formula to solve this. <o:p></o:p>
Thanks!<o:p></o:p>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Thanks- I used the following

=IF(COUNTIF(Sheet2!$A$1:$A$6000,$A2)>=COLUMNS($B2:B2),INDEX(Sheet2!$B$1:$B$6000,SMALL(IF(Sheet2!$A$1:$A$6000=$A2,ROW(Sheet2!$A$1:$A$6000)-ROW(Sheet2!$A$1)+1),COLUMNS($B2:B2))),"")

The only problem is that in when looking for a number such as 3407 from col A Sheet 1 it finds a number tha contains 3407 in this case 5407 and returnds the data. How do I chagne this for an exact match?

Thanks
 
Upvote 0
I am looking for the GCN number from sheet 1 col A in Sheet 2 col A and return results. In sheet 1 B through L. Also, there should be several other items returned other than lidocaine. Thank you for taking the time to look at this.

This is sheet 1

GCN</SPAN>
7801</SPAN>
29893</SPAN>LIDOCAINE 30 ML 2</SPAN>191306 </SPAN>50383078130</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
40646</SPAN>
62828</SPAN>
7990</SPAN>
8078</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
65598</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
13721</SPAN>
7857</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
65392</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
39498</SPAN>
58916</SPAN>
44226</SPAN>
61667</SPAN>
39806</SPAN>
52911</SPAN>
48618</SPAN>
53407</SPAN>
48618</SPAN>
48557</SPAN>
44694</SPAN>
11689</SPAN>
48541</SPAN>
16008</SPAN>
64354</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
38434</SPAN>
53473</SPAN>
8079</SPAN>
7905</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
18368</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
62658</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
66373</SPAN>
7984</SPAN>
24456</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7858</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
12076</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
44695</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
39105</SPAN>
70083</SPAN>
67691</SPAN>
39106</SPAN>
46793</SPAN>
31186</SPAN>
7760</SPAN>
23551</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
48544</SPAN>
48543</SPAN>
7964</SPAN>
48547</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
59670</SPAN>
19734</SPAN>
62065</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
30796</SPAN>
7772</SPAN>
45393</SPAN>
24055</SPAN>
7920</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7917</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7917</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7771</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7855</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7856</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
38587</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7985</SPAN>
7986</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
7986</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
47612</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
26869</SPAN>
61667</SPAN>LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
48554</SPAN>
20845</SPAN>
LIDOCAINE HCL 10X5 ML 2</SPAN>866044 </SPAN>17478071110</SPAN>LIDOCAINE HCL </SPAN>52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. </SPAN>HOS</SPAN>
SHEET 2
GCN NumberRX Item DescRX Item NumNDC Current CodeUSAN Generic NameAHFS Therapeutic Code - Tier 1LOB Code
3407LIDOCAINE 30 ML 2191306 50383078130LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE 30 ML 2191306 50383078130LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE 30 ML 2191306 50383078130LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE HCL 10X5 ML 2866044 17478071110LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE HCL 10X5 ML 2866044 17478071110LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE HCL 10X5 ML 2866044 17478071110LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
3407LIDOCAINE HCL 10X5 ML 2866044 17478071110LIDOCAINE HCL 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7739SOD CHL 15 ML 5698498 17478062312SODIUM CHLORIDE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7739SOD CHL 15 ML 5698498 17478062312SODIUM CHLORIDE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7739SOD CHL 15 ML 5698498 17478062312SODIUM CHLORIDE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONAK 15 ML 2.5862870 17478006412HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONIOVISC 15 ML 2.5278015 17238061015HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONIOVISC 15 ML 2.5278015 17238061015HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONIOVISC 15 ML 2.5278015 17238061015HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS
7751GONIOVISC 15 ML 2.5278015 17238061015HYPROMELLOSE 52 - EYE, EAR, NOSE AND THROAT (EENT) PREPS. HOS

<COLGROUP> <COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6948" width=190><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 48pt" span=2 width=64> <TBODY>
</TBODY>

<TBODY>
</TBODY><COLGROUP> <COL><COL><COL><COL><COL><COL><COL> </COLGROUP>
 
Upvote 0
Yes- But is returing the value of 3407. I assume it is getting this form 5347 in sheet 1
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
152
AlfredDB
A
Z
  • Question
Could you please help me about the vba code?
Replies
1
Views
141
Joe4
Joe4
T
  • Question
Create and auto-populate comments
Replies
3
Views
87
rlv01
R
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
196
harmless92
H
R
  • Question
Insert new rows based on user input
Replies
0
Views
95
remmuS24
R

Forum statistics

Threads
1,215,613
Messages
6,125,836
Members
449,266
Latest member
davinroach

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top