Search a single string and find the Nth match (Complex)

J

jblack13

New Member
Joined
Jun 12, 2015
Messages
9
I have a chart in a spreadsheet which lists when tax credit applications were submitted, or approved for each of a number of film production companies. There are about 7 different kinds of tax credits I'm tracking, and there are about a dozen film production companies which file various applications based on which of the 7 tax credits they qualify for.

The chart is updated by another department with the filing dates as filings are submitted or approval dates as certifications are received. However, most productions last longer than 12 months, and therefore have to file twice. I am trying to present a high-level executive summary which includes an update of the status of all the filings for each production company and for my purposes only care about the last date in the cell (i.e. the date of the most recent submission/certification).

For example:

TaxCredit1TaxCredit2TaxCredit3TaxCredit4
ProdCo1January 1, 2015January 1, 2015January 1, 2015January 1, 2015
ProdCo2January 1, 2015January 1, 2015January 1, 2014 / January 1, 2015January 1, 2015
ProdCo3January 1, 2015January 1, 2015January 1, 2015January 1, 2015
ProdCo4January 1, 2015January 1, 2015January 1, 2015January 1, 2015

<tbody>
</tbody>

TaxCredit3 for ProdCo2: we filed on January 1, 2014 and again on January 1, 2015.
The format that the date is updated in is as seen above, so I put together an absurdly long formula that does an INDEX MATCH of the ProdCo name and the Tax Credit type... it then searches the string in the cell for the occurrence of a month, then spits out the entire date and then converts it into a date format that is recognizable by Excel:

