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: 1871
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

This answer was rated:

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?

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?

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 3 years ago.

Here is the other file

Attachment: 2013-11-01_185324_asin_upc_database.xlsx

Ok,

both files look exactly the same,

was this intentional?
Customer: replied 3 years ago.

No, sorry, here is the other

Attachment: 2013-11-01_190604_source_file.xlsx

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 3 years 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.

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 3 years 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?

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
how did that work for you?
Customer: replied 3 years 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.

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
Hello?
Customer: replied 3 years 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?

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.
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 3 years 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.

the formulas are automatic.

Once the correct file is linked.

it will be automatic
Customer: replied 3 years 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?

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 3 years 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.

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
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 3 years 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.

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 3 years 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.

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 3 years ago.

Yes, definitely. That is what I was planning.

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 3 years ago.

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

ok, should have something to you by the end of the day
Customer: replied 3 years ago.

Terrific!

i will notifiy you when its ready
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 3 years 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!

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 and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years 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.


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