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

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 -->
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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!
 
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
 
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
 
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);)
 
Re: June/July 2008 Challenge of the Month

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

Rich (BB code):
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
 
Re: June/July 2008 Challenge of the Month

This Code takes into account having more than 1 color.

Code:
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
 
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.
 
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
2) for simplicity purposes name the lookup range 'LookUps' and the return values 'Returns' (in this case I2:I10 and J2:J10)<o:p></o:p>
3) In cell K1 enter the formula =K1+1 (this is the loop counter)<o:p></o:p>
4) This formula assumes the phrases to search are contained in column A, starting at A2.<o:p></o:p>
5) Enter the following formula into cell D2 and copy down: <o:p></o:p>
=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:p></o:p>
<o:p></o:p>
To recalculate the values select cell K1, press the F2 key and press Enter.<o:p></o:p>
<o:p></o:p>
Andrew

P.S. this assumes the data is relatively 'clean' without the extra spaces etc. and doesn't take account of 'red' vs 'Redditch'.
 
Last edited:
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)&", ")))
 

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

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
Back
Top