asked on

Count the uniques in 3 sheets with comparing 2 colums.

hi,

Count the uniques in 3 sheets with comparing 2 colums.
attached is a workbook that has 3 sheets
Desktops
Dcs
Stock

the colum "DJ" & "DK" has Os details

i want seperate stats for each sheet and a combines stats for all in one. All in the same sheet as shown in the stats sheet.

Regards
Sharath
OS.xls

Ardhendu Sarangi

Hi Sharath,

See attached sheet. One way to do this would be by using the following formula - SumProduct -

=SUMPRODUCT((Desktop!$DJ$2:$DJ$1500=Stats!A3)*(Desktop!$DK$2:$DK$1500=Stats!B3))

- Ardhendu

P.s: i will add a macro solution if you need it.

OS.xls

bsharath

ASKER

Thanks Pari a macro would be helpful

Ardhendu Sarangi

Sharath,

Try this and see if this works?

- Ardhendu

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/25/2010 by Ardhendu Sarang
    
    Sheets("Stats").Select
    Range("C3").FormulaR1C1 = _
        "=SUMPRODUCT((Desktop!R2C114:R1500C114=Stats!RC[-2])*(Desktop!R2C115:R1500C115=Stats!RC[-1]))"
    Range("C3:C12").Select
    Selection.FillDown
    Range("C16").FormulaR1C1 = _
        "=SUMPRODUCT((Dcs!R2C114:R1500C114=Stats!RC[-2])*(Dcs!R2C115:R1500C115=Stats!RC[-1]))"
    Range("C16:C25").Select
    Selection.FillDown
    Range("C29").FormulaR1C1 = _
        "=SUMPRODUCT((Stock!R2C114:R1500C114=Stats!RC[-2])*(Stock!R2C115:R1500C115=Stats!RC[-1]))"
    Range("C29:C38").Select
    Selection.FillDown
    Range("C42").FormulaR1C1 = "=R[-39]C+R[-26]C+R[-13]C"
    Range("C42:C51").Select
    Selection.FillDown
End Sub

Open in new window

bsharath

ASKER

Hi Pari when i run it asks me to open the Files.
Can we have the file names within the code...

bsharath

ASKER

Hi Pari any views...

Ardhendu Sarangi

Hi Sharath,

I will post a response today. I am trying to change the code for you. Please wait,

Thanks,
Ardhendu

bsharath

ASKER

Ok Pari thank U

Ardhendu Sarangi

HI Sharath,
This is difficult to put together in a macro because of so many variables. I was able to do this in one way but I not think it will work for frequent updates. I think it would be easier to stick to formulas.

See attached sheet,

Thanks,
Ardhendu

Option Explicit
Sub totals()
    Dim i As Integer, j As Integer, cntX As Integer, n As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Set ws1 = Sheets("Desktop")
    Set ws2 = Sheets("DCS")
    Set ws3 = Sheets("Stock")
    Set ws4 = Sheets("Stats")

'Sheet - Desktops
    For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
        If ws4.Range("B" & n) <> "" Then
            cntX = 0
            For i = 3 To ws1.Cells(65536, "DJ").End(xlUp).Row
                If ws1.Range("DJ" & i) = ws4.Range("B" & n) _
                   And ws1.Range("DK" & i) = ws4.Range("C" & n) Then
                    cntX = cntX + 1
                End If
            Next
        End If
        ws4.Range("D" & n) = cntX
    Next

'Sheet DCS
    For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
        If ws4.Range("F" & n) <> "" Then
            cntX = 0
            For i = 3 To ws2.Cells(65536, "DJ").End(xlUp).Row
                If ws2.Range("DJ" & i) = ws4.Range("F" & n) _
                   And ws2.Range("DK" & i) = ws4.Range("G" & n) Then
                    cntX = cntX + 1
                End If
            Next
        End If
        ws4.Range("H" & n) = cntX
    Next
    
'Sheet Stock
    For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
        If ws4.Range("J" & n) <> "" Then
            cntX = 0
            For i = 3 To ws3.Cells(65536, "DJ").End(xlUp).Row
                If ws3.Range("DJ" & i) = ws4.Range("J" & n) _
                   And ws3.Range("DK" & i) = ws4.Range("K" & n) Then
                    cntX = cntX + 1
                End If
            Next
        End If
        ws4.Range("L" & n) = cntX
    Next

