Macro that takes data from 3 files and create a final file with restrictions

Cris_93

Cris_93

New Member
Joined
Nov 1, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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.


[TABLE="width: 0"]
<tbody>[TR]
[TD]Depot/StoreFormat
[/TD]
[TD]Product
[/TD]
[TD]FactType
[/TD]
[TD]Unit
[/TD]
[TD]Date
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]Dublin
[/TD]
[TD]RTP088
[/TD]
[TD]Customer Forecast
[/TD]
[TD]Cases
[/TD]
[TD]02/11/2019
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]Belfast
[/TD]
[TD]RTB222
[/TD]
[TD]Customer Forecast
[/TD]
[TD]Cases
[/TD]
[TD]12/11/2019
[/TD]
[TD]32
[/TD]
[/TR]
[TR]
[TD]Hatfield
[/TD]
[TD]RTP088
[/TD]
[TD]Customer Forecast
[/TD]
[TD]Cases
[/TD]
[TD]07/11/2019
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Dordon
[/TD]
[TD]RTL008
[/TD]
[TD]Customer Forecast
[/TD]
[TD]Cases
[/TD]
[TD]05/11/2019
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[/TR]
</tbody>[/TABLE]

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"

[TABLE="width: 1541"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Supplier number[/TD]
[TD="align: center"]Category area[/TD]
[TD="align: center"]Star line[/TD]
[TD="align: center"]Tpnb[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Tpnd[/TD]
[TD="align: center"]Case size[/TD]
[TD="align: center"]Occ[/TD]
[TD="align: center"]Depot number[/TD]
[TD="align: center"]Depot name[/TD]
[TD="align: center"]Forecast date[/TD]
[TD="align: center"]Order date[/TD]
[TD="align: center"]Delivery date[/TD]
[TD="align: center"]Forecast cases[/TD]
[/TR]
[TR]
[TD="align: center"]5997500[/TD]
[TD="align: center"]MFP[/TD]
[TD="align: center"]N [/TD]
[TD="align: center"]52440995[/TD]
[TD="align: center"]PORK MINCE 500G[/TD]
[TD="align: center"]31411873[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]5.05E+12[/TD]
[TD="align: center"]735[/TD]
[TD="align: center"]BALLYMUN FRESH PBL [/TD]
[TD="align: center"]03/10/2019[/TD]
[TD="align: center"]04/10/2019[/TD]
[TD="align: center"]05/10/2019[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]5997500[/TD]
[TD="align: center"]MFP[/TD]
[TD="align: center"]N [/TD]
[TD="align: center"]52445008[/TD]
[TD="align: center"] IRISH LAMB MINCE 15% FAT 533G[/TD]
[TD="align: center"]37216980[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5.06E+12[/TD]
[TD="align: center"]735[/TD]
[TD="align: center"]BALLYMUN FRESH PBL [/TD]
[TD="align: center"]03/10/2019[/TD]
[TD="align: center"]04/10/2019[/TD]
[TD="align: center"]07/10/2019[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]5997500[/TD]
[TD="align: center"]MFP[/TD]
[TD="align: center"]N [/TD]
[TD="align: center"]52483552[/TD]
[TD="align: center"]T. ROUND STEAK BEEF MINCE 10% FAT 554G[/TD]
[TD="align: center"]34945576[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]5.06E+12[/TD]
[TD="align: center"]735[/TD]
[TD="align: center"]BALLYMUN FRESH PBL [/TD]
[TD="align: center"]03/10/2019[/TD]
[TD="align: center"]16/10/2019[/TD]
[TD="align: center"]17/10/2019[/TD]
[TD="align: center"]102[/TD]
[/TR]
[TR]
[TD="align: center"]5997500[/TD]
[TD="align: center"]MFP[/TD]
[TD="align: center"]N [/TD]
[TD="align: center"]63755738[/TD]
[TD="align: center"]T. FIN* IRISH CANADIAN MAPLE RASHERS 240G[/TD]
[TD="align: center"]32607750[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]5.06E+12[/TD]
[TD="align: center"]735[/TD]
[TD="align: center"]BALLYMUN FRESH PBL [/TD]
[TD="align: center"]03/10/2019[/TD]
[TD="align: center"]21/10/2019[/TD]
[TD="align: center"]22/10/2019[/TD]
[TD="align: center"]48[/TD]
[/TR]
[TR]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[/TR]
</tbody>[/TABLE]

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

"NI.xls"

[TABLE="width: 0"]
<tbody>[TR]
[TD]Supplier number
[/TD]
[TD]Occ
[/TD]
[TD]Tpnd
[/TD]
[TD]Description
[/TD]
[TD]Depot number
[/TD]
[TD]Depot name
[/TD]
[TD]Case size
[/TD]
[TD]Forecast date
[/TD]
[TD]Order date
[/TD]
[TD]Delivery date
[/TD]
[TD]Forecast cases
[/TD]
[/TR]
[TR]
[TD]6242200
[/TD]
[TD]2.0955E+11
[/TD]
[TD] 28798712
[/TD]
[TD]T.FIN 1 BEEF RIBEYE STEAK
[/TD]
[TD]835
[/TD]
[TD]NI BELFAST FRESH PBL
[/TD]
[TD]12
[/TD]
[TD]03/10/2019
[/TD]
[TD]19/10/2019
[/TD]
[TD]21/10/2019
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6242200
[/TD]
[TD]2.0955E+11
[/TD]
[TD]28798712
[/TD]
[TD]T.FIN 1 BEEF RIBEYE STEAK
[/TD]
[TD]835
[/TD]
[TD]NI BELFAST FRESH PBL
[/TD]
[TD]12
[/TD]
[TD]03/10/2019
[/TD]
[TD]10/10/2019
[/TD]
[TD]11/10/2019
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]6242200
[/TD]
[TD]2.0955E+11
[/TD]
[TD]28798712
[/TD]
[TD]T.FIN 1 BEEF RIBEYE STEAK
[/TD]
[TD]835
[/TD]
[TD]NI BELFAST FRESH PBL
[/TD]
[TD]12
[/TD]
[TD]03/10/2019
[/TD]
[TD]05/10/2019
[/TD]
[TD]07/10/2019
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]6242200
[/TD]
[TD]2.0955E+11
[/TD]
[TD]28798712
[/TD]
[TD]T.FIN 1 BEEF RIBEYE STEAK
[/TD]
[TD]835
[/TD]
[TD]NI BELFAST FRESH PBL
[/TD]
[TD]12
[/TD]
[TD]03/10/2019
[/TD]
[TD]09/10/2019
[/TD]
[TD]10/10/2019
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]…
[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]
The Depot name "NI BELFAST FRESH PBL" should be picked as "Belfast" to the final file;

