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 The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1326
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

1) I need to create a query function in excel that would take

Resolved Question:

1) I need to create a query function in excel that would take a list
of columns of data all of which have a product identifier called an
ASIN and query separate spreadsheet database to grab UPC codes and
populate a column in the original spreadsheet. Please note, the
full files will have 80,000+ rows of ASINS that need UPC codes added
to via this custom query function.

2) I am constantly having to highlight vast sets of rows of data to
copy and move to other sheets. I know I can highlight an entire row
but that also highlights all the empty cells below the end of the
data. I am certain there is a short cut to quickly highlighting all
the active cells in a row (those with data) to cut/copy and paste
elsewhere. How do I do this?
Submitted: 11 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 11 months ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

is this all to be done in excel?

Customer:

Yes, this would all be done in excel. I can send you sample files to give you an idea of what I am looking for in terms of functionality if that would help. Basically, one file would be the master and would have two columns, one with ASIN identifiers and one with the associated UPC code. Then I would have a second file that would have multiple columns of various bits of data including ASINs and an empty UPC column. I want to push a button (macro?) and initiate a query that would populate the empty column with the appropriate UPC code.

The-PC-Guy :

sure I can do this

The-PC-Guy :

it would be best to get the sample files

The-PC-Guy :

do you have a paperclip icon at the top of your chat window?

The-PC-Guy :

my responses are not going through

The-PC-Guy :

do you see what I am typing?

The-PC-Guy :

ok, I am not getting your responses

The-PC-Guy :

sorry

The-PC-Guy :

we seam to have lost chat connection

Customer:

I can't attach files, only pictures. What is the way around this?

The-PC-Guy :

what is the file extension

The-PC-Guy :

you can attach

The-PC-Guy :

xls

The-PC-Guy :

and

The-PC-Guy :

xlsc

The-PC-Guy :

xlsx

The-PC-Guy :

i mean

The-PC-Guy :

i have the first one

The-PC-Guy :

is there another file?

Expert:  The-PC-Guy replied 11 months ago.
Our chat has ended, but you can still continue to ask me questions here until you are satisfied with your answer. Come back to this page to view our conversation and any other new information.

What happens now?

If you haven’t already done so, please rate your answer above. Or, you can reply to me using the box below.
Expert:  The-PC-Guy replied 11 months ago.
sorry, I apologize for the chat issues, that is beyond my control

i have the first file


is there another file?


if you would like to upload a file

you can goto

www.wikisend.com

after uploading, they give you a FIle ID#

paste that number here
Customer: replied 11 months ago.

Here is the other file

Attachment: 2013-11-01_185324_asin_upc_database.xlsx

Expert:  The-PC-Guy replied 11 months ago.
Ok,

both files look exactly the same,

was this intentional?
Customer: replied 11 months ago.

No, sorry, here is the other

Attachment: 2013-11-01_190604_source_file.xlsx

Expert:  The-PC-Guy replied 11 months ago.
thats better.

So just to clarify what you want before I get started.

You want any UPC code put into column F of the source file, where AMUKSIN matches the ASIN of the asin file?

This can be done 2 ways

1. automatically by a formula, where the values in column F will update automatically.

or

2. A macro where you have to click a button to populate the sheet.

Whichever method you want I will do. Method 1 is the easiest, but I will do the macro if you prefer.

let me know
Customer: replied 11 months ago.

The UPC-ASIN master list would be updated by me regularly so it will change. Does that impact which option would work? And keep in mind it will be 60,000 or more queries time I run the matching process.

Expert:  The-PC-Guy replied 11 months ago.
no it would not make a difference as to whichc method you chose.

This file contains the one that is automatic with formula.

Wherever you see a blank value instead of a UPC in the sheet, that means that there was no matching asin number in data wokbook for that ASIN.

http://ge.tt/8T5BBww/v/0?c

if you want it the other way let me know.
Customer: replied 11 months ago.

