Search for text and extract number

thealley

New Member
Joined
Mar 1, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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 :)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
HI
As a start
Try this code covers A1:E100

VBA Code:
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

New Member
Joined
Mar 1, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top