Good Day Everyone.
Need your expert guidance to be able to create a dynamic template that users will be filling information with.
In file "Inputs.xlsx" the "Input-1" sheet is what will go into every file based on "data filed" and "file name" from sheet "Input-2". This should create me 7 new files with file name as in "Unique File Name". Every column in "Data Field" needs to be appended as a column header to "Input-1".
The New headings for each file is as below:
DOTY-Dealer Standards CI.xlsx
ID | CealerCD | DealerName | Town | Region | Dist | Category | Dealer Standards CI |
1 | 1111 | Citrusdal | Citrusdal | 1 | 32 | E | |
2 | 0022 | Krugersdorp | Krugersdorp | 1 | 12 | B | |
3 | 1122 | Louis Trichardt | Louis Trichardt | 1 | 32 | D | |
4 | 1124 | Malmesbury | Malmesbury | 1 | 32 | E | |
5 | 1119 | Modimolle | Modimolle | 1 | 32 | E | |
6 | 1118 | Mokopane | Mokopane | 1 | 32 | D | |
7 | 9111 | Moorreesburg | Moorreesburg | 1 | 32 | E | |
8 | 9110 | North West | Lichtenburg | 1 | 32 | D | |
9 | 9110 | North West | Zeerust | 1 | 32 | D | |
10 | 9004 | Roodepoort | Roodepoort | 1 | 12 | B |
Overall Recognition-Best in CVP Sales.xlsx
ID | CealerCD | DealerName | Town | Region | Dist | Category | Best in CVP Sales |
1 | 1111 | Citrusdal | Citrusdal | 1 | 32 | E | |
2 | 0022 | Krugersdorp | Krugersdorp | 1 | 12 | B | |
3 | 1122 | Louis Trichardt | Louis Trichardt | 1 | 32 | D | |
4 | 1124 | Malmesbury | Malmesbury | 1 | 32 | E | |
5 | 1119 | Modimolle | Modimolle | 1 | 32 | E | |
6 | 1118 | Mokopane | Mokopane | 1 | 32 | D | |
7 | 9111 | Moorreesburg | Moorreesburg | 1 | 32 | E | |
8 | 9110 | North West | Lichtenburg | 1 | 32 | D | |
9 | 9110 | North West | Zeerust | 1 | 32 | D | |
10 | 9004 | Roodepoort | Roodepoort | 1 | 12 | B |
Overall Recognition-Best in CVP Service.xlsx
ID | CealerCD | DealerName | Town | Region | Dist | Category | Best in CVP Service |
1 | 1111 | Citrusdal | Citrusdal | 1 | 32 | E | |
2 | 0022 | Krugersdorp | Krugersdorp | 1 | 12 | B | |
3 | 1122 | Louis Trichardt | Louis Trichardt | 1 | 32 | D | |
4 | 1124 | Malmesbury | Malmesbury | 1 | 32 | E | |
5 | 1119 | Modimolle | Modimolle | 1 | 32 | E | |
6 | 1118 | Mokopane | Mokopane | 1 | 32 | D | |
7 | 9111 | Moorreesburg | Moorreesburg | 1 | 32 | E | |
8 | 9110 | North West | Lichtenburg | 1 | 32 | D | |
9 | 9110 | North West | Zeerust | 1 | 32 | D | |
10 | 9004 | Roodepoort | Roodepoort | 1 | 12 | B |
and so on so forth....
refer to the inputs for details.
Thanks & Regards,
Chinmaya.
Solved! Go to Solution.
Hello @chinu267 ,
Hope this does the trick!
It takes the sheet name after the dash and applies it as a new column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
You did not take into account input-2 that will be used as a column....
Thanks again for your efforts..let me re explain the requirements
If you look at my expected output, I need to add an extra column for each data field onto sheet "input-1" and that file has to be saved as the unique file name for every individual instance.
Regret, the columns from sheet below needs to be added to the data in input-1 and for every row under data field has to be written to a new file. Say if data field is "Academy", it needs to be added as a column to sheet "Input-1" as
"
ID | CealerCD | DealerName | Town | Region | Dist | Category | Academy |
"
and that needs to be saved as excel "Sales Merit-Academy.xlsx"
Data Field |
% use of CoOp funds |
Academy |
Academy TNA completed |
Best in CVP Sales |
Best in CVP Service |
Dealer Standards ALL |
Dealer Standards CI |
Sorry for the trouble. Sent you a msg with my number
Please ignore the previous message. It works. Now I can connect to the DB and process all 140 odd files I believe. This was a sample of 10 records. DB has much more.
Really appreciate your help.
Glad it helped 🙂
Have a nice weekend! 😄