Comparing two spreadsheets, matching duplicates question

Hi. I have a motorcycle inventory of around 150 used bikes that I have to manage. Bikes getting sold, bikes going into inventory then onto our website.

Here's what I am doing.

Using two spreadsheets and using conditional formatting to compare our web inventory to our actual inventory. This lets me know what needs to be added to the site, or taken off of the site because it has been sold.

It's a bit rough though.

First I get a spreadsheet from our web provider with a bunch of useless info that I don't need.
Then I seem to have to arrange the two spreadsheets so that the stock numbers that I am comparing are in the same column, in order for conditional formatting to work.

I would really like to have something prompt me to load (Hard inventory) then another prompt to load (web inventory). And to have an automatic out put with the sold bikes in one category, and the bikes that need to be added in another category.

Can anyone point me in a direction, I dont even know what this would be called.

Thanks

Hi Harleyguy

Welcome to the fourm.

This seems do-able; with minimal input if you use some VBA.

If you would like to upload the format of (a) the stock sheet and (b) the web list, I think I can do something.

Regards
Alastair

HI I am am unfamiliar with the term VBA, and where exactly should I upload the files? Just here in the comments?

Hi harleyguy

VBA is Visual Basic Application. Another way of saying it is that I would use a macro. I know some people have issues with macros (possibly due to the risk of viruses)

When replying to the thread, instead of clicking on "Post Quick Reply", you should click on "Go Advanced", scroll down to "Manage Attachments" > "Add Files" > "Upload files" and you will be able to attach your files after which click on "Submit Reply".

Regards
Alastair

websiteexcelforum.xlsxLSexcelforum.xlsx

So there are the two files, one is from our site, and the other is the inventory. The one from the site, that is exactly how I get it. I normally cut out all the crap I don't need. Then I align the stock number column from each spreadsheet into one column. Then I use the match duplicates option fill with red. And that will let me know what is sold, and what needs to be added. Because if there are no duplicates found on the web spreadsheet, it means that the bike is no longer in the inventory and needs to be removed. If it doesnt match duplicates on the inventory, it means that the bike is new, and it needs to be put online.

So yea this macros option would be cool to check out. Let me know if you can help out.

Thanks.

Hi harleyguy

Sorry - I have not used a macro. If the names of the workbooks were always the same, then I could write a macro to do what I have done.

What I have done is in the website workbook put a formula in column HD. I have used HD as being the first free column. The formula will work equally well in any other column (O,P,R for example). I then set up conditional formatting on column W to highlight bikes not in stock.

Looking at the stock workbook in column I, I put in the following formula:

Please Login or Register  to view this content.

To explain:
the "LEN(B10)>2,LEN(B10)" checks to see how long the entry is in column B (this stops "No" being placed against a heading)
the "ISNA(MATCH(B10" means if the match fails then show "No", otherwise "Yes"

I then put conditional formatting on column I, just to make it look pretty.

This can be automated, but it does depend on the workbook names always being the same.

Let me know how you get on

Regards
Alastair

I can have the workbook names always be the same, I can have the lady send me the one from the website as a continuous name, and also I can just save the inventory file as the same name each time.

Hi harlleyguy

"websiteexcelforum" is not the catchiest name - would you like to suggest 2 names and I will incorporate that into a macro.

Regards
Alastair

True, but its direct. How about for the website inventory name it Webinventory and the other Hardinventory

Hi harleyguy

OK, I will automate on these names.

Regards
Alastair

Hi harleyguy

If you save the attached file in the same directory as the HardInventory and WebInventory files, when you open the Control file it should, on clicking the pretty button, open both files and compare them.

Let me know how you get on. Feel free to ask about anything that needs explaining

Regards
Alastair

Just as a reminder the lsexcelforum is hardinventory, and the website excel forum is webinventory

It has been edited.

Hi harleyguy

Can you let me know what the macro was trying to do when it failed?

Regards
Alastair

ENDRESULT.jpg Hi I attached a picture is that what the end result looks like, and what does the highlighted fields represent?

Hi Harleyguy

The highlighted cells are those which are on the website, but no longer in stock.

Regards
Alastair

Man, we are so close. The original way I was doing it would give me TWO results. Results for which units are sold, and need to be removed off the site, and also which bikes are new to the inventory and need to be added to the site.

This saves some time thanks.

