Comparing two large Excel files

Question

Hello,

We are starting our own drop-shipping business and have been working on our website for about a year and a half. We are planning on opening up the website in January. We are having trouble with figuring out how to compare our inventory lists to update the site daily.

First, we need to pull the Excel spreadsheet from our website with our item numbers, which I can do. There are about 9100 products currently on the website. Second, we need to download the newest Excel sheet from our supplier, which I can also do. There are about 8500 products my supplier currently sells. My supplier does not let me know what products are added or removed from the list daily. I can only find this information by the daily spreadsheet they send me. It will be nearly impossible and very tedious to manually go through 8500 products daily to find the differences.

So essentially, I need to compare the two lists of item numbers. If the numbers are the same from list to list, these can be removed. If the item number is on my suppliers list but not my website list, I need it placed into one column to add to my website. If the item number is on my website list but not my suppliers list, I need it placed into a separate column to remove from the website.

When the website matches my suppliers list, I can upload my suppliers list to the website to update the quantities for all of the products.

The item numbers contain letters and numbers, some are just numbers as well.

We have Excel Office 2019

We have tried researching on-line, reaching out to friends, talking to the Small Business Development Center, Careerlink, and spoke with Microsoft Support. Microsoft Support suggested we reach out on here and there are add-ins that can do the task we need.

Any help is greatly appreciated! Thank you in advance!

Answer 1

To:  Kim Marcin

re:  MediaFire programs

The programs at MediaFire are not related in any way to your inventory lists issue.

You do not need any of them to resolve your new, old, deleted items listings.

Though, when you become more familiar with Excel functions, the Custom_Functions add-in could be valuable.

You should be able to access the Excel help file for CountIf (not CountIfs)  and review the instructions/examples provided.  That is probably the best way to go. 

(microsoft, however, doesn't always make their help easy to understand)

Make two very short lists and try out the formula (function)...

  =CountIf**(Range of cells to look at, "item to find in the range")**

Note:  $ signs make the range address static)

Also, if you don't like the help file or having to post to a forum, buy an Excel book with a good index.

'---

Excel programs (now free) at MediaFire...The Custom_Functions add-in has 20+ new Excel functions including the X_vLookUp function.

Download (no ads) from...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 2

If you can share your file with me so that I can check your formula. Thank you.

Answer 3

Mitch,

Are you saying to just do an if function, not a vLookup?

Answer 4

Thank you for responding so quickly Mitch,

We have attempted using "vLookup" and "if" functions but could not get it to work. 

Maybe, we were doing something wrong. I was told by a friend that since the item numbers have letters and numbers the vLookup function might not work, not sure if this is true or not.

Do you have any good links to videos and/or websites that could help me figure out what I was doing wrong?

Answer 5

Hi, Kimmarcin

I'm Mitch, a Microsoft user like you. I'm happy to help answer your question.

I suggest you maintain a database of your product list and just do a simple "if function"/lookup formula in your supplier's list with your database as look-up data.

Pleese let me know should you need further assistance. Thank you.