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

 

[anuradhagrewal]

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?

It is possible that the same name appears more than once on same day in csv file?
No it is unique for a day

There is also possibility that two person with same name within same dept if not in different dept, right?
No as the system gives then a number like John1

 

[Zot]

It is possible that the same name appears more than once on same day in csv file?
No it is unique for a day

There is also possibility that two person with same name within same dept if not in different dept, right?
No as the system gives then a number like John1

The reason I asked was that when I ran my macro, it found same name more than once. Just need to verify if any possibility

 

[Zot]

Since there are 4 data columns, then destination has to have the same number of column. The Master worksheet, which I name as Master also, would be like below. Only 1st date to be filled. The 2nd date would be just =B2+1 ans so on

01-Jan-20

02-Jan-20

03-Jan-20

04-Jan-20

Name

Dept

Data Inflow

Data Outflow

Percentage

Dept

Data Inflow

Data Outflow

Percentage

Dept

Data Inflow

Data Outflow

Percentage

Dept

Data Inflow

The code would be

Sub GetDataCSV()

Dim k&, l&, eRow&, nRow&, eCol&, nCol&
Dim strDate$, NameDataX$, strCol$
Dim DateX As Date
Dim Fname As Variant
Dim dName As Object
Dim cell As Range, rngDate As Range, rngLast As Range
Dim cellMasterName As Range, cellDataName As Range
Dim rngNameMaster As Range, rngNameData As Range
Dim wsMaster As Worksheet, wsData As Worksheet
Dim wbMaster As Workbook, wbData As Workbook

' Create dictionary for list of name
Set dName = CreateObject("Scripting.Dictionary")

' Define Master workbook and worksheet as variable
Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Sheets("Master")

' Find last column with date and define date range
Set cell = wsMaster.Cells(1, wsMaster.Columns.Count).End(xlToLeft)
Set rngDate = wsMaster.Range("B1", cell)

' Select source csv data file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

' Define source data workbook and worksheet as variable
Set wbData = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsData = wbData.ActiveSheet

wbMaster.Application.ScreenUpdating = False

' Convert string date C1 in CSV file to date variable DateX
strDate = wsData.Range("C1")
If Len(strDate) = 7 Then k = 2: l = 1 Else: k = 3: l = 2
DateX = DateSerial(CInt(Right(strDate, 4)), CInt(Mid(strDate, 2, 2)), CInt(Left(strDate, l)))

' Find last Data row
Set cell = wsData.Range("B" & wsData.Cells.Rows.Count).End(xlUp)
Set rngNameData = wsData.Range("B3", cell)

' Find date column
For Each cell In rngDate
    If cell = DateX Then
        strCol = Split(cell.Address, "$")(1)
        Exit For
    End If
Next

For Each cellDataName In rngNameData

If Not Len(wsMaster.Range("A3")) = 0 Then
    Set cell = wsMaster.Range("A" & wsMaster.Cells.Rows.Count).End(xlUp)
    Set rngNameMaster = wsMaster.Range("A3", cell)
    Set cell = rngNameMaster.Find(cellDataName.Text, LookAt:=xlWhole)
    If cell Is Nothing Then
        nRow = wsMaster.Range("A" & wsMaster.Cells.Rows.Count).End(xlUp).Row + 1
    Else
        nRow = cell.Row
    End If
    If Not wsMaster.Range(strCol & nRow) = 0 Then
        MsgBox "Name " & cellDataName.Text & " appeared morw than once in csv file"
        End
    End If
Else
    nRow = 3
End If
    wsMaster.Range("A" & nRow) = wsData.Range("B" & cellDataName.Row)
    wsData.Range("C" & cellDataName.Row, "F" & cellDataName.Row).Copy Destination:=wsMaster.Range(strCol & nRow)
    
Next
wbData.Close False
'wsMaster.Select
wbMaster.Application.ScreenUpdating = True

End Sub

 

[anuradhagrewal]

Hi There
Thanks for your effort.
But I am actually looking for this as mentioned below.
Once again much much obliged

Greatly Appreciated

Anu

Name

02-Jan-20

03-Jan-20

04-Jan-20

05-Jan-20

06-Jan-20

07-Jan-20

08-Jan-20

09-Jan-20

10-Jan-20

11-Jan-20

12-Jan-20

13-Jan-20

14-Jan-20

16-Jan-20

(Person of particular Dept only)

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

Percentage

 

[Zot]

So basically you just want to extract out Percentage data from csv file? You already have the number daily for each person in each Dept. Unless you store in some kind of database, you do not want to get data from csv file one by one.

Sorry, I don't get what you are trying to achieve here. What is your repetitious routine that you do that need VBA for?

 

[anuradhagrewal]

Firstly thanks for your time.

Now let me explain the points raised in your query.

Firstly the CSV files are generated date wise: CSV sheet

The data in these files is consolidated as you can see and I can get it Name wise corresponding to their dept. for that given date.

Now I have to create a individual master file for all the Depts like : Ops ,IT, Sales :Master worksheet

Thus there are three variables Name being the unique and Dept and date being common factors to be picked up from a given day CSV file.
This csv file will be for every day and hence has to repetitive.

Henceforth I need to consolidate all the data into one Master file that can be updated Dept wise. In other words there will be a master file for SALES, IT and so on.​

Thanks for your time and effort.

Best Wishes

Anu

 

[Zot]

Firstly thanks for your time.

Now let me explain the points raised in your query.

Firstly the CSV files are generated date wise: CSV sheet

The data in these files is consolidated as you can see and I can get it Name wise corresponding to their dept. for that given date.

Now I have to create a individual master file for all the Depts like : Ops ,IT, Sales :Master worksheet

Thus there are three variables Name being the unique and Dept and date being common factors to be picked up from a given day CSV file.
This csv file will be for every day and hence has to repetitive.

Henceforth I need to consolidate all the data into one Master file that can be updated Dept wise. In other words there will be a master file for SALES, IT and so on.​

Thanks for your time and effort.

Best Wishes

Anu

So you want to extract data by Dept from csv and save on each Master workbook. Therefore, each Dept Master workbook will have column
Name (presumed column A) and subsequent column would be Date on 1st row and just Percentage for each person underneath that Date?

 

[anuradhagrewal]

Exactly my dear

 

[Zot]

Exactly my dear

At last ?. I think best approach would be like creating new workbook for each Dept if not existed yet and fill data accordingly in one go. However, right now I'm so busy with my job task. I'm answering to this forum in between task during office hour .. not from home ?. Have something urgent at the moment.

 

[anuradhagrewal]

Hey No problem
Take your time but "one go" is possible only for 2020 2019 not for going forward.
Whenever you are in a free space do keep my request in mind and help me out.
I will do it manually till then but trust me its not error free and takes a lot of time
I'll be waiting in earnest