Ok, I guess do it with a formula and directions on how to us, send me the file and I will populate with the full 60K plus of UPC codes and run a full query and if it all goes as it should we can conclude. Also, I notice that the codes in the yellow code are missing the 0's in front. Can this be fixed?

Expert:  The-PC-Guy replied 11 months ago.
here is the modified file per your request.

http://ge.tt/9V4YHww/v/0?c

all you have to do is copy the formula that is in Cell F2 down the column for as many rows as you need, and the upcs will populate.

let me know if you have any other issues.

thanks.

ALSO IF YOU CAN TAKE A MINUTE TO RATE MY ANSWER SO I GET PAID FOR MY TIME, IT WOULD BE APPRECIATED
Expert:  The-PC-Guy replied 11 months ago.
how did that work for you?
Customer: replied 11 months ago.

OK, give me a few hours. I am getting a big mailing out the door. Then will play around with and make sure it works fully.

Expert:  The-PC-Guy replied 11 months ago.
ok, please check it out, I can correct any issues.

if there are none

IF YOU CAN TAKE A MINUTE TO RATE MY ANSWER SO I GET PAID FOR MY TIME, IT WOULD BE APPRECIATED
Expert:  The-PC-Guy replied 11 months ago.
Hello?
Customer: replied 11 months ago.

Hello!


 


Sorry, I had to get through my work day, then time with family, then bed, then up early to get to an airport to get on a flight. I am in route now and looking at it. Looks like the ASIN UPC master file that is linked to the file that you sent me is missing. I take it I can just create my own look up directory and link in the edit function, yes?

Expert:  The-PC-Guy replied 11 months ago.
yes you would use your file,

what you do is goto the data tab

then click edit links

then change that to whatever the data file is that you are using

make sure you enable autoupdate of links when you start the file

let me know if you have any questions.
Expert:  The-PC-Guy replied 11 months ago.
IF You could please take a moment and RATE my service, That would be great.

I know you are traveling. If you have any problems using my formulas, you can always write back to me, even after rating.

so please take a minute a do so, this way I get paid.

Thanks
Customer: replied 11 months ago.

I have updated the link to a xls file with ASIN and UPC header files. I am unclear on how to initiate the query process so that the empty column in the JA MOD file. i tried closing and saving the JA MOD file and then re opening it but nothing happens.

Expert:  The-PC-Guy replied 11 months ago.
the formulas are automatic.

Once the correct file is linked.

it will be automatic
Customer: replied 11 months ago.