Code: 
=IFERROR(DATEVALUE(IF(ISNUMBER(SEARCH("january",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("January ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17),10,1)=",", SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("January ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)," "," 0",1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("January ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,16)),IF(ISNUMBER(SEARCH("february",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("February ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17),11,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("February ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)," "," 0",1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("February ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)),IF(ISNUMBER(SEARCH("march",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("March ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14),8,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("March ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("March ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14)),IF(ISNUMBER(SEARCH("april",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("April ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("April ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("April ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,14)),IF(ISNUMBER(SEARCH("may",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("May ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,12),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("May ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,12)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("May ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,12)),IF(ISNUMBER(SEARCH("june",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("June ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("June ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("June ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13)),IF(ISNUMBER(SEARCH("july",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("July ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("July ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("July ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,13)),IF(ISNUMBER(SEARCH("august",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("August ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,15),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("August ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,15)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("August ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,15)),IF(ISNUMBER(SEARCH("september",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("September ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,18),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("September ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,18)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("September ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,18)),IF(ISNUMBER(SEARCH("october",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("October ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,16),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("October ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,16)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("October ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,16)),IF(ISNUMBER(SEARCH("november",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("November ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("November ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("November ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)),IF(ISNUMBER(SEARCH("december",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,TC!$8:$8,0))))),IF(MID(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("December ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17),5,1)=",",SUBSTITUTE(MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("December ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17)," ",0,1),MID(INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))),FIND("December ",INDIRECT("TC!"&ADDRESS(MATCH($B$1,y,0),MATCH(B$39,x,0))))+0,17))))))))))))))),"")

It is particularly complex because I wrote it using INDIRECT() formulas so that I could just copy and paste it and it would work dynamically for all of the ProdCo's (each of which as a separate worksheet within the workbook). The ProdCo name is written in the $B$1 cell of each worksheet, B$39 through H$39 are the names of all the Tax Credits (the above formula is placed in the B$40 - H$40 cells, "y" and "x" are the named ranges of the chart which list all the ProdCos, and the Tax Credit types, respectively... and TC is the name of the chart worksheet.

Firstly, if someone could help me clean it up so it's more manageable (perhaps using VBA) that would be awesome.
But mainly my goal is to preform the same search, but skip to the last match... even if I could make the INDEX MATCH start from right to left would probably do it.

In any case, I'm lost in the woods.
If anyone here could offer any advice I'd be very grateful.
Thanks,
James
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
James,

Not the correct data layout but does the formula in G3 help?
It assumes that multiple application dates will be separated by a forward slash '/'
Excel Workbook
ABCDEFGHIJ
2TaxCredit1TaxCredit2TaxCredit3TaxCredit4TC1TC2TC3TC4
3ProdCo1January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015
4ProdCo2January 1, 2015January 1, 2015January 1, 2014 / January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015
5ProdCo3January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015
6ProdCo4January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015January 1, 2015
Sheet2



Hope that helps.
 
Upvote 0
Tony,
Thanks for the suggestion. Unfortunately, the dates aren't always separated with a "/", and sometimes when "/"s are used it's never in a uniform manner, here are two examples:

"First application certified on October 17, 2014, 2nd application submitted June 6, 2015"
or
"Certificate issued May 16, 2014 / Second certificate issued April 25, 2015"

Frustratingly, there is no uniformity to the way they make the updates, and frankly it's like herding cats trying to get them to enter the data in a standardized format. Having said that, in every case, the date that appears last (the most recent one) within the string is the one I want to return for my summary.

Perhaps I am doomed to enter the data manually for my summary.
 
Upvote 0
James,

Try with this user defined function...

Code: 
Function Last_Date(Txt)
Last_Date = ""
MyArry = Split(Txt, " ")
For i = UBound(MyArry) To 2 Step -1
LDate = MyArry(i - 2) & " " & MyArry(i - 1) & " " & MyArry(i)
If IsDate(LDate) Then
Last_Date = LDate
Exit For
End If
Next i
End Function
Excel Workbook
DE
9First application certified on October 17, 2014, 2nd application submitted June 6, 2015 xxxxxJune 6, 2015
10Certificate issued May 16, 2014 / Second certificate issued April 25, 2015April 25, 2015
11January 1, 2014 / January 1, 2015January 1, 2015
12January 1, 2015January 1, 2015
Sheet2
 
Upvote 0
Tony,

This is fantastic and working very well. Thank you very much!

I don't know if there's a catch-all way of going about it, but since you nailed it for the examples I provided, is there also a way to pick up dates in different formats? (eg. "Sept 15/15" or "September 15/2015" or "(Sept 15, 2015)").
If not don't worry about it, you've already made my life 100x easier.
Thanks again!
 
Upvote 0
Glad it helps.

You can try the below which will deal with those examples.

If any more identifiable variants then add suitable substitutions.

Code: 
Function Last_Date(Txt)
Last_Date = ""


'** Add other subs if required
Txt = WorksheetFunction.Substitute(Txt, "/", " ")
Txt = WorksheetFunction.Substitute(Txt, "(", "")
Txt = WorksheetFunction.Substitute(Txt, ")", "")




MyArry = Split(Txt, " ")
For i = UBound(MyArry) To 2 Step -1
LDate = MyArry(i - 2) & " " & MyArry(i - 1) & " " & MyArry(i)
If IsDate(LDate) Then
Last_Date = Format(CDate(LDate), "mmmm dd, yyyy")
Exit For
End If
Next i
End Function



Excel Workbook
DE
9First application certified on October 17, 2014, 2nd application submitted June 6, 2015 xxxxxJune 06, 2015
10Certificate issued May 16, 2014 / Second certificate issued April 25, 2015April 25, 2015
11January 1, 2014 / January 1, 2015January 01, 2015
12January 1, 2015January 01, 2015
13Sept 15/15September 15, 2015
14September 15/2015September 15, 2015
15(Sept 15, 2015)September 15, 2015
16
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
251
Marcelo Branco
M
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
143
gtd526
gtd526
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
172
gtd526
gtd526
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
154
gtd526
gtd526
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
448
AndyEd
A

Forum statistics

Threads
1,215,430
Messages
6,124,851
Members
449,194
Latest member
HellScout

Share this page

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