Complicated Excel Sheet including formula and VB Script

anuradhagrewal

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
Nothing was attached as I see it from here ☺️
 

anuradhagrewal

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
I wanted to attach the csv and xls file but I am unable to do so.
Please help based on this.

Much Obliged
 

Attachments

  • CSV files.png
    CSV files.png
    28.1 KB · Views: 4
  • Masterworksheey.png
    Masterworksheey.png
    151.1 KB · Views: 4

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
Too many data to recreate from picture. Use XL2BB to copy and paste. This way forumers can just copy paste your data.

Read here

 

anuradhagrewal

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Read here

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:

CSV raw data file for filtering:

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

Regards

Anu
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,311
Messages
5,635,482
Members
416,859
Latest member
GowthamiSita

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
Top