Results 1 to 4 of 4

Thread: How to show unique values of Column B from Multiple sheets using a function

  1. #1

    How to show unique values of Column B from Multiple sheets using a function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone,

    Can I know how to show unique values of column B from Multiple sheets using a function. Please suggest!

    Thanks!
    Kiran

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    A quick google query shows this result that might assist you:

    http://answers.microsoft.com/en-us/o...9-114d4e563a6d


  3. #3

    How to show unique values of Column B from Multiple sheets using a function

    Thank you!

    But I am not getting the correct result. Could you please help me on the logic.

    I need to ignore first two sheet and from the third sheet I need to show the distinct values based on Column B (Range is B3 to B1000) for the remaining sheets.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try altering it to:

    Code:
    Sub UniqueValues()
    Dim newWS As Worksheet, r As Long, N As Long, i As Integer
    Application.ScreenUpdating = False
    
    For Each ws In Sheets
        Application.DisplayAlerts = False
        If ws.Name = "UNIQUE_DATA" Then ws.Delete
        Application.DisplayAlerts = True
    Next
    
    Set newWS = Sheets.Add(after:=Sheets(Sheets.Count))
    newWS.Name = "UNIQUE_DATA"
    N = 1
    For i = 3 To Sheets.Count - 1
        r = Sheets(i).Cells(Rows.Count, "B").End(xlUp).Row
        Sheets(i).Range("B1:B" & r).Copy
        Cells(N, 2).PasteSpecial xlValues
        N = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Next
    r = Cells(Rows.Count, "B").End(xlUp).Row
    Debug.Print r
    Range("B1:B" & r).AdvancedFilter _
    Action:=xlFilterInPlace, Unique:=True
    Range("B1:B" & r).Copy
    Range("A1").PasteSpecial xlValues
    
    Application.CutCopyMode = False
    Range("B1:B" & r).AdvancedFilter _
    Action:=xlFilterInPlace, Unique:=False
    Columns(2).Delete
    r = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & r).Sort key1:=Range("A1"), Header:=xlNo
    Application.ScreenUpdating = True
    End Sub


Posting Permissions

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