End Sub

Open in new window

OS.xls

bsharath

ASKER

Ok Pari...Can you help with a macro that will place the formulas.
As my excel crashed very offen and manually pacing the formulas is a pain. If when run the macro places the formulas in the same table way that you showed in the attachment i and happy

bsharath

ASKER

Hi Pari...Just a reminder on all the 3 posts...:-)

Ardhendu Sarangi

Hi Sharath,
Try the following code.. this uses some formulas for calculation but you won't see them in the end result. The end-result is a macro.

- Ardhendu

Option Explicit
Sub totals()
    Dim i As Integer, j As Integer, cntX As Integer, n As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Set ws1 = Sheets("Desktop")
    Set ws2 = Sheets("DCS")
    Set ws3 = Sheets("Stock")
    Set ws4 = Sheets("Stats")
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    'Sheet - Desktops
    ws4.Range("D6").FormulaR1C1 = _
    "=SUMPRODUCT((Desktop!R2C114:R1500C114=Stats!RC[-2])*(Desktop!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("D6:D" & ws4.Cells(65536, "B").End(xlUp).Row).Select
    Selection.FillDown

    'Sheet DCS
    ws4.Range("H6").FormulaR1C1 = _
    "=SUMPRODUCT((Dcs!R2C114:R1500C114=Stats!RC[-2])*(Dcs!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("H6:H" & ws4.Cells(65536, "B").End(xlUp).Row).Select
    Selection.FillDown


    'Sheet Stock
    ws4.Range("L6").FormulaR1C1 = _
    "=SUMPRODUCT((Stock!R2C114:R1500C114=Stats!RC[-2])*(Stock!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("L6:L" & ws4.Cells(65536, "J").End(xlUp).Row).Select
    Selection.FillDown

    'Final Stats
    ws4.Range("H20").FormulaR1C1 = "=R[-14]C[-4]+R[-14]C+R[-14]C[4]"
    ws4.Range("H20:H" & ws4.Cells(65536, "F").End(xlUp).Row).Select
    Selection.FillDown

    For i = 1 To ws4.Cells(65536, "F").End(xlUp).Row
        For j = 1 To ws4.Cells(i, 256).End(xlToLeft).Column
            'Cells(i, j).Select
            If Cells(i, j) <> "" Then
                Cells(i, j) = Cells(i, j)
            End If
        Next
    Next
    ws4.Range("A1").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Open in new window

bsharath

ASKER

Hi pari
i get this
Selected method or class failed
when debug goes here
ws4.Range("D6:D" & ws4.Cells(65536, "B").End(xlUp).row).Select

Ardhendu Sarangi

Sharath,

I missed one line. Try this.

- Ardhendu

Option Explicit
Sub totals()
    Dim i As Integer, j As Integer, cntX As Integer, n As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Set ws1 = Sheets("Desktop")
    Set ws2 = Sheets("DCS")
    Set ws3 = Sheets("Stock")
    Set ws4 = Sheets("Stats")
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ws4.Select
    'Sheet - Desktops
    ws4.Range("D6").FormulaR1C1 = _
    "=SUMPRODUCT((Desktop!R2C114:R1500C114=Stats!RC[-2])*(Desktop!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("D6:D" & ws4.Cells(65536, "B").End(xlUp).Row).Select
    Selection.FillDown

    'Sheet DCS
    ws4.Range("H6").FormulaR1C1 = _
    "=SUMPRODUCT((Dcs!R2C114:R1500C114=Stats!RC[-2])*(Dcs!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("H6:H" & ws4.Cells(65536, "B").End(xlUp).Row).Select
    Selection.FillDown


    'Sheet Stock
    ws4.Range("L6").FormulaR1C1 = _
    "=SUMPRODUCT((Stock!R2C114:R1500C114=Stats!RC[-2])*(Stock!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("L6:L" & ws4.Cells(65536, "J").End(xlUp).Row).Select
    Selection.FillDown

    'Final Stats
    ws4.Range("H20").FormulaR1C1 = "=R[-14]C[-4]+R[-14]C+R[-14]C[4]"
    ws4.Range("H20:H" & ws4.Cells(65536, "F").End(xlUp).Row).Select
    Selection.FillDown

    For i = 1 To ws4.Cells(65536, "F").End(xlUp).Row
        For j = 1 To ws4.Cells(i, 256).End(xlToLeft).Column
            'Cells(i, j).Select
            If Cells(i, j) <> "" Then
                Cells(i, j) = Cells(i, j)
            End If
        Next
    Next
    ws4.Range("A1").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

