***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[ghatfanazzam]

Re: June/July 2008 Challenge of the Month

I have a formula for this challenge , I don't know if any body else posted it before.
in cell B2 put this formula
=VLOOKUP(INDEX($D$2:$D$10,MATCH(TRUE,COUNTIF(A2,"*"&$D$2:$D$10&"*")>0,0)),$D$2:$E$10,2,FALSE) then confirm it with Ctrl + Shift + Enter<!-- / message -->

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

Daniel,

Instead of:
=OFFSET(E$1,SUM(ISNUMBER(FIND(KEY,A2))*ROW(KEY))-1)

why not:
=OFFSET(E$1,SUM(ISNUMBER(SEARCH(Key,A2))*ROW(Key))-1,)

SEARCH will find any case.

I like the dynamic range aspect!

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Mike:

FIND is faster due to less overhead. If case desensitivity is important, then by all means use SEARCH...

Daniel

 

[dedek]

Re: June/July 2008 Challenge of the Month

This will also do the job...


IF(ISERROR(INDEX($E$2:$E$100;MATCH("ok";IF(SEARCH($D$2:$D$100;A2)>0;"ok";"");0)));"No keyword found";INDEX($E$2:$E$100;MATCH("ok";IF(SEARCH($D$2:$D$100;A2)>0;"ok";"");0)))

regards

 

[gimmer]

Re: June/July 2008 Challenge of the Month

I've posted a new challenge of the month today. Like the last challenge, this one will have many different approaches.

Hi there,
First Thanks for a great site, always a solution for my excel problems.

I created at macro for this challange.

here is the macro:
Sub subtotal()
Dim text As String
Dim soeg As String
For Each c In Selection
text = c.Value
For i = 1 To 10
soeg = Cells(i, 4).Value
If InStr(1, text, soeg, vbTextCompare) Then
If soeg <> "" Then
c.Offset(0, 2).Value = soeg
End If
End If
Next i
Next c
End Sub

Thank you
Lars (From Denmark)

 

[D Kelly O'Day]

Re: June/July 2008 Challenge of the Month

Here's my VBA approach that uses ranges for both phrases and keywords:

Public Sub Keyword_Assign()
        Set Phrases = Worksheets("sheet1").Range("A1:A10000")
         No_keys = Range("d1").CurrentRegion.Rows.Count - 1
         ReDim Keys(No_keys, 2)
      For n = 1 To No_keys
            Keys(n, 1) = Cells(n + 1, 4)
            Keys(n, 2) = Cells(n + 1, 5)
      Next n
' Cycle through Keywords, get next keyword text string to be searched
    For i = 1 To No_keys
            Target = Cells(i + 1, 4)
            Assignee = Cells(i + 1, 5)
            'Search the phrase range for target using Find
                Set T_Cells = Phrases.Find(What:=Target, _
                    After:=Phrases.Cells(Phrases.Cells.Count), _
                    LookIn:=xlFormulas, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
           ' Check to see if anything found, if no, increment For/Next     ​
                If T_Cells Is Nothing Then GoTo 99
                Application.Goto reference:=T_Cells
            Do
              'Loop through T_Cells until last entry found
               Set T_found_cell = T_Cells
              Set T_Cells = Phrases.FindNext(After:=T_found_cell)
              rw = T_Cells.Row
             Cells(rw, 2) = Keys(i, 2)
             If T_found_cell.Row >= T_Cells.Row Then Exit Do
           Loop
         99
   Next i
End Sub

D Kelly O'Day
http://processtrends.com

 

[DiscoPistol]

Re: June/July 2008 Challenge of the Month

This Code takes into account having more than 1 color.

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range("A2:A25")


For Each Value In rng
x = 2
Do Until x = 10
Col = Cells(x, 4).Value
myint = InStr(1, Value, Col)
If myint > 0 Then
If Value.Offset(0, 1).Value <> 0 Then
Value.Offset(0, 1).Value = Value.Offset(0, 1).Value & ", " & Cells(x, 5).Value
Else
Value.Offset(0, 1).Value = Cells(x, 5).Value
End If
End If
x = x + 1

Loop
Next Value

End Sub

Not great but I feel a real sense of pride from doing that......

DP

 

[jeffreyweir]

Re: June/July 2008 Challenge of the Month

Here's a non-array and non-VBA solution.

Cut the keyword/assigned table, and paste special transposed, so they go across the screen somewhere. I added another row at the top first, to make things match up nicely. So assuming the keyword/assigned table now runs horizontally from rows I1 to Q2, input this formula in every cell beneath: =IF(ISNUMBER(SEARCH(I$1,$A3)),$A3,"")

Copy this to other cells, all the way down to the last row that your dataset occupies.

Then, in the Assigned To column, put this: =INDEX($I$1:$Q$2,2,MATCH(A3,I3:Q3,0))

No array formula necessary - although granted there is one cut and paste, and a bit of formula copying.

 

[Andrew Fergus]

Re: June/July 2008 Challenge of the Month

This challenge got me wondering if you could write a formula (not VBA) to return multiple values. The issue is the inability to loop using a formula without using a self- referencing formula. This exercise can be done with a single helper cell (which is the loop counter via a self referencing formula) and a circular reference formula like this :<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
1) select menu option Tools > Options > Calculation > tick 'Iteration' and set the 'Maximum Iterations' to a number greater than the number of lookup values (I used 100).<o></o>
2) for simplicity purposes name the lookup range 'LookUps' and the return values 'Returns' (in this case I2:I10 and J2:J10)<o></o>
3) In cell K1 enter the formula =K1+1 (this is the loop counter)<o></o>
4) This formula assumes the phrases to search are contained in column A, starting at A2.<o></o>
5) Enter the following formula into cell D2 and copy down: <o></o>

=IF(AND($K$1=COUNTA(Lookups)+1, LEN(D2)>2),LEFT(D2,LEN(D2)-2), IF($K$1=1, "", D2) & IF($K$1>COUNTA(Lookups), "", IF(ISERROR(SEARCH(INDEX(Lookups,$K$1),$A2)), "", INDEX(Returns,$K$1) & ", ")))

<o></o>
<o></o>
To recalculate the values select cell K1, press the F2 key and press Enter.<o></o>
<o></o>
Andrew

P.S. this assumes the data is relatively 'clean' without the extra spaces etc. and doesn't take account of 'red' vs 'Redditch'.

 

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

Andrew - I love that method! Just had a lot of fun playing with it.

This is a tweaked version - per Aladin's method earlier, that takes account of the CHAR(160)s and Redditch:

=IF(AND($K$1=COUNTA(Lookups)+1, LEN(D2)>2),LEFT(D2,LEN(D2)-2), IF($K$1=1, "", D2) & IF($K$1>COUNTA(Lookups), "",IF(ISERROR(SEARCH(" "&INDEX(Lookups,$K$1)&" "," "&SUBSTITUTE($A2,CHAR(160),""))), "",INDEX(Returns,$K$1)&", ")))