"Ocado.xls"

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

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.


[TABLE="width: 0"]
<tbody>[TR]
[TD]TPND
[/TD]
[TD]Code
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]22918725
[/TD]
[TD]RPP001
[/TD]
[TD]T GAMMON STKS WT CARMELISED ONION MPQAS
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP001CP
[/TD]
[TD] TESCO GAMMON STEAK WITH CARMELISED CP
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP001WP
[/TD]
[TD]WIP TESCO GAMMON STEAK WITH CARMELISED
[/TD]
[/TR]
[TR]
[TD]22918656
[/TD]
[TD]RPP002
[/TD]
[TD]TESCO BACON CHOPS WT MSTRD BUTTER MPQAS
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP002CP
[/TD]
[TD]TESCO BACON CHOPS WITH MUSTARD BUTTR CP
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP002WP
[/TD]
[TD]WIP TESCO BACON CHOPS WITH MUSTARD BUTTR
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP003
[/TD]
[TD]MARKET VALUE PALE BACK BACON JOINT
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP003WP
[/TD]
[TD]WIP MARKET VALUE PALE BACK BACON JOINT
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP004
[/TD]
[TD]MARKET VALUE PALE HAM FILLET
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP004WP
[/TD]
[TD]WIP MARKET VALUE PALE HAM FILLET
[/TD]
[/TR]
[TR]
[TD]23966061
[/TD]
[TD]RPP005
[/TD]
[TD]TESCO THICK CUT SMKD RASHERS 250G MPQAS
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP005WP
[/TD]
[TD]WIP SMOKED THICKCUT BACK BACON RASHERS
[/TD]
[/TR]
[TR]
[TD]23966147
[/TD]
[TD]RPP006
[/TD]
[TD]TESCO THICKCUT MAPLE RASHERS 250G MPQAS
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]RPP006WP
[/TD]
[TD]WIP THICKCUT MAPLE BACK BACON RASHERS
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
Hi there :)
On Ocado file the column that has the date is formatted as "General". I change it to "date" and ran again the macro but the issue persist.

