1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Cell Macro/Filter

Discussion in 'Business Applications' started by raamindasu, Jul 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. raamindasu

    raamindasu Thread Starter

    Joined:
    Aug 24, 2007
    Messages:
    12
    (I am unsure if the first time i posted this worked or not, but here we go again.)

    I need to remove some data from cells, preserve numerical values, and add them together.

    The record format is this EntityA(#records). In most cells, There is not only an Entity A, but B-H as well. I am trying to strip out the entity name information, retain the numerical value in the parentheses, and then add them together cell by cell, row by row.

    I have attached a sample of the sheet.

    Im not looking for anything too fancy, I've just got about 1000 rows of these and need a total count of (#records) that have been received. So in the sample, it doesn't matter that A3 = 32 total, I'm just in need of a folmula, filter, or macro that will tell me that A3-A9= 41 total. (If the "n/a" or "~" values are problematic they can just be blank.)

    Can anyone help with this?
     

    Attached Files:

  2. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Hi!
    welcome to TSG!
    Let see if I understand you right
    You have about 1000 rows, in each one in column A you have some text (the name) and in brackets you have some numbers, and you wanna to sum all or just some of the number #value
    Am I right?
    In one cell can be more than one combination of text +#value?
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,833
    AJ, I am nearly finished with an answer to this one.
     
  4. raamindasu

    raamindasu Thread Starter

    Joined:
    Aug 24, 2007
    Messages:
    12
    yes,

    basically i want to sum all values in parentheses in the column; and there can be multiple, comma-separated "Entity(#)"s per cell
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,833
    This works, just repeat for the other columns

    Private Sub CommandButton1_Click()
    Dim lastrow As Long, count As Integer, data As String, total As Integer, count2 As Integer, start As Integer, finish As Integer
    lastrow = Cells(Rows.count, "A").End(xlUp).Row
    For count = 2 To lastrow
    start = 0
    finish = 0

    data = Cells(count, 1)
    For count2 = 1 To Len(data)
    If Mid(data, count2, 1) = "(" Then start = count2
    If Mid(data, count2, 1) = ")" Then finish = count2
    If start > 0 And finish > 0 Then
    total = total + Val(Mid(data, start + 1, finish - start))
    'MsgBox total & " " & start & " " & finish & " " & Mid(data, start + 1, finish - start)
    start = 0
    finish = 0
    End If
    Next count2
    Next
    Cells(lastrow + 2, 1) = total
    End Sub

    you need to reset A11 back to 0
     

    Attached Files:

  6. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    OBP I changed a little you code and made a UDF, it looks like this:
    Code:
    Function SumOrCountValues(rgRange As Range)
        Dim rgCel As Range
        sumValue = 0
        For Each rgCel In rgRange
            txt = rgCel.Value
            r = Len(txt) - Len(WorksheetFunction.Substitute(txt, "(", ""))
            For i = 1 To r
                m1 = WorksheetFunction.Find("(", txt) + 1
                m2 = WorksheetFunction.Find(")", txt) - m1
                sumValue = sumValue + CInt(Mid(txt, m1, m2))
                txt = WorksheetFunction.Substitute(txt, "(", "", 1)
                txt = WorksheetFunction.Substitute(txt, ")", "", 1)
            Next
        Next
        SumOrCountValues = sumValue
    End Function
    
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,833
    Does that mean that the user has to Highlight the range for it to operate on?
     
  8. raamindasu

    raamindasu Thread Starter

    Joined:
    Aug 24, 2007
    Messages:
    12
    my apologies for the long delay. i am unaware as to what the two above fields implementation would be. i tried monkeying around in vbe and so forth but to no avail. (obviously thats a lack of experience on my part).

    for me the problem is fixed, though not in so gentle a fashion as what yours did. i went in there and used the 'text to columns' tool like a machete, separated everything up, sorted, replaced all "("s with ", ("s and then was able to cut the number loose from the string. then excel was able to sum perfectly fine after.

    i wish there was a count format for numbers like $tring (#val). i tried to upt together a custom one but it wasnt having it.

    thanks again
     
  9. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    OBP it work almost like a simple sum function, you select the range you need and you'll have the sum of all number contained in parentheses!


    Did you tried the function I posted?
    Copy it into a standard module of the workbook, or in Personal.xls workbook, press the function sigh on the right of the formula edit field, select the Custom Function, category and find there the name of the function! In the dialog box that will appear select the range you need to be summed value and you 'll get the sum you wanted!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/730402

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice