How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Ryan-CTech Your Own Question

Ryan-CTech
Ryan-CTech, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 524
Experience:  Expert VBA Programmer.
18042535
Type Your Microsoft Office Question Here...
Ryan-CTech is online now
A new question is answered every 9 seconds

How do I find out if an item in one column of an Excel spreadsheet

This answer was rated:

How do I find out if an item in one column of an Excel spreadsheet is present in a column of another spreadsheet?

Customer:

Welcome To Just Answer!

Customer:

A Macro would be best. What exactly do you wish to do? :)

JACUSTOMER-qw73s3xl- :

I have two spreadsheets. I want to see if a jobname on one spreadheet is present on the other spreadsheet and vice versa.

Customer:

yeah that can be done with a Macro. Are you familiar with them?

JACUSTOMER-qw73s3xl- :

vaguely-very

Customer:

No worries, If you want I can add it to your workbook for you.

JACUSTOMER-qw73s3xl- :

awesome!

Customer:

How would you like it setup?

Customer:

What I mean by that is,..How do you want to search for said Jobname? 1 at a time?

JACUSTOMER-qw73s3xl- :

i guess we could return the items that are not present on the other worksheet?

JACUSTOMER-qw73s3xl- :

not one at a time-compare both lists and say true or false or something?

Customer:

Ok, Do you want to add some kind of Marker to it? Like change the Colors Green for Found and Red for not found?

JACUSTOMER-qw73s3xl- :

that would be cool!

Customer:

Sweet :)..

Customer:

What I need you to do is attach your file here.

Customer:

See the Paper clip icon in the tool bar

JACUSTOMER-qw73s3xl- :

both spreadsheets, correct?

Customer:

You it to attach your book

Customer:

Use..

Customer:

Sorry lol

Customer:

Yes :)

Customer:

Do you mean you have 2 Excel files? 2 separate workbooks?

JACUSTOMER-qw73s3xl- :

did you get them?

Customer:

No :(

Customer:

You might have to upload them 1 at a time

Customer:

Now we got them :)

JACUSTOMER-qw73s3xl- :

now?

Customer:

Yes sir :)

Customer:

I have them

JACUSTOMER-qw73s3xl- :

k

Customer:

Walk me thru what and where :)

JACUSTOMER-qw73s3xl- :

okay, if you open 2013wip both status WB.....Column a is the job name.

JACUSTOMER-qw73s3xl- :

Then in 130822 Cindy job report WB.... 7-29 tab,

JACUSTOMER-qw73s3xl- :

column b, job name code

JACUSTOMER-qw73s3xl- :

I wanbt to comapare the job report with the wip report

Customer:

Ok just a sec

Customer:

I dont have a Cindy report book

JACUSTOMER-qw73s3xl- :

The file is called " Copy of 130822 Cindy Job Report?

JACUSTOMER-qw73s3xl- :

you don't have that?

Customer:

these 2

Customer:

2013-08-23_170840_2013wip_both_status_jcbndrpt

Customer:

Quick question....

JACUSTOMER-qw73s3xl- :

i don't know what that first one u mentioned is. do you have it now?

Customer:

Are both of these sheets located inside the same workbook?

JACUSTOMER-qw73s3xl- :

no, but I could make that happen....

Customer:

If it wont mess with your routine it would be much better if the sheets were in the same book :)

Customer:

Instead of creating a New Workbook, create a new sheet. Then all the data is under 1 roof :)

JACUSTOMER-qw73s3xl- :

got it?

Customer:

yes sir :) Or Mam :)

JACUSTOMER-qw73s3xl- :

cindy= maam

Customer:

So I search Column A of sheet1 for a Match on the same Column of sheet 2 and turn Matches green and non matches red.? Sound good? :)

JACUSTOMER-qw73s3xl- :

no

Customer:

:(

Customer:

How would you like it :)

JACUSTOMER-qw73s3xl- :

lol...column a of sheet one with column b of sheet 2

Customer:

Oops I'm sorry lol, I didn't see Column A was hidden. My mistake :).. Give me like 5 minutes :)

JACUSTOMER-qw73s3xl- :

sure

JACUSTOMER-qw73s3xl- :

what name shall I address you with?

Customer:

Ok all done :) Just a sec while I upload it you.

Customer:

I added a Button on the top right of Sheet1. Simply click it and watch it work :)

http://www.mediafire.com/view/?mp43vj9f5y5cxib

JACUSTOMER-qw73s3xl- :

Mr. Yegar, These aren't the ultimate spreadsheets I will be using. When I get the final versions, can you show me how to duplicate what u did. Okay, I'm going to play now...hold on..I'm excited!

Customer:

It would be my pleasure to show you :)

JACUSTOMER-qw73s3xl- :

its still working...

JACUSTOMER-qw73s3xl- :

i think....

Customer:

Did it mess up?

