Macro to Map data columns in two different excel files and produce data in third file

Hi There

Would be grateful if someone can help develop a macro to solve below problem

* Sheet 1 - file 1 is base datafile thats maps our master product file with master product file received from our suppliers / merchants listing supplier product code to our product code, and supplier name to our name. This is prepared basis a one time exercise.

* Sheet 1 - file 2 is daily price list that we receive from the supplier in that format - it lists down supplier product code, supplier product name, MRP i.e. list price, selling price - at times this is at a discount to list price, and quantity available for sale.

* Sheet 1 - file 3 is the format in which we need output basis mapping of sheet 1 with sheet 2. Instructions are given against each field. We use file 3 to upload the file in our system that calculates final selling price to retail consumers. Our agents often refer this final file while discussing sales with potential customers.

the file formats are attached on google drive link here: https://drive.google.com/folderview?...E0&usp=sharing

Many thanks for your time and help here.

Thanks Hans, your help is appreciated.

Will be happy to clarify as may be needed.

Regards
Viral

Thanks Hans, will look forward to your assistance.

Regards
ProMax

I can understand. I can wait for another day too.

Warm Regards
ProMax.

Dear Hans

This looks great as a solution - since thanks at the outset. It worked on the sample files we had enclosed. However when we tried on actual file, it failed - note that in actual practice for this one case there were 2100 records in Sheet 1 - mapping file and 7100 records in supplier price information file, the failure was that it returned records as if no records were looked up and hence no data was found at all so all 7100 records were re-populated in the output file. I am attaching data a few records from actual files to see where is the miss. Also, couple of improvements and one point that was not correctly understood - see below:

A) In the macro file, can we insert three rows to include following information

• Name of the Merchant. We will enter short names / acronyms and we want that output file bears this name as part of file name
• Directory path with name of file for Sheet 1 - Promax / Supplier Mapping File
• Directory path with name of file for sheet 2 - Price Master Received from Supplier - we need this as the supplier will daily send one price information file and the names of the file could differ. We can directly write name as D:/SupplierOne/priceinformation_Dec11.xlsx so that Macro knows that information has to be mapped from this file

B) The second part of the file for data not found - this needs to be refined further
We need to populate those records that are there in Sheet 1 but for which no price information was sent by the supplier in sheet 2 and label these records as "Price Information Not Found". I believe currently it is the other way round and populates only those extra records in Sheet 2 for which price information is sent but there is no mapping records Sheet 1 - the base mapping file that maps supplier products to our product masters. In the given example, information is not available only for product code 7546 with name "AASHIR ATTA SEL 5KG" in Sheet two and we will use this output to quickly communicate to supplier that information for this product is missing.


C) The associate in the company who does this has one more additional request - at times for the same products supplier file has two sets of price information. You can see this in the sample that is culled out from actual file. Can such records - where two or more records of price information is found - can be auto populated as third part under the label "Multiple Price Records found" - we will need validation that the product code first finds place in sheet 1 as those are the only ones where promax is concerned. In given example two price records were found in sheet two for product code 4967 with product name: y-up Drink 600ML. We want to go back to supplier to clarify which is the right price in case multiple price information is sent.

D) Lasty, the associate also just helped me understand that special price should be blank where MRP / List price equals selling price AND Also where Selling price is only at 1% discount to the MRP/List price. Basically - it was found that to show a discount of only 1% to retail individual customer does not make sense and we rather tell them the MRP/List price.

I have tried to be as clear as I can, though am always happy to clarify if needed. Many thanks once more for your precious help in framing this solution.

Thanks, I will prepare sample output file and share soon.

I had tried approaching people in my local circle to check if someone can help with the problem, before approaching the forum. Unfortunately, could not locate anyone and hence sought help here. I hope you appreciate and my since thanks for your help so far.

The changes are really not so complex and I am sure with one more attempt, we should get there.

Thanks a bunch, you have been great help.

Dear Hans

I have enclosed the sample files. I have chosen to do it in separate files so that codes are accordingly written - I am replicating how it happens in reality where all three files are always separate.

All yellow cells are for instructions only - for reference purposes. These are not available in real life files neither are needed in output files

I hope this proves useful to you.

Regards
Promax

Sure Hans. Just to iterate, the macro file shared last time had worked perfectly on the sample we had enclosed but when we tried on actual files, it had failed. Not sure if it could be due to size of actual files or some other reasons.

Thanks Hans. It was incorrect results. I had check and rechecked the file layouts, they were not different from what I shared originally. I can share again here if needed. Incorrect result can be described as this: The first part it was supposed to generate by mapping sheet 2 data from sheet 1 - was blank. Three rows after that I populated entire records from sheet 2 in the output file as if nothing was every mapped / matched.

Like I said, it had worked fine on the test files enclosed but it failed on actual files. I can share them if needed [I am new to excel forum, if there is a way to mark this actual files as private, will be useful - so that other audience cannot see actual files]