Trying to solve count

[parttime_guy]

Hi All,

Is the result required in sample attached possible in excel via a formula.
Any assistance would highly be appreciated.

Thanks and Best regards

[lecxe]

Hi

For ex., in E2:

=COUNT(1/FREQUENCY(IF($A$2:$A$100=D2,MATCH($B$2:$B$100,IF($A$2:$A$100=D2,$B$2:$B$100 ),0)),ROW($A$2:$A$100)-ROW($A$2)+1))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

[parttime_guy]

Hi lecxe,

Thanks for the array formula (tried it - it work perfectly), is it possible to do the same with a count/countifs or a sum/sumifs

Best Regards

[lecxe]

Assuming, like in the example, that if there's a value in A there's also a value in B, in E2, non array entered:

=SUMPRODUCT(($A$2:$A$100=D2)/(COUNTIFS($A$2:$A$100,$A$2:$A$100&"",$B$2:$B$100,$B$2:$B$100&"")))

Copy down

[david000]

Lecxe - What is the &"" doing exactly?

[lecxe]

Hi David

It's for the case of empty cells. If you don't use it you'll get a count of 0 which will mess up the formula.
Concatenating an empty cell with an empty string will result in counting empty cells + cells with empty strings, avoiding the zero result in those cases.

Simple test

Write some text values in A1:A10, but leave some cells empty or with a null string.
Clear cell B1 (empty, no value, no formula)

Try:

=COUNTIF(A1:A10,B1)
=COUNTIF(A1:A10,B1&"")

In the first case you get 0.
In the second case you get the count of empty cells + cells with empty strings.

[david000]

Interesting...thanks I'll put that in my toolbox.

I got a similar answer wrapping it in a T() function too, but it's not consistent.

=COUNTIF(A1:A10,T(B1))

Thanks again!

[Bob Phillips]

You should also note that if you have a row where column A matches your test cell, but column B is blank, that will be counted as a separate instance. You can avoid that with

=SUMPRODUCT(($A$2:$A$100=D2)*($B$2:$B$100<>"")/(COUNTIFS($A$2:$A$100,$A$2:$A$100&"",$B$2:$B$100,$B$2:$B$100&"")))

[lecxe]

David, I'm glad it was helpful.

[lecxe]

Thanks xld.

You are right, of course.

I just wanted to keep the formula simple and that's why I wrote:

Assuming, like in the example, that if there's a value in A there's also a value in B, in E2, non array entered:

The truth is that I do know what's the interpretation of that case, a value in A without a value in B, should it be counted or not?

[Bob Phillips]

No, nor do I, it is a business call as to whether it is counted or not. I was just adding for David's information.

[lecxe]

Anyway you're probably right. If I had to guess I'd also not count them.

[parttime_guy]

Dear xld,

As always ur a "star" .

Thanks lecxe and all - for ur help.

Best Regards

[lecxe]

I'm glad it helped. Thanks for the feedback.

[parttime_guy]

Hi Everybody,

I have hit another road-block

Can you please help me (same query - different results required) - attached sample worksheet.

Regards

[david000]

Well, this is a post that won't go away... I'm very curious to see some other solutions to this problem, but here's my first thought.

Sub looksgood1()
Dim FoundCell
Dim rCell As Range
Dim e As String
Dim strFirstAddress As String
Dim lra As Integer
Dim lrd As Integer
Dim cel As Range
Dim lrb As Integer

lra = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lrd = Sheet1.Cells(Rows.Count, "d").End(xlUp).Row

Range("B1:B" & lrb).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

For Each cel In Range("D2:D" & lrd)
With Range("A1:A" & lra).SpecialCells(xlCellTypeVisible)
        Set rCell = .Find(cel.Value, After:=Range("a1"), lookat:=xlWhole, LookIn:=xlValues)
            strFirstAddress = rCell.Address
            Set FoundCell = rCell
            Do
                e = e & Cells(rCell.Row, rCell.Column).Offset(, 1) & ", "

                Set rCell = .FindNext(rCell)
                Set FoundCell = Union(FoundCell, rCell)

            Loop While Not rCell Is Nothing And rCell.Address <> strFirstAddress
  cel.Offset(, 1) = Left(e, Len(e) - 2) 'delete last 2 chars
End With
e = ""
Next cel
ActiveSheet.ShowAllData
End Sub

[parttime_guy]

Hi David,

Thanks for your vba code (will try to use your code on Workbook_Open Event).

Are the same results possible via a excel formula???

Regards

[lecxe]

Are the same results possible via a excel formula???

It's not possible a general solution using just formulas. You can have a solution with just formulas if you make some restriction, for ex. no more than 10 items per group. In that case you could have a formula solution, but it would require several auxiliary columns or it would be 1km long.


A compromise solution would be to have just a small snippet in vba to concatenate arrays and then to use a formula solution.

For the table you posted, for ex.:

Function MyConcat(vArr As Variant) As String
Dim v As Variant
Dim s As String

For Each v In vArr
    If v <> "" Then s = s & ", " & v
Next v
MyConcat = Mid(s, 3)
End Function

Now you can use a formula. For ex., in E2:

=MyConcat(IF($A$2:$A$100=D2,IF(MATCH($B$2:$B$100,IF($A$2:$A$100=D2,$B$2:$B$ 100),0)=ROW($B$2:$B$100)-ROW($B$2)+1,$B$2:$B$100,""),""))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

[lecxe]

Also a vba solution.

Assuming like in the spreadsheet you posted a contiguous table in A:B, starting in row 2, values in both columns. Try:

Sub DisplayGroups()
Dim rSrc As Range, rDest As Range, rC As Range
Dim vKey As Variant

Set rSrc = Range("A2", Range("A2").End(xlDown))
Set rDest = Range("D2")

With CreateObject("Scripting.Dictionary")
    For Each rC In rSrc
        If Not .exists(rC.Value) Then .Add rC.Value, CreateObject("Scripting.Dictionary")
        If Not .Item(rC.Value).exists(rC.Offset(, 1).Value) Then .Item(rC.Value).Add rC.Offset(, 1).Value, ""
    Next rC
    For Each vKey In .keys
        .Item(vKey) = Join(.Item(vKey).keys, ", ")
    Next vKey
    rDest.Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
End With
End Sub

[parttime_guy]

Hi Lecxe

Wow your code rocks and is superfast.

Thanks everyone once again for all your help.

Best regards