'        Range("D" & i) = Application.WorksheetFunction.SumProduct((ws1.Range("DJ2:DJ1500") = Range("B" & i)) * (ws1.Range("DK2:DK1500") = Range("C" & i)))
'        Range("H" & i) = Application.WorksheetFunction.SumProduct((ws2.Range("DJ2:DJ1500") = Range("F" & i)) * (ws2.Range("DK2:DK1500") = Range("G" & i)))
'        Range("L" & i) = Application.WorksheetFunction.SumProduct((ws3.Range("DJ2:DJ1500") = Range("J" & i)) * (ws3.Range("DK2:DK1500") = Range("K" & i)))
'
'Sheet - Desktops
'For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
'If ws4.Range("B" & n) <> "" Then
'cntX = 0
'For i = 3 To ws1.Cells(65536, "DJ").End(xlUp).Row
'If ws1.Range("DJ" & i) = ws4.Range("B" & n) _
 'And ws1.Range("DK" & i) = ws4.Range("C" & n) Then
'cntX = cntX + 1
'End If
'Next
'End If
'ws4.Range("D" & n) = cntX
'Next

'Sheet DCS
'For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
'If ws4.Range("F" & n) <> "" Then
'cntX = 0
'For i = 3 To ws2.Cells(65536, "DJ").End(xlUp).Row
'If ws2.Range("DJ" & i) = ws4.Range("F" & n) _
 'And ws2.Range("DK" & i) = ws4.Range("G" & n) Then
'cntX = cntX + 1
'End If
'Next
'End If
'ws4.Range("H" & n) = cntX
'Next

'Sheet Stock
'For n = 6 To ws4.Cells(65536, "B").End(xlUp).Row
'If ws4.Range("J" & n) <> "" Then
'cntX = 0
'For i = 3 To ws3.Cells(65536, "DJ").End(xlUp).Row
'If ws3.Range("DJ" & i) = ws4.Range("J" & n) _
 'And ws3.Range("DK" & i) = ws4.Range("K" & n) Then
'cntX = cntX + 1
'End If
'Next
'End If
'ws4.Range("L" & n) = cntX
'NextSub Stats()

' Macro3 Macro
' Macro recorded 1/25/2010 by Ardhendu Sarang

Open in new window

bsharath

ASKER

Pari now no data is got into the stats. it runs without errors but no output

Ardhendu Sarangi

Sharath,

Since the conditions are so complex, the code is using relative references to calculate the totals. Are you having the totals in the following format?

If you have a different format then can you let me know.

Thanks,
Ardhendu
OS.xls

bsharath

ASKER

thanks Pari for stressing so much for me...

1. Can i not get the Os names also populated automatically. ( if not possible then we can omit that)
2. When i copy the same headers and code to my excel it gets 0's for all
I have the same patter in my excel. Same colums same sheet names. But still get 0's

Ardhendu Sarangi

Sharath,

>> 1. Can i not get the Os names also populated automatically.
is your list of OS constant? If the list varies, then you would have to manually insert them into the grid. If they are constant then I can include them into the code.

>> 2. When i copy the same headers and code to my excel it gets 0's for all
Can you check if you have the data defined correctly on the Desktop, DCS and Stocks sheet. If there is no data in those sheets then you won't get the results in your stats sheet.

Please post the sheet that you are having a problem with?

Thanks,
Ardhendu

bsharath

ASKER

1. yes are constant. Like
Windows 2003 + service Pack1
Windows 2003 + service Pack2
Windows 2003 + service Pack3

2. I copies all the names from the actual in the same format and pasted in the stats sheet and now it works perfect

Can i have the "DJ","DK" colum populared automatically. Something like get both colums and then remove all duplciates in both colums so the actuals are left and then get the totals?

Ardhendu Sarangi

Hi Sharath,
I think I can try something to do that. Give me some time,

Thanks,
Ardhendu

bsharath

ASKER

Ok Pari thanks

bsharath

ASKER

Hi Pari any views...

Ardhendu Sarangi

Hi Sharath,
Try the following code please?

- Ardhendu

