Macro to Map data columns in two sheets and produce data in third sheet

[promax]

Hi There

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

* Sheet 1 is base datafile mapping our master product file with master product file received from our suppliers / merchants
* Sheet 2 is daily price list that we receive from the supplier in that format
* Sheet 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 sheet 3 to upload the file in our system that calculates final selling price to retail consumers

the file formats are attached on google drive link here: https://drive.google.com/folderview?id=0B7ynWqzeAP-VRU9DdXNTTmV3dE0&usp=sharing

Many thanks for your time and help here.

 

[oldbrewer]

45738	lipton g tea light 100 ba	306065	liptonabc				45738	lipton g tea light 100 ba	200	200	50
17732	l'oreal total repair serum 80m	306180	lorealxyz				17732	l'oreal total repair serum 80m	150	125	0
			sheet 3
		306065	45738	liptonabc		50
		306180	17732	lorealxyz	125	0
		is this what you want in your sheet 3

<colgroup><col><col><col><col span="5"><col><col span="4"></colgroup><tbody>
</tbody>

 

[promax]

Many thanks for prompt response.

This is what I want in sheet 3 - two parts - Part I and Part II

Part I - information on all items found in Sheet 2:
SKU ID Product Name MRP Special Price Quantity
306065 Lipton ABC 200 NA 50
306180 lorealxyz 125 100 0

Part II: List down all items from Sheet 1, where pricing information could not be locate in the Sheet 2 - Price Information Sent by the Supplier. Format: Product code of the supplier [P-Code] and [Supplier Product Name]

Note: Special Price - which typically indicates a discounted price - will be NA if Sheet 2 has Special Price and MRP as equal - example if both MRP and Special Price is 200 in Sheet 2 then Sheet 3 will show Special Price as NA or will show it as blank. If Special Price is smaller then MRP in Sheet 2, then Sheet 3 will populate both MRP and Special Price. MRP means Maximum Retail Price.



promax

 

[oldbrewer]

	sheet1						sheet 2
45738	lipton g tea light 100 ba	306065	liptonabc				45738	lipton g tea light 100 ba	200	200	50
17732	l'oreal total repair serum 80m	306180	lorealxyz				17732	l'oreal total repair serum 80m	150	125	0
			sheet 3
		306065	45738	liptonabc		50
		306180	17732	lorealxyz	125	0
		is this what you want in your sheet 3	headings missed off so i confused you - tell me what you want in terms of sheets1,2 and 3 as I made them

<colgroup><col><col><col><col span="5"><col><col span="4"></colgroup><tbody>
</tbody>

 

[promax]

I have made edits under sheet 3 as also added headers for both sheet 1 and sheet 2. Hope this should make things clearer.

	sheet1						sheet 2
Supplier Product Code	Supplier Product Name	Promax Product Code	Promax Product Name				Supplier Product Code	Supplier Product Name	List Price	Selling Price	Quantity
45738	lipton g tea light 100 ba	306065	liptonabc				45738	lipton g tea light 100 ba	200	200	50
17732	l'oreal total repair serum 80m	306180	lorealxyz				17732	l'oreal total repair serum 80m	150	125	0
			Sheet 3 -Part I		Should populate following information.
		Promax Product Code	Supplier Product Code Not Required	Promax Product Name	Product List Price	Product Discount Price - keep blank if List Price and Selling Price is Same in Sheet 2, else Populate Selling Price from Sheet 2	Product Quantity
		306065	45738	liptonabc	200		50
		306180	17732	lorealxyz	150	125	0
		Part II of Sheet 3 or you can say Sheet 4 - OK either ways			To list Down Supplier Product Code and Supplier Product Name of All Items for which no information was available in Sheet 2 [i.e. it gives items where supplier did not send any price information to promax]
		is this what you want in your sheet 3	See above on what I want in Sheet three I hope that is useful. I had also attached google drive link in my very first post - if that proves useful.

<tbody>
</tbody>

 

[oldbrewer]

