I have two spreadsheets. Spreadsheet 1 has a series of sample barcodes in the format LB###### where # is a number (duh) in the first column, and corresponding numerical data in the second column. Spreadsheet two also has barcodes of the same format in column 1 with several columns containing other related data. Some of the barcodes appear in both spreadsheet 1 and 2.
Is there an easy way for me to quickly find the barcodes in 2 that also appear in 1 and somehow mark those rows in both sheets?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You can simply use countif to do what you are looking for that is...
=countif(Range to check,value to check)
This will give a return 0 if the value is not present or will give you the answer the same value is present as in how many number of times its there.
Enclosed is the workbook for your reference which tells about how vlookup works.
Let me know in case if you need any further help over this.
Saurabh...
You can do the following:
1. Put the following formula in a new column in Sheet2. You can hide this column later if you do not wish it to be seen. In my example, I am putting this formula in cell E2. The Barcode is in column A (cell A2) and the sample barcodes are in a sheet called "Sample Barcodes" in range A2 to A4. Note the $ signs in the addresses before the row and column addresses.
=COUNTIF('Sample Barcodes'!$A$2:$A$4,A2)
This will give you the number of times that particular barcode exists in your sample barcode sheet.
2. Now Copy the formula down for the rest of your rows.
3. Select all the rows containing data and go to Format -> Conditional Formatting.
In Condition 1 select "Formula Is" and type in the following in the text area:
=$E2>0
Click on the Format button and select the formatting style you want for highlighting the rows containing barcodes which exist in the sample barcode sheet.
Click on OK and then OK again.
Business Accounts
Answer for Membership
by: razauPosted on 2009-10-28 at 09:43:58ID: 25685244
Use following formula in sheet 1
=VLOOKUP(B1,Sheet2!B$1:B$6
above formula look value from sheet1 to sheet 2 if found matching code from sheet 2 it will return value.