Hello,
Trying to create a macro to carry out certain functions from a csv file. First step is to create a new worksheet (Sheet1) which is labelled as merged.
1) Use only a certain set of Headers from the csv file , currently doing this using WorkSheet.Function.Match
2) Copy two of the headers from the csv file to the new worksheet (merged), this is currently doable.
3) remove duplicates based on Column 2 (the name is duplicated). This is currently doable.
Problems:
1) I need the other selected headers from WorkSheet.Function.Match in the new worksheet
2) To do Sumif function in the new worksheet(merged) using data from the csv file, I have to do sumif for all the Match function selected headers.
Any help is greatly appreciated.
Thank you,
thekid
Trying to create a macro to carry out certain functions from a csv file. First step is to create a new worksheet (Sheet1) which is labelled as merged.
1) Use only a certain set of Headers from the csv file , currently doing this using WorkSheet.Function.Match
2) Copy two of the headers from the csv file to the new worksheet (merged), this is currently doable.
3) remove duplicates based on Column 2 (the name is duplicated). This is currently doable.
Problems:
1) I need the other selected headers from WorkSheet.Function.Match in the new worksheet
2) To do Sumif function in the new worksheet(merged) using data from the csv file, I have to do sumif for all the Match function selected headers.
Any help is greatly appreciated.
Thank you,
thekid
Code:
Sub merger()
'to extract required data and do the required calculations, the initial template is a csv file'
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "merged"
Sheets("Months").Activate
Sheets("Months").Select
' the csv file is somtimes messed up just correcting it here'
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.wraptext = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
' storing data from each of the matched headers for later use, the file consists a lot more headers, these are the ones I need'
project = WorksheetFunction.Match("PROJECT NAME", Rows("1:1"), 0)
name = WorksheetFunction.Match("PERSON_NAME", Rows("1:1"), 0)
clock = WorksheetFunction.Match("Clocked_in", Rows("1:1"), 0)
timeout = WorksheetFunction.Match("Time_Out", Rows("1:1"), 0)
timeallot = WorksheetFunction.Match("Time_Alloted", Rows("1:1"), 0)
vacation = WorksheetFunction.Match("Vacation_Time", Rows("1:1"), 0)
'Copy the project and person_name column to the Combined SEs sheet, would like other selected headers from above copied on the first row of the merged sheet'
Sheets("Months").Columns(project).Copy Destination:=Sheets("merged").Range("A1")
Sheets("Months").Columns(name).Copy Destination:=Sheets("merged").Range("B1")
' Remove duplicates from the Person_name, since the person there will be duplicates'
Sheets("merged").Activate
Sheets("merged").Select
Cells.RemoveDuplicates Columns:=2, Header:=xlYes
' Aim is to do sumif function using person_name from the merged sheet and finding the sumif from the MOnths person_name column'
Range("C2").Select
ActiveCell.FormulaR1C1 = WorksheetFunction.SumIf(Sheets("Months").Columns(name), Sheets("merged").Columns(name), Sheets("Months").Colums(clock_in))
End Sub