Search for text and extract number

[thealley]

Hi,

I have a range of cells, say, A1 to P100. I would like to search for names plus adjacent number of hours in this range of cells (example cell A1 is Joe 5hrs, A2 is Diane 6hrs, C5 is Joe 3hrs, D8 is Chris 2hrs.

So then, I would like, example columns S1 to S3 to be: 8, 6, 2, representing the total number of hours of each person.

If I add another entry in E5 as Diane 3hrs, column S updates to 8, 9, 2. And so on.

I looked into a mixture of COUNTIF, ISNUMBER and SEARCH, but it was getting too complex for me.

Any help would be appreciated.

Thanks

 

[mohadin]

HI
As a start
Try this code covers A1:E100

Sub test()
    Dim a As Variant
    Dim e, x
    a = Range("A1:E100")
    With CreateObject("scripting.dictionary")
        For Each e In a
            If e <> "" Then
                x = Split(e)
                If Not .exists(x(0)) Then
                    .Add x(0), Split(x(1), "hrs")(0) * 1
                Else
                    .Item(x(0)) = .Item(x(0)) + Split(x(1), "hrs")(0) * 1
                End If
            End If
        Next
        Range("S1").Resize(.Count) = Application.Transpose(.items)
         Range("T1").Resize(.Count) = Application.Transpose(.keys)
    End With
End Sub

 

[thealley]

Hi mohadin,

Thanks for your reply. I'll try this out at home to get a hands on.

Actually I want this for work, and we're not allowed to use VBA codes, so I was looking more into some formulas (if it can be done that way). Apologies for not advising beforehand.

Thanks.