• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif function looking up values from another worksheet

thekid

New Member
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

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
 

Attachments

  • test.xlsx
    12.9 KB · Views: 6
The easy way is to start with an already created result worksheet with contains the needed headers​
rather than hard coded headers within the VBA procedure 'cause when you need to add or remove​
a header that no needs to amend the procedure but just the worksheet.​
Or you must be sure to never need any change in the future … So ?​
There are different ways to achieve your need, depending on the real size of the source text file,​
your Excel / VBA skills to maintain the code, …​
As here according to your attachment where the source text file is missing and the few rows in the 'CSV' worksheet​
the easy way is to just automate what any beginner can yet operate manually just using the Excel basics.​
If you have not yet receive any answer from any forum - except for those who saw your wild cross posting - is​
maybe 'cause your explanation is not very clear, so you must better elaborate your need.​
1) Use only a certain set of Headers from the csv file
Should we guess ? No as we are not on a mind readers forum …​
2) Copy two of the headers from the csv file to the new worksheet
Same as above, too evasive …​
3) remove duplicates based on Column 2 (the name is duplicated).
Same player shoot again ! Which column 2 ? From source file or result worksheet ?​
As both have nothing to do with 'name' !​
Problems:
1) I need the other selected headers
'selected' has a particular meaning in Excel but should we guess that's not really what you mean ?​
So when a thread is not explained as any forum expect for in the initial post,​
you can cross post in other forums but if you just copy / paste your first post​
as it is without well thinking / elaborating / rewriting your need, what could you expect more ? …​
 
My Apologies on the shoddy job I have done of posting incomplete information.

Or you must be sure to never need any change in the future … So ?
Yes, I did think about this. But the number of headers I require will never change nor the format of the current source file (csv file) with the existing headers.
There are different ways to achieve your need, depending on the real size of the source text file, your Excel / VBA skills to maintain the code, …
Currently the source file (csv format) is close to 104 MB, and will only increase till the end of the year.

Rewriting the thread:

Trying to create a macro to automate SUMIF function from the source file (csv file) to a new worksheet (named here as “merged”).

There are certain set of headers from the source file (example attached; tab: “csv”) which will need to be copied to the new worksheet (named “merged”) the headers to be copied are : Project Name, Person Name, Clocked_in, Time_Out, Time_Alloted, Vacation_Time

Since the “Project Name and Person Name” data do not change in the source file the entire column can be copied directly with the data to the new worksheet (named “merged”). But there will be duplicates in both Person Name and Project Name which will need to be removed in the new worksheet for the next step (SUMIF function).

The duplicates will need to be removed in the new worksheet to allow SUMIF function based on the Person Name copied into the worksheet:
SUMIF (range, criteria, [sum_range]) in the current scenario it would be:

(SUMIF(source_file(Person_Name), worksheet(Person_Name), source_file(criteria))

The “criteria” between the source file and worksheet will be the remaining headers:
Clocked_in, Time_Out, Time_Alloted, Vacation_Time

Really hoping, that I have been able to convey the problem.
 
Yes, now it's crystal clear !​
But according to​
Currently the source file (csv format) is close to 104 MB, and will only increase till the end of the year.
How many rows in this source file actually ?​
Aren't you afraid that the rows # will be greater than a worksheet can load ?​
 
The source file consists of 116,000 - 235,000 rows in total. These are historical files and I have not found any that has more then these rows.

The number of columns in each source file is close to 200 - 250 header names. Initial plan was to create a script to open each csv file (source file) and pull the data (required headers), but since the number of rows would exceed the worksheet, the option of collating each csv separately was thought of.
 
So according to your initial attachment a starter demonstration to just allocate the SUMIF results to columns B:E :​
Code:
Sub Demo0()
      Const D = "merged", S = "CSV"
        Dim V, F$, Rc As Range
    With Sheets(S).UsedRange
            V = Application.Match(Sheets(D).[A1], .Rows(1), 0):  If IsError(V) Then Beep: Exit Sub
            F = "=SUMIF('" & S & "'!" & .Columns(V).Address & ",'" & D & "'!$A2,'" & S & "'!"
        For Each Rc In Sheets(D).UsedRange.Columns("B:E")
                V = Application.Match(Rc.Cells(1), .Rows(1), 0)
            If IsNumeric(V) Then
                     V = .Columns(V).Address
                With Range(Rc.Cells(2), Rc.Cells(Rc.Cells.Count))
                    .Formula = F & V & ")"
                    .Formula = .Value2
                End With
            End If
        Next
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
This is works, but the !A2 needs to iterate through the names till blank space.
F = "SUMIF('" & S & "'!" & .Columns(V).Address & ",'" & D & "'!A2,'" & S & "'!" For Each Rc In Sheets(D).UsedRange.Columns("B:E")
 
but since the number of rows would exceed the worksheet, the option of collating each csv separately was thought of.
Last week I have uploaded in a worksheet a text file which sizes 243 Mb and around 5.3 millions of rows​
so that's very not a concern except the time needed to process data …​
For big data or several text files the principle is to not directly load the files in a worksheet but proceed in memory.​
Anyway according to your attachment where Sheet2 (Merged) worksheet already exists​
another starter demonstration which may replace your initial procedure :​
Code:
Sub Demo1()
        Dim H, V, F$, C%, R&, A$
    With Sheet1.UsedRange
            H = Split("PERSON_NAME CLOCKED_IN TIME_OUT TIME_ALLOTED VACATION_TIME")
            V = Application.Match(H, .Rows(1), 0):  If IsError(V(1)) Then Beep: Exit Sub
            F = "=SUMIF(" & .Columns(V(1)).Address(External:=True) & ",'" & Sheet2.Name & "'!$A2,"
        For C = 2 To UBound(V)
            If IsError(V(C)) Then H(C - 1) = False: V(C) = False
        Next
            H = Filter(H, False, False):  V = Filter(V, False, False)
            Sheet2.UsedRange.Clear
            Sheet2.[A1].Resize(, UBound(H) + 1) = H
           .Columns(Val(V(0))).AdvancedFilter xlFilterCopy, , Sheet2.[A1], True
            R = Sheet2.UsedRange.Rows.Count - 1:  If R = 0 Then Exit Sub
            Application.ScreenUpdating = False
        For C = 1 To UBound(V)
                 A = .Columns(Val(V(C))).Address(External:=True)
            With Sheet2.Cells(2, C + 1).Resize(R)
                .Formula = F & A & ")"
                .Formula = .Value2
            End With
        Next
            Application.ScreenUpdating = True
    End With
End Sub
You may Like it !​
 
Back
Top