JACUSTOMER-qw73s3xl- :

oh, I had to hot okay...WOW!!

Customer:

Is it good? :)

JACUSTOMER-qw73s3xl- :

no, its cool!!

JACUSTOMER-qw73s3xl- :

so

Customer:

It really easy to adapt the code. I am making Notes in my code to give you a better idea :)

JACUSTOMER-qw73s3xl- :

if the ones on sheet one are on sheet 2 they turn green and if not it turns red. That is SO AWESOME!!!!!!!!

Customer:

Glad you like it :)

JACUSTOMER-qw73s3xl- :

i don't even know where your code is!!!

Customer:

To see the code press the ALT key and hold it. Then tap the F11 key

JACUSTOMER-qw73s3xl- :

do i put the cursor on the a column cell when i do that?

Customer:

When you do what?

Customer:

When you search?

JACUSTOMER-qw73s3xl- :

no, alt, f11. i did it but nothing happened..

Customer:

You may have to click a cell..Any cell just once. Then tap Alt + F11

JACUSTOMER-qw73s3xl- :

not sure if my f11 button is working. its red, is that what the alt does- get to the red

Customer:

Hmm not sure.. Try this..

Customer:

See where it says Sheet1 at the bottom?

JACUSTOMER-qw73s3xl- :

yes

Customer:

Right click the Sheet1 name and Click "View Code"

JACUSTOMER-qw73s3xl- :

k, i did . that worked!

Customer:

Then when the new window Opens, look on the Left and double click where it says Module

Customer:

Thats the code.

JACUSTOMER-qw73s3xl- :

i see the code. now what?

Customer:

Ok,... heres where it can be tricky.

Customer:

See where it says Sheet1 throughout the Code?

JACUSTOMER-qw73s3xl- :

yup

Customer:

Sheet1 is where the Code begins its search.

JACUSTOMER-qw73s3xl- :

yes

Customer:

Now see where it has a Range("A1:A1000")

Customer:

Next to Sheet1

JACUSTOMER-qw73s3xl- :

yes, it searched that range..

Customer:

Correct

Customer:

Thats the only part of the TOP of the code you should adapt.

JACUSTOMER-qw73s3xl- :

okay..

Customer:

Next look for where it says Sheet2.select

JACUSTOMER-qw73s3xl- :

yes

Customer:

Its just below the top

JACUSTOMER-qw73s3xl- :

yup

Customer:

Thats where the codes Looks for its match

JACUSTOMER-qw73s3xl- :

yes, column b

Customer:

You got it..

Customer:

Thats it..

Customer:

Just modify Where to start and where to look/

Customer:

The rest of the code is just tweaks to make it run fast and smooth

JACUSTOMER-qw73s3xl- :

okay. so can I replace the workshhets with updated values and the code will remain behind it?

Customer:

Yes Mam.

JACUSTOMER-qw73s3xl- :

okay, i think i got it. that is SO cool!!

JACUSTOMER-qw73s3xl- :

um, so i can have 4 more questions for the rest of the month?

Customer:

:) And later on if you want. it can be adapted to do lots of cool stuff. The sky is the limit :)

JACUSTOMER-qw73s3xl- :

okay. I think i am gold for now! If i get back online later, can I get to you? I'd like to request you..

Customer:

You sure can. Just put "ForCustomer at the beginning of your question or follow this link.....

Customer:

Just a sec lol

JACUSTOMER-qw73s3xl- :

and can I print and/or save this chat?

Customer:

You should be able to yes. Or bookmark it and return anytime

JACUSTOMER-qw73s3xl- :

how do i bookmaRK IT? : )

Customer:

Right click the page and select "Add to Favorites"

JACUSTOMER-qw73s3xl- :

ok, I am good to go! I'm so glad I found you . Thank you so much for all of your help!!!!

JACUSTOMER-qw73s3xl- :

ps. you get an extra bright smiley!!

Customer:

Your very welcome :) I am here anytime you need me. Please don't forget to Rate :)

JACUSTOMER-qw73s3xl- :

you are rated TOP NOTCH. have a GREAT day!!

Customer:

Thanks you too :)

JACUSTOMER-qw73s3xl- :

bye!

Customer:

Bye

Ryan-CTech and other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.

HiCustomer Are you there? You helped me out Friday, but I need additional help on same project. I have new data for the worksheet, but can't get the macro to work.

Customer: replied 3 years ago.


Hi Yegarboy,


 


What if I wanted to reverse Sheet one and 2...ie; see which ones are on two and not one?


 

Customer: replied 3 years ago.


You are THE BEST!! thank you!!

Anytime my friend :)
Customer: replied 3 years ago.


see ya next time!! I'll give you another great rating!! : )

Just let me know if I can help you again. I am always here to help. :)

Take care.
Thanks for the Rating :) You can Rate one om my new Answers above.