grouping by instr text? 1

[CRoompa]

Using Crystal 11, original data is just a text file, but I imported it into a spreadsheet; then I am trying to look at a column and group similar webaddresses, count the number of hits.
Data looks like this mess:

56 /cmdoc.html
10 /cmdocs/cr0764/cr0764.html
10 /cmdocs/ChangeRequestTemplate.doc
21 /reports.html
14 /nov09reports/reportsnov09.html
10 /circuits.html
5 /circuits/niprnetcir.html
9 /trbmeetings2009.html
9 /trbmeetings.html

Ideally I would like to see something like:

76 cmdoc
35 reports
15 circuits
18 trbmeetings

Any suggests to get me even close would be helpful.

 

[Turkbear]

Hi,
There appears to be no repeating pattern that could be used to isolate the part you want to display - In your example, for instance, sometimes it is the part between the first and second / character while others have no /, except at the start - futher, unless it is a typo, you are treating cmdoc like it was the same as cmdocs in your sample count.

Perhaps testing for the number of / chars then using the Instr and Mid functions you could get most of it.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you have a limited number of categories, you could write a formula like this:

if {table.field} like "*cmdoc*" then
"cmdoc" else
if {table.field} like "*reports*" then
"reports" else
if {table.field} like "*circuits*" then
"circuits" else
if {table.field} like "*trbmeetings*" then
"trbmeetings" else
"other"

Then insert a group on this and then you can insert summaries at the group level.

-LB

 

[CRoompa]

I was trying to use mid and instr and over complicating it. the if statements work. Thanks.