I did what you asked and saved the file in .csv and opened with notepad and the date is stored as below:
1586344634452.png


Should it be saved as a number like "43190" or it actually should be a date?

Thanks for all your patience seriously,
Cristian
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
If you format a column in your worksheet as Short Date and put =NOW() in it, does the date appear in dd/mm/yyyy format?
If you format a column in the Ocado workbook as Short Date and put =NOW() in it, does the date appear in dd/mm/yyyy format?

So it looks as if the dates in the Ocado.xls file are stored as dd/mm/yyyy, but according to this:
From:

VBA Macros - Region and Language

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.
bettersolutions.com bettersolutions.com
Date Literals
Date literals ignore the WRS [Windows Regional Settings] and are always in US format (mm/dd/yyyy)
When any other format different to mm/dd/yyyy is entered Excel will automatically convert it to the #mm/dd/yyyy# format.
It is much safer to avoid using date literals altogether and use VBA functions instead (DateSerial, DateValue)

I was wrong about how Excel interprets dates:

So perhaps if you insert a row in the Ocado file with a date that has to be in dd/mm/yyyy format (such as 30/01/2020 -- which looks terribly odd to me),
copy the date block, including that row, paste the date block in your worksheet it will "convince" Excel to see the rest of the dates in that format.
Then delete the 30/01/2020 row and continue processing.

I am hoping that when Excel sees the first date as dd/mm/yyyy it interprets the remaining dates as having the same format. There are a few other options to try mentioned in the code.

See if this update to your import code corrects the problem:

VBA Code: 
    'Your Code - Opening the Ocado File
    Workbooks.Open Filename:= _
        "\\IEDRGSFS01\data\Planning\production plans\production plans\Forecast from Tesco Connect\Ocado.xls"
    'Insert a blank row above the part that you are going to copy
    Range("A6:H6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Fill the cells of the blank row
    Range("A6:H6").Resize(1, 8).Value = Array(#1/30/2020#, "X", "X", "X", "X", "X", "X", "X")
    'Continue with your code, but including copying the added row
    btRw = Range("A65536").End(xlUp).Offset(0, 0).Row
    Range("$A$5:$H$" & btRw).Copy
    Windows(repFl).Activate
    Sheets("Ocado").Select
    btRw = Range("A65536").End(xlUp).Offset(0, 0).Row
    Range("A" & btRw).Offset(1, 0).Select
    ActiveSheet.Paste
    
    'May want to try either of the following to see if it helps instead of ActiveSheet.Paste
    '  if just the extra row did not help
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    'Delete the added row
     Range(Range("A" & btRw).Offset(2, 0), Range("H" & btRw).Offset(2, 0)).Delete Shift:=xlUp
    
    'Continue with your code
    Application.CutCopyMode = False
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    'What is this chunk for?
    Windows("Ocado.xls").Activate
    Range("$F$6:$F$" & btRw).Copy
    
    'Closing the file
    Windows("Ocado.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = False
 
Upvote 0
Even easier" Add local:=True to your Workbooks.Open statement

VBA Code: 
Workbooks.Open Filename:= _
        "\\IEDRGSFS01\data\Planning\production plans\production plans\Forecast from Tesco Connect\Ocado.xls", local:=True
 
Upvote 0
It is working now!!!! :D

YESSS!! Thank you so much my friend!!

You helped me so much!

Best regards,
Cristian
 
Upvote 0
You must log in or register to reply here.

Similar threads

Cris_93
  • Locked
  • Question Question
Macro to built final file from 3 different files
Replies
1
Views
595
Fluff
Fluff

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top