Two Excel Sheet Comparison Scraping

Hi,

This is a tough one - I have two excel sheets which can be seen in the attached file.

Both sheets have items which are not the same but mean the same. You need to use scraping techniques to extract matches from both and then identify the cheapest price for it to be populated into "Final Sheet"

Sheet 1: Has products and prices (gets updated on a weekly basis)
Sheet 2: Has products and prices (gets updated on a weekly basis)
Final Sheet: Will be populated with products and prices

Methodology:

Use "Sheet 1" and "Sheet 2" to exact type of products and whichever has the lowest price should be populated in "Final Sheet"

Example:

"Sheet 1"; item 165
Apple iPhone 5 64GB Mobile Phones
Price is $907.07

"Sheet 2"; item 202
Apple iPhone 5 A1429 GSM 64GB White
Price is $917.0

In the above example both items are exactly the same; but I require the lowest priced IPhone to be populated in "Final Sheet". Similarly there are many more items such as this.

My requirement is that the data is to extracted and populated in Excel. The excel sheets do not have the same number of items, where one item exists and the other list does not have the item; then the item gets populated into the "Final Sheet"


This is not a simple job where you look for exact matches and copy them in to the "Final Sheet" this is complicated as the description and the price is the key that will standardise them and the compare it.

Omer

OllieB,

I have a fair idea on what you are referring to and as it seems there is human intervention involved in this (which is fine)

Getting someone to do it free is a good idea but can someone do it for me for some $$$ - once off and then minor tweaking every now and then.

Omer

By this week.

With the free bit I'm stating that no one will do it free but I'm ready to spend a little money on the solution as its been going for 2-3 months.

Once its done and the returned values in a final sheet can be used via vlookup to see the items source - so when you place an order you would know which supplier to chase.

Thanks a million!

Omer

Hi omer81,

as OllieB suggested I´d also go with separating the whole name and than compare the parts of it, just, wouldnt go with % since that one gonna be highly influence with the # of parts you get, So I´d rather go with setting 2-3 columns as prior and if those get the match I´d mark it as success and than continue with the function to compare prices

Best regads

Soul

PS -- you´re really going for a tough one

Hi SoulP,

You're on the mark there - its a problem and problems need to be solved - I was trying initially but then got a bit frustrated as there are only so much I know about Excel.

Omer

Hm,

have been looking in your file for a moment now and I can tell only that there are ways of doing this, just you (or rather the guy whos gonna write the actual code) needs to find the system in the data input from each source and than from that you can separe it.

I´ve done something like that, handed it just today to my colleague it was tax/custom related and I had to spent few hours to actualy get the system at which the data were filled in the forms etc.

Also a little hint, I´d go by the maker(producer) grouping (which allows you to separate the actualy name from the part of the string you already know will match on many occasions).

The only problem to me (as for now) is that im not sure whether certain things match or not - I mean I m not into cameras and that stuff so i dunno whether to compare em according to mm values or whatever, like due to the pricing it seems like those two first canons should be about the same (match) but the name is completely different, in one case one is missing the mm value the other is off the rocket with the value, so that would be no match for the code in my eyes and this brings me back to the point mentioned above, if you sort the stuff by producer and mby category, u can set than the comparing parametrs (such as those MM of cameras) which seems like the way to go you create one big loop and you should be ok, just to find the exact paramters for each category

Hope it helps, this little analysis.

I´m sorry i wont try it by myself, I´m just not fit for it today (braindead), mby tomorrow will see

Soul

Thanks both SoulP and OllieB,

The maker / price is the key.

For example in both sheets you will find Iphone 4s 64gb (or 32gb..or whatever) - similarly in the camera's everything is important upto the point where "Brand New" is written or the words "Digital Camera" / "Camera" the numbering convention is there to define the model and type of camera (in camera world speak)

I'm really wanting to see the end result of this puzzle.

Omer

OllieB,

I'm like a little kid in a candy store - I'm absolutely going to love my weekend going through this finished excel sheet which has torn many weekends apart.

I'm currently going through the sheet and normally check a 10% sample for personal confidence, and looking for ways to find a fault BUT cannot :D

Most probably I'll be posting later this afternoon and hopefully I can get both OllieB and SoulP details.

Thank you a lot - this is just amazing and indeed appreciate your work.

Omer

Hi both,

sorry i took my time, kinda busy lately. I have check on ollieb´s work and it seems promising so i believe you willl get what you´re looking for. I wanted to try it myself but sadly i got flooded with new requests for some tools at work so i kinda dont have that much time to look at this and I admit, i wouldnt be able to make such a thing ollieb did in one go, (would need like 2-3 days to sit on it to produce something i´m happy with), so I´m sorry if i disappointed you and you´ve waited to see my version of solution but there wont be any prolly.

best regards both

Soul

SoulP,

Noooo don't give up on me!

I'm now thinking along the lines of fuzzy logic that could also yield accurate results.
OllieB had an amazing stab at it and as he mentions that there is substance in tolerance values of 51% and anything below that is questionable.

I attempted to use the "LEFT" function, including spaces and about 20 characters on both vendors. This was an attempt to first standardize the information. If you look at the data; the manufacturer and model are normally the 1st two words of any line.

Once done, I used the Match function on both columns to either give me TRUE or FALSE. Unfortunately this methodology did not work

There isn't a way around the an items colors - then again anything that shows up as no match will need human intervention.

And the work continues...

Omer

Though I might share a new finding...I stumbled across PowerUps add in for Excel.

There is a feature in there that finds similarities between two texts in terms of percentages; the problem there is that you need to standardize the text first.

For example; IPhone 4s 32gb MUST be lined up to IPhones 4s 32gb White - to give you 80% or so...if its lined up to LG Washing machine - then the match is 0.08%.

Well I can live with that as well, but how do I standardise the info in both sheets?

Omer

OllieB,

Sorry mate - don't agree with you

To go from having NO solution to having THE solution overnight; its an absolute miracle and have no one but to thank you for it.

What I had been saying previously is that there maybe other solutions but don't work as effectively, the example is that one that I attempted but the results didn't turn out too well.

So in a nutshell the one that you've provided is the way forward.

Could you let me know how is it done?

Omer

Dear OllieB,

I have to say - I'm lost on how to do this on my own. The new sheet has improved results - like really the results have improved.

The code that you provided (please ignore the simplicity of my question); does that go next to each item?

Column A
Apple iPhone 5 16GB Mobile Phones

Column B
Your given parameters?

I guess what I'm asking is how do I do it? (not a spoon feed) but where does everything go.

Apologies for asking a very very basic question.

Omer

OllieB,

Apologies I couldn't back to you - I'm yet to try the solution in VBA (as you had prescribed) - but only when I recover from the seasonal cold & flu

Also it will give me a chance to try on the newly updated sheet.

Omer