Macro to built final file from 3 different files

[Cris_93]

Hello Excel masters

I need your help in order to develop a final file (CustomerForecast) from 3 different files with some restrictions.

The final file should have the below format. The columns "FactType" and "Unit" should have always the same values.

Depot/StoreFormat	Product	FactType	Unit	Date	Value
Dublin	RTP088	Customer Forecast	Cases	02/11/2019	23
Belfast	RTB222	Customer Forecast	Cases	12/11/2019	32
Hatfield	RTP088	Customer Forecast	Cases	07/11/2019	30
Dordon	RTL008	Customer Forecast	Cases	05/11/2019	30
…	…	…	…	…	…

<tbody>
</tbody>

The other data for the others 4 columns come from the below 3 different files. I will put in red the required columns from each file:

"ROI.csv"

Supplier number

Category area

Star line

Tpnb

Description

Tpnd

Case size

Occ

Depot number

Depot name

Forecast date

Order date

Delivery date

Forecast cases

5997500

MFP

N

52440995

PORK MINCE 500G

31411873

8

5.05E+12

735

BALLYMUN FRESH PBL

03/10/2019

04/10/2019

05/10/2019

40

5997500

MFP

N

52445008

IRISH LAMB MINCE 15% FAT 533G

37216980

4

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

04/10/2019

07/10/2019

10

5997500

MFP

N

52483552

T. ROUND STEAK BEEF MINCE 10% FAT 554G

34945576

12

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

16/10/2019

17/10/2019

102

5997500

MFP

N

63755738

T. FIN* IRISH CANADIAN MAPLE RASHERS 240G

32607750

8

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

21/10/2019

22/10/2019

48

…

…

…

…

…

…

…

…

…

…

…

…

…

…

<tbody>
</tbody>

The Depot name "BALLYMUN FRESH PBL" should be picked as "Dublin" to the final file;

"NI.xls"

Supplier number	Occ	Tpnd	Description	Depot number	Depot name	Case size	Forecast date	Order date	Delivery date	Forecast cases
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	19/10/2019	21/10/2019	2
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	10/10/2019	11/10/2019	8
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	05/10/2019	07/10/2019	21
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	09/10/2019	10/10/2019	9
…	…	…	…	…	…	…	…	…	…	...

<tbody>
</tbody>

The Depot name "NI BELFAST FRESH PBL" should be picked as "Belfast" to the final file;

"Ocado.xls"

Forecast Delivery Date	Delivery Place	Order Group	SKU	Supplier Line Number	Product Description	Case Barcode	Forecast Order Qty (Cases)
06 October 2019 05:00:00	Dordon	Do: HFS	77760011	77760011	Ocado Lean Beef Steak Mince 5% Fat (500 GR)	15055004195	60
06 October 2019 05:00:00	Dordon	Do: HFS	296274011	296274011	Eden Beef Steak Mince 15% Fat (500 GR)	05391810242	38
06 October 2019 08:30:00	Hatfield	Ha: HFS	435984011	435984011	Eden Lean Beef Steak Mince 5% Fat (400 GR)	05391811102	51
07 October 2019 08:00:00	Erith CFC	Er: HFS	72581011	72581011	Ocado 4 Quarter Pounder Beef Burgers (454 GR)	15055002382	2
07 October 2019 08:00:00	Erith CFC	Er: HFS	402166011	402166011	Eden Aberdeen Angus Beef Roasting Joint (1.5 KG)	95391811068	6
…	…	…	…	…	…	…	…

<tbody>
</tbody>

As you for sure noticed the "Product" on the final file has a different format/id than in the three files "Tpnd" and "SKU". That's because for each product from our costumers we use a specific internal code (Retail code). The match between our suppliers code and our RT codes is made in a different file called "Week Forecast" and has the bellow aspect.

TPND	Code	Description
22918725	RPP001	T GAMMON STKS WT CARMELISED ONION MPQAS
	RPP001CP	TESCO GAMMON STEAK WITH CARMELISED CP
	RPP001WP	WIP TESCO GAMMON STEAK WITH CARMELISED
22918656	RPP002	TESCO BACON CHOPS WT MSTRD BUTTER MPQAS
	RPP002CP	TESCO BACON CHOPS WITH MUSTARD BUTTR CP
	RPP002WP	WIP TESCO BACON CHOPS WITH MUSTARD BUTTR
	RPP003	MARKET VALUE PALE BACK BACON JOINT
	RPP003WP	WIP MARKET VALUE PALE BACK BACON JOINT
	RPP004	MARKET VALUE PALE HAM FILLET
	RPP004WP	WIP MARKET VALUE PALE HAM FILLET
23966061	RPP005	TESCO THICK CUT SMKD RASHERS 250G MPQAS
	RPP005WP	WIP SMOKED THICKCUT BACK BACON RASHERS
23966147	RPP006	TESCO THICKCUT MAPLE RASHERS 250G MPQAS
	RPP006WP	WIP THICKCUT MAPLE BACK BACON RASHERS

<tbody>
</tbody>

Some of the codes are inactive. The only active code is the one in front of the Tpnd code. The macro should match the code from the supplier and pick the correspondent Retail Code to the final file.

Thanks a lot guys!

Kind regards,
Cristian

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...a-3-files-create-final-file-restrictions.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.