That was my expectation but it appears not to be doing anything. I have opened both files made sure that the file with resource file (with ASIN and UPC columns complete with 60,000+ entries is linked to the JA MOD file via edit links. I have tried the "check status" button in the edit links window. That changes the status from "unknown" to "source is open" but still nothing happens and the empty column does not populate. I tried removing and readding the link to the resource file and still nothing happens. What next?

Expert:  The-PC-Guy replied 11 months ago.
did you copy the formula from the file I sent you?

YOu may have to do that.

Let me know if that fixes it.

IF not I will offer to try a remote session to fix it for you.

that may be the easiest way to help you

its probably just something you are overlooking.

let me know

thanks
Customer: replied 11 months ago.

no idea where the formula is, how to copy it and where to copy it to. that was not discussed. i am a bit frustrated by this now. Can you please do this as a macro instead? Macros have execute buttons.


 


Set up as one macro enable excel file with two tabs. First tab will be for the ASIN UPC library and second tab will be where I load in the various columns of data with black UPC column at end. Then I can just push the macro button to initiate the process of getting the corresponding UPC code for each ASIN on the second sheet.

Expert:  The-PC-Guy replied 11 months ago.
can you send me a new sheet with the way you want it layed out?

Be sure to indicate which sheets and columns the data is coming from and which it is going to

thanks
Expert:  The-PC-Guy replied 11 months ago.
not sure if my replies are going through, so I will repeat

can you send me a new sheet with the way you want it layed out?

Be sure to indicate which sheets and columns the data is coming from and which it is going to

thanks
Customer: replied 11 months ago.

All of your replies have gone through. Please keep in mind, I was traveling all weekend, doing some work from the air, but did not have space to work on the computer on the flight home last night and did not get home til after 9pm then had to spend some time with my family, get a bite to eat and go to bed early so I could be up this morning working at 7am.


 


Attached are two screen grabs of the the tabs in the macro enable excel file that show how I would like this set up. Just Answers is now not allowing the transfer of any non image files.

Expert:  The-PC-Guy replied 11 months ago.
ok, understood.

I would like you to try one of these other sites when you get a chance.

It would be best if you could send me the entire file with all 80000 rows, this would avoid you having to copy the macro over to another workbook which could be a bit of a process. I want to make sure this works for you the first time.

Please upload the workbook to

www.wikisend.com

or

www.ge.tt

both sites work the same way

you first upload the file

then they give you a link

paste that link here.
Customer: replied 11 months ago.

Here is the problem. That library will constantly change with new UPCs added and others removed, so I need a process that works regardless of the number of UPC/ASINs on the library page.

Expert:  The-PC-Guy replied 11 months ago.
understood, and it will if you are going to be using the same workbook. Inotherwords if you change the values, add some delete some within the same workbook that contains the macro, then press the button to run the macro again it will update the output, regardless of how many things you add.

Just rember to always use the same workbook. This will avoid having to copy the macro over to new workbooks.

Does this make sense.
Customer: replied 11 months ago.

Yes, definitely. That is what I was planning.

Expert:  The-PC-Guy replied 11 months ago.
ok, then if you can get me your sample workbook

using one of these 2 sites. can you get me the file.

-----------------------------------

Please upload the workbook to

www.wikisend.com

or

www.ge.tt

both sites work the same way

you first upload the file

then they give you a link

paste that link here.
Customer: replied 11 months ago.

http://wikisend.com/download/916308/UPC LOOK UP MACRO.xlsm

Expert:  The-PC-Guy replied 11 months ago.
ok, should have something to you by the end of the day
Customer: replied 11 months ago.

Terrific!

Expert:  The-PC-Guy replied 11 months ago.
i will notifiy you when its ready
Expert:  The-PC-Guy replied 11 months ago.
here you go, let me know if you need anything else

http://ge.tt/1NdiBBx/v/0?c

remember you have to enable content when you load the workbook, or the macros won't work


Cool PLEASE REMEMBER TO RATE MY SERVICE SO I GET COMPENSATED FOR MY TIME:
Cool

you may do so with the smiley faces underneath this chat window
Customer: replied 11 months ago.

It works! Strangely it take 95% of system resources over about a 10 minute period to populate the UPCs but it does work. Not sure why it saps so much computing power (I have 8 gigs of ram and a 1.6 ghz processor) but it does. Any thoughts on that? Regardless, I will complete the transaction so you can get paid. Appreciate your help!

Expert:  The-PC-Guy replied 11 months ago.
could be your processor Is it AMD by any chance.

I am running Excel on a Dual-Core Intel 3.5 Ghz

and it populates instantly. Of course the more rows you have the longer it takes.

I can speed it up a bit.

I have made a slight change that may help on slower processors.

But keep in mind that the more rows of data you have the longer it will take.

http://ge.tt/5rx4mFx/v/0?c
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1326
Experience: 20 years experience providing remote computer support
The-PC-Guy and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 11 months ago.

I am running it on an Intel Core i5-4200U 1.6 ghz with turbo boost up to 2.6 ghz.


 


Most definitely I expect it to take longer with a 59K row query, but it took about 10+ minutes and almost all my system resources which was a surprise. I have a couple of computers that I don't use much so I think I will just farm this function out to them if needed but will try your revised version and report back. Regardless, I am happy with you work. I don't see how to conclude the matter so you get paid or is that already done? It has been a while since I used Just Answers.

Expert:  The-PC-Guy replied 11 months ago.

Cool PLEASE REMEMBER TO RATE MY SERVICE SO I GET COMPENSATED FOR MY TIME:
Cool

you may do so with the smiley faces underneath this chat window

let me know if you have problems doing this

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional