Counting unique names with three column condition

Hey!

I'm new to this but I have a problem I would really appreciate getting help with.

I have a worksheet with over 1000 rows, with a column containing a bunch of names of different projects. Each project can appear several times, since they have changed status over the years. There is a date column, and a status column varying from "Idea", "Project", "Company" and "Archive". So project "A" for example can appear four times if they have gone through all these stages. And to make it complicated there is an Archive ID column with numbers ranging from 0-3. 0 meaning the project is closed, 1 meaning the project has continued externally, 2 meaning the project is on hold, 3 meaning the project is active. So if a project has the status "Archive" a given date you need to chech the "Arhive ID" to get more information on the status. If the project has one of the other statuses the "Archive ID" number means nothing.

I hope this is not to confusing. My problem here is that I want to know how many ideas have been ended, i.e. I want to count all the projects who only occur two times, first with the status "Idea" and then "Archive" with ID 0. I would actually like to count several combinations, for example idea to project, and project to archive 1,2,3,0. It would be easier to count how many have gone from idea to project since that eliminates the search through the "Archive ID" column, but I guess the solution would be similar.

So does anybody think they can help? Is it possible to do this without writing a code?

I really appreciate the help!

/M

The way I always used to do this was to use an Advanced Filter to extract a list of unique entries and then use SUMIF or SUMPRODUCT to find whatever combinations I was looking for. This can be quite slow depending on the number of formulae, the complexity of the formulae and the volume of data.

However, I suspect that the recommendation is actually to use a Pivot Table. This is quick and flexible ... and more important, built in and hence as efficient as it's going to be.

Regards

Maybe I could attach an example of the worksheet to better understand your solution?

That would be good

Regards

Code to extract unique titles using Advanced Filter and then add formulae to create analysis.

Option Explicit

Sub TitleAnalysis()
Dim lLR1 As Long
Dim lLR2 As Long

Application.ScreenUpdating = False
' Determine last row on Blad1
With Sheets("Blad1")
    lLR1 = .Range("A" & .Rows.Count).End(xlUp).Row
End With
' Set up Sheet Blad2
With Sheets("Blad2")
    ' Remove old data
    .Cells.Clear
    ' Advanced Filter unique Title entries
    Sheets("Blad1").Columns("A:A").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=.Range("A1"), _
        Unique:=True
    ' Determine last row on Blad2
    lLR2 = .Range("A" & .Rows.Count).End(xlUp).Row
    ' Create headings
    .Range("A1:I1") = Split("Title|Entries|Idea|Archive|0|1|2|3|4", "|")
    ' Count entries for each Title
    .Range("B2:B" & lLR2).FormulaR1C1 = "=COUNTIF(Blad1!C[-1],Blad2!RC1)"
    ' Count ideas for each Title
    .Range("C2:C" & lLR2).FormulaR1C1 = _
        "=SUMPRODUCT((Blad1!R2C1:R" & lLR1 & _
        "C1=Blad2!RC1)*(Blad1!R2C3:R" & lLR1 & _
        "C3=Blad2!R1C))"
    ' Count archives for each Title
    .Range("D2:D" & lLR2).FormulaR1C1 = _
        "=SUMPRODUCT((Blad1!R2C1:R" & lLR1 & _
        "C1=Blad2!RC1)*(Blad1!R2C3:R" & lLR1 & _
        "C3=Blad2!R1C))"
    ' Count archive breakdown for each Title
    .Range("E2:I" & lLR2).FormulaR1C1 = _
        "=SUMPRODUCT((Blad1!R2C1:R" & lLR1 & _
        "C1=Blad2!RC1)*(Blad1!R2C3:R" & lLR1 & _
        "C3=""Archive"")*(Blad1!R2C4:R" & lLR1 & _
        "C4=--R1C))"
    ' Format headings bold
    With .Range("A1:I1")
        .Font.Bold = True
    End With
    ' Format all cells Centre
    With .Range("A1:I" & lLR2)
        .HorizontalAlignment = xlCenter
        ' Copy/PasteSpecial - convert formulae to values
        .Copy
        .PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
End With
Sheets("Blad2").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

If you want to keep the formulae, comment out the Copy/PasteSpecial lines.

See attached example.


Regards

This should match your actual layout:

Option Explicit

Sub TitleAnalysisLive()
Dim lLR1 As Long
Dim lLR2 As Long

Dim ws1 As Worksheet: Set ws1 = Sheets("Alla Projekt med Case")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")

Application.ScreenUpdating = False
' Determine last row on Alla Projekt med Case
With ws1
    lLR1 = .Range("B" & .Rows.Count).End(xlUp).Row
End With
' Set up Sheet ws2
With ws2
    ' Remove old data
    .Cells.Clear
    ' Advanced Filter unique Title entries
    ws1.Columns("B:B").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=.Range("A1"), _
        Unique:=True
    ' Determine last row on ws2
    lLR2 = .Range("A" & .Rows.Count).End(xlUp).Row
    ' Create headings
    .Range("A1:K1") = Split("Title|Entries|Idésökning|Arkiv|Projekt|LUAB|0|1|2|3|4", "|")
    ' Count entries for each Title
    .Range("B2:B" & lLR2).FormulaR1C1 = _
        "=COUNTIF('" & ws1.Name & "'!C,RC1)"
    ' Count ideas/archives/projects/company for each Title
    .Range("C2:F" & lLR2).FormulaR1C1 = _
        "=SUMPRODUCT(('" & ws1.Name & "'!R2C2:R" & lLR1 & _
        "C2=RC1)*('" & ws1.Name & "'!R2C5:R" & lLR1 & _
        "C5=R1C))"
    ' Count archive breakdown for each Title
    .Range("G2:K" & lLR2).FormulaR1C1 = _
        "=IF(RC4=0,"""",SUMPRODUCT(('" & ws1.Name & "'!R2C2:R" & lLR1 & _
        "C2=RC1)*('" & ws1.Name & "'!R2C5:R" & lLR1 & _
        "C5=""Arkiv"")*('" & ws1.Name & "'!R2C6:R" & lLR1 & _
        "C6=--R1C)))"
    ' Format headings bold
    With .Range("A1:K1")
        .Font.Bold = True
    End With
    ' Format all cells Centre
    With .Range("A1:K" & lLR2)
        .HorizontalAlignment = xlCenter
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ' Copy/PasteSpecial - convert formulae to values
        '.Copy
        '.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
End With
ws2.Select
Range("A1").Select

With ws2.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .SetRange Range("A2:K" & lLR2)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.ScreenUpdating = True
End Sub

Regards

You're welcome. Thanks for the rep.

how would you go about calculating how many days each entry is in respective status?

I think you'd need to sort the records (on the data sheet) into status within title. However, you'd probably need to add a helper column with a status sequence so that you can sort them correctly.

Once they can be sorted into the correct sequence, you can then subtract one date from the next.

Regards