Sub Macro1()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet

    Application.DisplayAlerts = False
    'On Error Resume Next

    Set ws1 = Sheets("Desktop")
    Set ws2 = Sheets("DCS")
    Set ws3 = Sheets("Stock")
    Sheets("Stats").Delete
    Sheets.Add
    ActiveSheet.Name = "Stats"
    Set ws4 = Sheets("Stats")

    ws1.Range("DJ2:DK" & ws1.Cells(65536, "DJ").End(xlUp).Row).Copy
    ws4.Range("A1").PasteSpecial
    Application.CutCopyMode = False


    ws2.Range("DJ3:DK" & ws2.Cells(65536, "DJ").End(xlUp).Row).Copy
    ws4.Range("A" & ws4.Cells(65536, "A").End(xlUp).Row + 1).PasteSpecial
    Application.CutCopyMode = False

    ws3.Range("DJ3:DK" & ws3.Cells(65536, "DJ").End(xlUp).Row).Copy
    ws4.Range("A" & ws4.Cells(65536, "A").End(xlUp).Row + 1).PasteSpecial
    Application.CutCopyMode = False
    ws4.Range("A1").Select

    ws4.Range("A1:B" & ws4.Cells(65536, "A").End(xlUp).Row + 1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
                                                                                                                   "D:E"), Unique:=True
    ws4.Columns("A:C").Delete Shift:=xlToLeft
    Cells.EntireColumn.AutoFit
    ws4.Range("A1:F" & ws4.Cells(65536, "A").End(xlUp).Row + 1).Select
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    ws4.Range("C1") = "Desktop"
    ws4.Range("D1") = "Dcs"
    ws4.Range("E1") = "Stock"
    ws4.Range("F1") = "Totals"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    ws4.Cells.Interior.ColorIndex = 2
    With ws4.Range("A1:F1")
        .Font.Bold = True
        .Interior.ColorIndex = 15
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ws4.Range("C" & ws4.Cells(65536, "A").End(xlUp).Row + 1) = "=SUM(R[-10]C:R[-1]C)"
    ws4.Range("C" & ws4.Cells(65536, "A").End(xlUp).Row + 1 & ":F" & ws4.Cells(65536, "A").End(xlUp).Row + 1).FillRight
    ws4.Range("F2") = "=SUM(RC[-3]:RC[-1])"
    Range("F2:F" & ws4.Cells(65536, "A").End(xlUp).Row).FillDown
    
'FORMULAS - SUMPRODUCT
    ws4.Range("C2") = "=SUMPRODUCT((Desktop!R2C114:R1500C114=Stats!RC[-2])*(Desktop!R2C115:R1500C115=Stats!RC[-1]))"
    ws4.Range("C2:C" & ws4.Cells(65536, "A").End(xlUp).Row).FillDown
    ws4.Range("D2") = "=SUMPRODUCT((Dcs!R2C114:R1500C114=Stats!RC1)*(Dcs!R2C115:R1500C115=Stats!RC2))"
    ws4.Range("D2:D" & ws4.Cells(65536, "A").End(xlUp).Row).FillDown
    ws4.Range("E2") = "=SUMPRODUCT((Stock!R2C114:R1500C114=Stats!RC1)*(Stock!R2C115:R1500C115=Stats!RC2))"
    ws4.Range("E2:E" & ws4.Cells(65536, "A").End(xlUp).Row).FillDown

For i = 2 To ws4.Cells(65536, "A").End(xlUp).Row + 1
    ws4.Range("C" & i) = ws4.Range("C" & i).Value
    ws4.Range("D" & i) = ws4.Range("D" & i).Value
    ws4.Range("E" & i) = ws4.Range("E" & i).Value
    ws4.Range("F" & i) = ws4.Range("F" & i).Value
    
Next

End Sub

Open in new window

OS.xls

bsharath

ASKER

Pari attached the workbook
I have 3 issues
One total does not populate
2 the 3rd row has the totals i guess
And another Windows 2003 does not get the count right
I have placed more data into the sheet and renamed the sheet names also accordinly changed in the code.

OS-1-.xls

ASKER CERTIFIED SOLUTION

Ardhendu Sarangi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ardhendu Sarangi

Hi Sharath,
Did that work for you??

bsharath

ASKER

Thanks a lot Pari its perfect.. :-)
Its a very useful one for me...

Any help on the others