Hi Harleyguy

erm... If you look at WebInventory, column W you will see several cells highlighted in light brown. These are those items on the web site that are no longer in stock.

If you now look in HardInventory, column I you will note several cells with "Yes" cunningly hidden behind green highlighting. These are those items that are also on the WebInventory.

Have I missed something here?

Regards
Alastair

OK so let me get this straight the green fields in the hardinventory file are the bikes that are on the website and on the inventory. Because there are fields being highlighted in green that are not on the website. For example new bikes, they aren't on the website, and there are alot of new bikes in green.

The File i have attached here is what I am going for.

The File i have attached here is what I am going for.

Hi harleyguy

OK I see what you want. Do you want the End Result to be a separate workbook (in which case what name?) or as a sheet in HardInventory or WebInventory?

Regards
Alastair

Hey, so sorry for not getting back sooner. It would be so helpful to have the end result in a separate work book.

Hi harleyguy

I will get on to this soon. Do you have any preference for the name of the new workbook. Using all my linguistic skills, I will use "Summary" unless you suggest anything more suitable.

Regards
Alastair

Hi, yes that would be a great name for it, summary, yes sounds good.

Thanks again for all of your help.

Hi harleyguy

Here's the Control file that will run with your WebInventory and HardInventory files. The Control file needs to be placed in the same directory as the other 2 files.

I hope it works OK for you, but if not, then be aware that I am out of the country for 2 weeks after tomorrow and will not be able to reply to any emails.

Regards
Alastair

Hello!

Man, you sure are getting this down to the gnitty gritty. I see now it creates a file called summary, as to what I am assuming is an output of what should be currently on the website.

It still involves manual work. Before you made something that was highlighting the units that were online but needed to be removed, and highlighting the units that need to be added to the website.

This, still involves more work. Just because I still have to go on the site, and search one by one to find out what I need to remove from the site.

Do you see what I am saying?

Hi harleyguy

I am currently by a pool in Greece - no access to Excel.

I thought that the 2 files Hardinventory and Webinventory were produced with no human intervention. Is this wrong? If it is wrong, then you need to let me have the raw data and I will see what can be done.

Regards
Alastair

Hey man that sounds awesome, and yea the spreadsheets require no human intervention. Still the summary file, is just showing me one list. I believe the summary file is just showing me what needs to be currently on the site. But it doesn't show me what needs to be removed too. So still requires me to go into the site, and cross reference it to the summary sheet. I was trying to make this program. So I just throw in the two spreadsheets, and it tells me what needs to be removed from the site, and what needs to be added to the site.

HI, just wondering if you are still in Greece

How you doing? I was wondering of you were back from Greece yet?

HI Harleyguy

You must think that I have very long holidays (or I have given up on you!)

Neither is right. I checked for a response to my Greek post - but did not spot that your reply was on a new page. When I was checking something else I spotted your posts. So - my apologies for the delay.

The Summary sheet (attached) show:

The Webinventory details, showing which needs to be Removed eg Row 8

and the Hardinventory list showing those itemd to be Added eg Row 250

I thought that the layout of the Summary was as per your example? So either I have got it wrong, or your Summary file is different to my Summary file.

Please note that the Summary file has the current date added to it. This means that the macro will stop if you try to run the Summary twice on one day.

Regards
Alastair

Yes that summary file is perfect!!!!!

I must still be doing something wrong. My summary file comes out all messed up. summary.jpg

Hi Harleyguy

I note that you have attached a jpg file, but I am unable to open it.

Perhaps you will send me your messed up summary file?

Regards
Alastair

Ok here it is

Hi Harleyguy

Here are the 3 files that I was working on to produce the "perfect" Summary file.

If you do not get a perfect result, please let me have back the Summary file and let me know any error messages that the system generates.

Just to confirm: the Webinventory, Hardinventory and Control files all have to be in the same directory

Regards
Alastair

Hi,

Thanks again for all your help.

So I am leaving these files in the downloads.
Then I am opening control and pressing the button you have provided called compare.

Now, I am seeing an error called "run-time error '1004'Method save as of object workbook failed.

Getting so close!!

Wow it just worked on a different computer.

Does it matter about the version of excel? Because it is working on one computer, and not on another.

HI, here is the latest summary file, something regarding the sheet command, I think, in Visual Basic.