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
 

anuradhagrewal

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

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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Zot

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,154
Office Version
  1. 2016
Platform
  1. Windows
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-2002-Jan-2003-Jan-2004-Jan-20
NameDeptData InflowData OutflowPercentageDeptData InflowData OutflowPercentageDeptData InflowData OutflowPercentageDeptData Inflow

The code would be

VBA Code:
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

New Member
Joined
Dec 3, 2020
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
Hi There
Thanks for your effort.
But I am actually looking for this as mentioned below.
Once again much much obliged

Greatly Appreciated

Anu

Name02-Jan-2003-Jan-2004-Jan-2005-Jan-2006-Jan-2007-Jan-2008-Jan-2009-Jan-2010-Jan-2011-Jan-2012-Jan-2013-Jan-2014-Jan-2016-Jan-20

(Person of particular Dept only)
PercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentagePercentage
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,154
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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? :unsure:
 

anuradhagrewal

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,154
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,154
Office Version
  1. 2016
Platform
  1. Windows
Exactly my dear :love: :love: :love:
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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

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