Posts by CurtFunk

  • Can't make dependant combo boxes to work

    Re: Can't make dependant combo boxes to work


    Hi,

    You need to set the "RowSource" property for each combo box. So set the property to the range of data you want listed in the combo box. For Categora, that would be "A2:A7".

    Hope that helps,
    CF

  • Using multiple functions in SUM functions

    Re: Using multiple functions in SUM functions


    Hi Newman11

    I think the problem was the number of brackets. I modified the formula to read as follows:

    =((SUM(100-A1)/100)*A3)/A2

    It gave a result of 1.823359.

    Now technically, you don't need the SUM function in this example. The SUM function is use to add numbers in a range. So your formula could also have been:

    =(((100-A1)/100)*A3)/A2

    Remember that the number of open "(" should equal the number of closed ")".

    Hope that helps,

    CF

  • Lookup Codes in Table

    Re: How do display an item automatically when user keys in a code which matches the l


    You would use the VLOOKUP function. In your sample spreadsheet, place the following formula into the cell you indicated:

    =VLOOKUP(J8,E9:F15,2)

    That should do it ...
    CF

  • compare and count similar cells

    Re: compare and count similar cells


    I didn't write any code but here is a suggestion. Take a look at the "like" operator. It allows you to look for partial matches between two strings. You can also use wildcards.

    My thought would be to take the left of a string up to the first blank character and then use the "like" operator to see if that group of characters exists within the other string.

    Proposed modification to your code ...

    Code
    comp = Left((Cells(w, 1)), InStr(1, Cells(w, 1), " ")) & "*"
    Comp1 = Cells(w + 1)
    If Not (Comp1 [COLOR=red]Like[/COLOR] comp) Then
        Sheets("Consolidation").Cells(y, 1) = Left((Cells(w, 1)), InStr(1, Cells(w, 1), " "))
        Sheets("Consolidation").Cells(y, 2) = subtot
        subtot = Cells(w + 1, 3)
        y = y + 1
      Else
        subtot = subtot + Cells(w + 1, 3)
     End I



    This will also solve your second problem since the value you place in your cell will be the full name (not just the first 6 characters).

    Note this gets trickier if there is no space after the name.

    Hope that helps,
    CF

  • Sum of nonstrikethrough numbers

    Re: Sum of nonstrikethrough numbers


    Aswani,

    Upon reflection my original code would not work. I have attached a sample spreadsheet with a quick (and dirty) macro that will illustrate what I mean. Essentially, convert the value to a string , then format the strikethrough font and then right alighn it since text defaults to left align.

    The code in the macro looks like this:



    Run the macro1 in the spreadsheet and watch cell E14. You will see the number appear, with strike through, but the SUM does not change (your original quandry).

    Hope that helps,
    CF

  • Sum of nonstrikethrough numbers

    Re: Sum of nonstrikethrough numbers


    One option might be to have your macro, when it creates the value in K18, convert the number to a string value. A SUM command somewhere else in the spreadsheet will ignore the string characters in its summation. Since it appears you are already formatting the cell with strikethrough format, you can do the conversion there - make sure you recreate any decimal and comma positioning using the "Format" function. An example:

    Code
    myStringVlu = Format(Str(myNumVlu), "#,00.00")



    CF

  • Lookup from text string

    Re: Lookup from text string


    Stamron

    I think the easiest way is to use a user-defined-function (UDF). First I created a second sheet in your sample and put a table containing the keywords that you want to find (this way you can add more keywords). The range of keywords has be assigned a range name of "Keywords". The table that contains both the keywords and results has a range name assigned of "Categories". Next I created a UDF called "Find_Category" to which you pass the log text. The function then searches for each of the keywords located in the keywords range and if successful it returns the found keyword in the table. You burry that in a VLOOKUP and poof you have the "result". Since the search tables has to be sorted for the lookups to work, the tricky part was finding the first keyword match - you can read the code for that solution.

    I have attached the resulting spreadsheet (note that it has an XLSM - Macro Enabled spreadsheet - format).
    Hope that helps - CF

    PS. If you have VBA experience then the following is the code that is in the function:

  • Extract three sets of numbers from one cell

    Re: Extract three sets of numbers from one cell


    Hi Dan

    Given the complexity of your data extract requirements I think that using an excel formula will exceedingly difficult (particularly if you want to avoid going blind reviewing the syntax). I think this is a good example for a user-defined-function (UDF). I am not sure your level of experience with UDF or visual basic. I developed a UDF that will allow you to pass all the different variations of the values you are looking for (i.e. "BLACK", "BLACK:", "BLACK " or "BLACK -". The function will return the associated value. You can use this function for the other lookups (like TOTAL, COLOUR, B/W etc.).

    To make life easier I created the UDF and included it in the attached sample file (that was your original post). It illustrates how to use the function.

    NOTE: The function assumes that there is always at least a " " before the value we are extracting. Also, the search is case-sensitive. Also, the excel sheet now has an XLSM extension (macro enabled spreadsheet).

    I hope this helps ... CF

    PS In the event that you have experience with UDF, I have included the code.

    Those reading this thread - don't laugh at the code - but here is the code for the UDF

  • Testing 123

    Re: Testing 123


  • Testing 123

    [VBA]
    Function Extract_value(search_string As String, search_vlu As Variant, Optional search_vlu2 As Variant = "", _
    Optional search_vlu3 As Variant = "", Optional search_vlu4 As Variant = "", Optional search_vlu5 As Variant = "", _
    Optional search_vlu6 As Variant = "") As Variant

    Dim Text_Pos As Integer
    Dim First_blank, Second_Blank As Variant
    Dim values(6, 1) As Variant

    values(0, 1) = search_vlu
    values(1, 1) = search_vlu2
    values(2, 1) = search_vlu3
    values(3, 1) = search_vlu4
    values(4, 1) = search_vlu5
    values(5, 1) = search_vlu6

    ' Make sure that the search values are sorted largest to smallest
    SortColumn1 = 0
    For i = LBound(values, 1) To UBound(values, 1) - 1
    For j = LBound(values, 1) To UBound(values, 1) - 1
    Condition1 = values(j, SortColumn1) > values(j + 1, SortColumn1)
    If Condition1 Then
    For y = LBound(values, 2) To UBound(values, 2)
    t = values(j, y)
    values(j, y) = values(j + 1, y)
    values(j + 1, y) = t
    Next y
    End If
    Next
    Next




    ' First look for the primary search value

    For i = LBound(values, 1) To UBound(values, 1)
    Text_Pos = InStr(search_string, values(i, 1))
    srch_str = values(i, 1)
    If Text_Pos > 1 Then Exit For
    Next


    If Text_Pos > 1 Then
    '
    ' We assume that the number will be following the first "blank" character
    ' and that number will end at the next blank (or end of string)
    '
    First_blank = InStr(Text_Pos + Len(srch_str) - 1, search_string, " ")
    Second_Blank = InStr(First_blank + Len(srch_str), search_string, " ") - 1
    If Second_Blank = -1 Then Second_Blank = Len(search_string) + 1
    Extract_value = Mid(search_string, First_blank, Second_Blank - First_blank)
    Else
    Extract_value = "None"

    End If

    End Function


    [/VBA]

  • Return Name based on Rank with multiple criteria

    Re: Return Name based on Rank with multiple criteria


    I am thinking that a pivot table may be your answer. Alternatively you can use data filtering on your 2k items to narrow the list. Depending on the version of excel (i.e. 2007) you could use "Tables" that combined with filter can allow you to include totals.

    Hope that helps.

  • Extract three sets of numbers from one cell

    Re: Extract three sets of numbers from one cell


    I think I understand what you are trying to do. From your example, I put the "B = 6742 F = 3027" into cell A8. I then put the following formulas into cell B8 and C8 respectively.

    B8: =MID(A8,FIND("=",A8,1)+2,FIND(" ",A8,FIND("=",A8,1)+2)-FIND("=",A8,1)-2)
    C8: =MID(A8,FIND("=",A8,FIND("=",A8,1)+1)+1,LEN(A8)-FIND("=",A8,FIND("=",A8,1)+1)+1)

    Simply copy the formulas for the 300+ rows that you have.

    Essentially the first formula extracts the text between the first "=" and the space after the number. The second formula extracts the text after the second "=". Note that this formula assumes that you have a "= ' format in your source text.

    I have attached as sample that also shows how to convert it to a number (that you can do math on).

    Hope that helps.

  • Lookup using mutilple criteria

    Re: Lookup using mutilple criteria


    What I have done in the past is set up an index column within the data (you can always hide the column). The cells within the index columns would be concatenation of the cells that you want to search by. In your example - assume that the vegitable is in column A and the colour in column B. You would insert a column in front of Column A. The new formula would be =A1&B1.

    Then in your VLOOKUP function you would use the concatenated values of what you are trying to find.

    I have attached an example. Cell I4 contains the VLOOKUP example.

    Hope that helps.