COL A		sheet1					COL H		sheet 2
suppcode	suppprodname	promaxcode	promaxname		ROW 2		suppcode	suppprodname	listprice	sellingprice	quantity
45738	lipton g tea light 100 ba	306065	liptonabc				45738	lipton g tea light 100 ba	200	200	50
17732	l'oreal total repair serum 80m	306180	lorealxyz				17732	l'oreal total repair serum 80m	150	125	0
12345	acme t paste 100	444123	acme toothpaste 100 gm				12345	acme t paste 100	75	65	40
23456	smith r bl 5	444124	smith razor blades 5 pack				34567	jones b beans 400	35	35	200
34567	jones b beans 400	444125	jones baked beans 400 gm				56789	heinz veg s	44	44	100
45678	heinz tom s	444126	heinz tomato soup
56789	heinz veg s	444127	heinz vegetable soup
			sheet 3	COL E
	ROW 17	promaxcode	promaxname	listprice	disc price	quantity
		306065	liptonabc	200		50
		306180	lorealxyz	150	125	0
		444123	acme toothpaste 100 gm	75	65	40
		444124	smith razor blades 5 pack	NO DETAILS AVAILABLE
		444125	jones baked beans 400 gm	35		200
		444126	heinz tomato soup	NO DETAILS AVAILABLE
		444127	heinz vegetable soup	44		100
			D18
			=OFFSET($D$2,ROW()-17,0)
			E18
			=IF(ISERROR(OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2)),"NO DETAILS AVAILABLE",OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2))
			F18
			=IF(IF(ISERROR(OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2)),"NO DETAILS AVAILABLE",OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),3))=E18,"",(IF(ISERROR(OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2)),"NO DETAILS AVAILABLE",OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),3))))
			G18
			=IF(E18="NO DETAILS AVAILABLE","",IF(IF(ISERROR(OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2)),"NO DETAILS AVAILABLE",OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),4))=F18,"",(IF(ISERROR(OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),2)),"NO DETAILS AVAILABLE",OFFSET($H$2,MATCH(OFFSET($C$2,MATCH($C18,$C$3:$C$9,0),-2),$H$3:$H$9,0),4)))))
		listprice	NO DETAILS AVAILABLE
		Count of promaxcode
		promaxcode	Total
		444124	1	a simple pivot table based on sheet3 pulls out products where no details available
		444126	1
		Grand Total	2

<colgroup><col><col><col><col><col><col span="3"><col><col><col><col span="33"></colgroup><tbody>
</tbody>

 

[promax]

Dear Oldbrewer,

Thanks for your help and prompt response, however I have not understood this.
Note that sheet 1 sheet 2 and sheet 3 are only names I was using. Actually both sheet 1 and sheet 2 are separate individual excel files in which data is available to us [eg. file name 'sheet 1.xls' is prepared one time but 'sheet 2.xls' which is supplier price data is sent to us say every alternate day] and basis this, 'sheet 3.xls' as output is required in the 3rd file. So where do I copy the given formulae? You have mention Row 17 - but the results can start from row 2 itself in sheet 3.xls assuming row 1 is for the header titles.

I am sorry I did not follow through the solution and hence I am asking.

Also, we need a Maco based solution where all mapping and output happens at click of a button, since we have multiple suppliers and everyday about 25% of the suppliers will send us new supplier price information.

 

[oldbrewer]

the row and column locations are to help you understand how the formulas work.

I have used "tables" on the same sheet to demonstrate the approach. Easy to refer to say [supplierpricedata]sheet1!A1 in the formulas

or - in an out of the way place on your "sheet3" you could have a block of cells eg in CA1 = [supplierpricedata]sheet1!A1 then you can use my formulas without the need for external references.

no problem with supplier info coming every 2 days - just over-write the previous 1

multiple suppliers - make sure they use a common format and then put supplier1 info in first 200 rows, supplier2 info in rows 201 to 400 etc

that way you can update as they come in and the whole sheet of ALL your supplier info in effect becomes a giant lookup table

 

[promax]

the row and column locations are to help you understand how the formulas work.

I have used "tables" on the same sheet to demonstrate the approach. Easy to refer to say [supplierpricedata]sheet1!A1 in the formulas - Promax: where do I use [supplierpricedata]sheet1!A1 in the above example Please let me know what to find and replace

or - in an out of the way place on your "sheet3" you could have a block of cells eg in CA1 = [supplierpricedata]sheet1!A1 then you can use my formulas without the need for external references. Promax - Did not understand this

no problem with supplier info coming every 2 days - just over-write the previous 1

multiple suppliers - make sure they use a common format and then put supplier1 info in first 200 rows, supplier2 info in rows 201 to 400 etc Promax - this solution does not work. Each supplier sends files separately and there is no uniform time for them to send. The three sheets are worked out separately, the third sheet or the third output file is also used to independently update another system. Hence the output is needed in independent supplier wise files [to note: even input files are maintained or is made available supplier wise only.]

that way you can update as they come in and the whole sheet of ALL your supplier info in effect becomes a giant lookup table

See my inline comments above

 

[oldbrewer]

if you have info in another sheet you have to use eg =sheet2!A1

but if you put the info way over to the right on your existing sheet you can refer to direct eg =DA1

if say block 401 to 600 is for supplier 3 whenever supplier 3 sends an update delete 401 to 600 then paste in new data

my first point I was merely saying I put info on same sheet for ease of understanding logic, but if you need it on say sheet2 you will reference it by eg sheet2!A1