Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclude duplicates master spreadsheet

    Trying to help a user exclude duplicate entries from several sheets using a master sheet with totals. Each individual sheet calculates correctly using the following formula, =SUMPRODUCT((D721<>"")/COUNTIF(D721,D721&"")). How do I use a formula like this to total from 10 worksheets? The following formula from the total page does not work as it just sums D5 from each of the schools. =SUM('North Schuykill'!D5,Shamokin!D5,Shikellamy!D5,Pottsville !D5,'Mount Carmel'!D5,Mahanoy!D5,Berwick!D5,Milton!D5)

    My apologies for any confusion as I am out of my comfort zone with this problem.
    Thanks

    I have attached spreadsheet, thank you for any assistance.
    Attached Files Attached Files
    Last edited by mlynnmills; 2015-03-03 at 13:20. Reason: Attached spreadsheet

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    The system converted something you typed to a smiley face (probably a colon and D21).

    If you're trying to sum from D7 to D21 on all of the sheets, and the sheets are contiguous, try this:

    =sum('North Schuykill:Milton'!D7 : D21)

    [no spaces around the colon]
    Last edited by kweaver; 2015-02-27 at 12:06.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    To prevent the smiley faces from use of the colon enclose the formulas in NOPARSE tags {noparse} formula here {/noparse}
    Just REPLACE the {} with [] for it to work properly.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Is there a code for strikethrough?


    zeddy

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    I can't find one. You can find a full list of codes HERE. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG: Is there a list of these codes somewhere on the site?

    Oops...overlap...thanks!
    Last edited by kweaver; 2015-02-27 at 12:55.

  7. #7
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you the timesaving tip. Much appreciated. How can I incorporate the countif function into the sum for all schools? Each school district has a sheet and within the sheet there are students names and the schools they applied to. If 10 students from North Schuykill each applied to the same university, we only want to count this university one time. Countif works on the school district sheets however the total sums each sheet and therefore each university is being counted several times as students from any of the high schools could potentially apply to the same university. We only want to count each university one time on the total page.

    Thank you

  8. #8
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Spreadsheet uploaded to initial post. Would greatly appreciate any assistance. Thank you so much for your expertise.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    mlynnmills,

    The following code will calculate the number of unique colleges throughout the workbook that the students applied to. The number will be updated on the Totals sheet when the workbook opens and each time the Totals sheet is activated. There is a hidden sheet called "Settings" and must remain as the first sheet.

    HTH,
    Maud

    Place in a standard module:
    Code:
    Public Sub UniqueColleges()
    Application.ScreenUpdating = False
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Settings")
    ws1.Cells.ClearContents
    Row = 1
    '--------------------------------
    'LIST ALL COLLEGES ON HIDDEN SHEET
    For I = 3 To Worksheets.Count
        With Worksheets(I)
        LastRow = .Cells(Rows.Count, 4).End(xlUp).Row
        For J = 7 To LastRow
            ws1.Cells(Row, 1) = .Cells(J, 4)
            Row = Row + 1
        Next J
        End With
    Next I
    '--------------------------------
    'FIND UNIQUE COLLEGES AND RETURN NUMBER
        LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
        ws1.Range("A1:A" & LastRow).RemoveDuplicates Columns:=1, Header:=xlNo
        Worksheets("Totals").Range("C6") = WorksheetFunction.CountA(ws1.Range("A1:A" & LastRow))
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your expertise is very much appreciated! I will open and forward to admissions area for testing. Thank you so much.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    UDF version:

    Code:
    Public Function UniqueColleges(sColumn As String) As Long
        Dim ws                    As Worksheet
        Dim oDic                  As Object
        Dim LastRow               As Long
        Dim n                     As Long
        Dim vData
        Application.Volatile True
    
        Set oDic = CreateObject("Scripting.Dictionary")
    
        For Each ws In ThisWorkbook.Worksheets
            If Not ws Is Application.Caller.Worksheet Then
    
                LastRow = Application.Max(ws.Cells(Rows.Count, sColumn).End(xlUp).Row, 7)
    
                vData = ws.Range(ws.Cells(7, sColumn), ws.Cells(LastRow, sColumn)).Value2
                If IsArray(vData) Then
                    For n = 1 To UBound(vData, 1)
                        If Len(vData(n, 1)) <> 0 Then oDic(vData(n, 1)) = Empty
                    Next n
                Else
                    If Len(vData) <> 0 Then oDic(vData) = Empty
                End If
            End If
        Next ws
        UniqueColleges = oDic.Count
    
    End Function
    called using =UniqueColleges("D")
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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