+ Reply to Thread
Results 1 to 4 of 4

add countif to my code to sum duplicated data

  1. #1
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    418

    add countif to my code to sum duplicated data

    hello
    i have this code works very well but the problem when get data from multiple files there are duplicated data i would merge them and sum the values i highlight the duplicated data in my file
    HTML Code: 
    Sub Consolidation()
    
    Dim CurrentBook As Workbook
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Sheets("sheet1")
    Dim IndvFiles As FileDialog
    Dim FileIdx As Long
    Dim i As Integer, x As Integer
    
    Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
    With IndvFiles
        .AllowMultiSelect = True
        .Title = "Multi-select target data files:"
        .ButtonName = ""
        .Filters.Clear
        .Filters.Add ".xlsx files", "*.xlsx"
        .Show
    End With
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    For FileIdx = 1 To IndvFiles.SelectedItems.Count
        Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileIdx))
        For Each Sheet In CurrentBook.Sheets
            Dim LRow1 As Long
            LRow1 = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
            Dim LRow2 As Long
            LRow2 = CurrentBook.ActiveSheet.Range("A" & CurrentBook.ActiveSheet.Rows.Count).End(xlUp).Row
    
            Dim ImportRange As Range
            Set ImportRange = CurrentBook.ActiveSheet.Range("A2:d" & LRow2)
          ' t = Application.WorksheetFunction.CountIf(Workbooks("77.xlsx").Worksheets("sheet1").Range("d:d"), "<>")
    
            ImportRange.Copy
            WS.Range("A" & LRow1 + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next
        CurrentBook.Close False
    Next FileIdx
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: add countif to my code to sum duplicated data

    With a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    418

    Re: add countif to my code to sum duplicated data

    thanks ,oeldere for your help but in my opinion when add just line code about function count if is best because i use code it's practical why i use another way by pivot table as i use the code

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: add countif to my code to sum duplicated data

    Then record the macro using the formula and add it on your code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] amend code merge duplicated data
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-25-2020, 05:09 PM
  2. vba countif duplicated the values for two columns
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-19-2020, 05:06 PM
  3. Macro shows duplicated entries but sometimes they are not duplicated due to data.
    By tweacle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2020, 04:47 PM
  4. [SOLVED] VB Code to highhight duplicated data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-07-2018, 03:07 AM
  5. Replies: 6
    Last Post: 02-04-2016, 05:51 AM
  6. SUMIF & COUNTIF - Without Duplicated Values
    By xaviersun in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-22-2015, 09:20 AM
  7. Using Countif to return un-duplicated instances
    By dforte in forum Excel General
    Replies: 8
    Last Post: 11-19-2010, 09:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1