Complicated Excel Sheet including formula and VB Script

[anuradhagrewal]

Dear Experts
I have a complicated issue for which I need your help.
Attached is the csv file 01012020.csv

Problem
I need to import these csv files for each day of 2020 into an excel sheet and create an excel file filtered for each dept.
So for example I have imported csv files from 01012020 to 01032020 I need to create an excel file for all data by filtering for eg: Sales

Help Needed
I would be very grateful if a VB script be suggested where
1)I can import all the csv files (xxxxx(date).csv) into an existing workbook.
2)Now I can run a script where I can delete all other depts. except the one I need. For eg in all the sheets I need data only related to the "IT" dept and the script deletes all other depts mentioned in column C.
3)Then I create a master worksheet with all the names and I am able to do a vlookup based on the sheet name. For eg :In the master worksheet I have the name of all the persons Liam,
Noah, Oliver, William, Elijah, James and for everyday I can see how much percentage of data was used.
In other words when I do a Vlookup I should have the option to select based on the worksheet so I can drag it across all the columns.

Can anybody please please help

Regards

Anu

 

[Zot]

Nothing was attached as I see it from here

 

[anuradhagrewal]

I wanted to attach the csv and xls file but I am unable to do so.
Please help based on this.

Much Obliged

 

[Zot]

Too many data to recreate from picture. Use XL2BB to copy and paste. This way forumers can just copy paste your data.

Read here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[anuradhagrewal]

Too many data to recreate from picture. Use XL2BB to copy and paste. This way forumers can just copy paste your data.

Read here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Buddy
I am unable to upload or display the files as suggested by you in XL2bb

However I have posted the workbooks in google sheets to have a view

Kindly check it out:
Master Work book.xls:

Master worksheet

Sheet1 Name,01-Jan-20,02-Jan-20,03-Jan-20,06-Jan-20,07-Jan-20,08-Jan-20,09-Jan-20,10-Jan-20,13-Jan-20,14-Jan-20,15-Jan-20,16-Jan-20,17-Jan-20,20-Jan-20,21-Jan-20,22-Jan-20,23-Jan-20,24-Jan-20,27-Jan-20 Noah James Benjamin Henry Jacob Michael Jackson Jack Owen Matthew John Carter Gabriel Christopher

docs.google.com

CSV raw data file for filtering:

CSV sheet

01012020 1012020 Name,Dept,Data Inflow,Data Outflow,Percentage 20,Liam,Ops,74376,69881,106% 20,Noah,IT,85304,52364,163% 20,Oliver,Others,70775,55980,126% 20,William,Others,19574,72513,27% 20,Elijah,Others,77668,43310,179% 20,James,IT,4755,21424,22% 20,Benjamin,IT,57923,44762,129% 20,Lucas,Others...

docs.google.com

Please suggest it will be of great help and a life saver.

Regards

Anu

 

[Zot]

I have downloaded both files

The Master just have names in column A and daily dates across the rest of columns. The csv file has date in C1 (which I think in text sequence ddmmyyyy), names in column B, then Dept, Data Inflow, Data Outflow, Percentage.

Now you want to import data according to date and respective name, but what you need to import and to where? The Master sheet has name and date intersect. What you want to put into intersection?

 

[anuradhagrewal]

I have downloaded both files

The Master just have names in column A and daily dates across the rest of columns. The csv file has date in C1 (which I think in text sequence ddmmyyyy), names in column B, then Dept, Data Inflow, Data Outflow, Percentage.

Now you want to import data according to date and respective name, but what you need to import and to where? The Master sheet has name and date intersect. What you want to put into intersection?

Hi
This process is a regular process that has to be retrospectively. What I have to do is going forward that I have to build a usage percentage file for 2019,2020 and in 2021 at the end of every week (Friday) update the percentage used for each individual ---dept wise.
What I am most humbly proposing is that:
1)The master file (for 2020 and so on) will have all imported csv files(date wise in ddmmyyyy format).
2)Now once the csv files are imported there needs to be a VB Script that when it is called it filters all the CSV files(that have been imported in the master worksheet) except the master worksheet itself (Sheet 1) by dept wise(C2 onwards in the CSV file).
3)Then I plan to do a vlookup (two criteria Vlookup) in the master file such that it picks up for the given date (mentioned in C1 of every csv file) the percentage used for every name (mentioned in A2 onwards in the Master worksheet) and lists in a row the percentage(F2 in every csv sheet) used for that day of the given name.(end objective)
4)This I will have to do for every dept every week (on Friday going forward). Also if there is an addition or reduction of names the same needs to be factored out from the added csv files.(if that's possible)
5)Hence if it can be automated through a VB Script it will be much much helpful.

Thanks and Much appreciated.

Warm Regards

Anu

 

[Zot]

Importing data to Master from csv file to appropriate date column is not difficult, but you will end up with all data from csv into 4 columns (since 4 data columns). Then on next day another 4 columns in subsequent date. For a year total data would be humongous. Is this what you want?

Data to sort and process could be done almost immediate by using pivot table.

 

[anuradhagrewal]

Importing data to Master from csv file to appropriate date column is not difficult, but you will end up with all data from csv into 4 columns (since 4 data columns). Then on next day another 4 columns in subsequent date. For a year total data would be humongous. Is this what you want?

Data to sort and process could be done almost immediate by using pivot table.

Once I have the data in a row column format I can use the pivot.But I need all the data to be in 1 sheet.
Can it be done ... more specifically can u please help

 

[Zot]

It is possible that the same name appears more than once on same day in csv file?

There is also possibility that two person with same name within same dept if not in different dept, right?