Results 1 to 1 of 1

Thread: How I need to get the FAIL COUNT Based on two Columns in all the sheets

  1. #1

    How I need to get the FAIL COUNT Based on two Columns in all the sheets



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

    Hi,

    I have a requirement to show the Unique value of column B in all the sheets for that I have a written a function

    After I got the Unique Values I need to compare those Unique Value with Other Columns in each Sheet let say Column F Which has PASS OR FAIL and we should get the Count for those Unique Values Which has "FAIL" in Column F.

    I have written the code like this

    1) I Wrote a Code the generate the Unique Values which are generated in Column A in UNIQUE SHEET

    Code:
    Sub UniqueValues_ShtNames()
    
    'JAN 08, 2015
    
    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("B3:B" & r).Copy
    Cells(N, 1).PasteSpecial xlValues
    Cells(N, 2).Resize(r) = Sheets(i).Name
    N = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next
    ActiveSheet.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    r = Cells(Rows.Count, "A").End(xlUp).Row
    For i = r To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) Then
    Cells(i - 1, 2) = Cells(i - 1, 2) & ", " & Cells(i, 2)
    Rows(i).ClearContents
    End If
    Next
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("A:B").EntireColumn.AutoFit
    
    Application.ScreenUpdating = True
    
    End Sub

    2) Using the Column A of UNIQUE SHEET I am trying to search Column F in each Sheet which has FAIL and I need the COUNT of each Unique Value.

    The Function I have written is comparing with sheet and not with Column B in each sheet.

    Code:
    Function FailCount(WhatSheet As String, WhatColumn As Range)
      '=FailCount(A1,F:F) 
      If Not Evaluate("=ISREF('" & Trim$(WhatSheet) & "'!A1)") Then
        FailCount = "Invaild sheet name"
       Exit Function
      End If
      FailCount = Application.Evaluate("=COUNTIF('" & Trim$(WhatSheet) & "'!" & WhatColumn.Address & ",""FAIL"")")
    End Function
    I have attached the file - I Need the Count in Column C in Unique Sheet which will be at the Last.

    Any help is appreciated!

    Thanks!
    Kiran
    Attached Files Attached Files
    Last edited by NBVC; 2015-01-09 at 12:40 PM.

Posting Permissions

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