Query for MS Excel or MS Access - how do I write...

[kittykat]

I've got something I need to test quickly to see if my logic works. I'm developing a segmentation model and want to segment a list of names based on certain criteria I've developed.

Basically it's all to do with position name and job name - based on these 2 elements I can then segment you into a group.

We've devised a list of key words that indicate what category you are segmented into. There are no duplicates obviously.

Here's a sample list:

Front Office = Category or Segment

If you have one of the following in your position name then you fall into the segment Front Office:

Demand
Contact
Client
Customer
Relationship
Sales
Service
Help Desk
Advisor
Support

I can do this in either Access or Excel but I have 28,000 records to segment. There are 6 categories (eg Front Office, Back Office, Support etc) and each category has key words that define which area they fall into.

Does anyone know how the hell I can do this?? I haven't got a clue but it must be do'able. Thanks!!

[BoD]

In Excel , define a range that contains the Front office key words

Name that range "Front"

next to each item in the 28,000 list use the following formula

=IF(ISNUMBER(SEARCH(Front,M9)),"Front Office","")

Do the same kind of thing for the other categories

[Tas]

oh is that how that works. I'll know more after my advanced excel course I hope!

[Jedi]

Only one problem with your solution BoD, Search works on strings, not arrays (ranges).

[BoD]

Good point..hang on a min

[BoD]

Funny it half works with arrays

Anyway.. somewhat less elegantly

Open a new code module

Function FindDesk(sIn As String, rDesks As Range) As String

Dim rDesk As Range

For Each rDesk In rDesks
If InStr(sIn, rDesk.Value) > 0 Then
FindDesk = rDesk.Offset(0, 1).Value

End If
Next

End Function


Create a range that looks like this somewhere

Pos1 Front Office
Pos2 Front Office
Pos3 Middle Office
Pos4 Back Office

name the first column as "postions"

In the spreadsheet - where A1 contains the postion you want to check

=FindDesk(A1,positions)

[Morrolan]

alternatively: buy Access for Dummies and use that to do it in Access. worked for me.

[kittykat]

Thanks HoD and Jedi. HoD you are a bit of a whizz.

M.. I'm happy to get Access for Dummies if it's in there. Is it easy to do in Access or do I need to write code and stuff??

[Burbage]

I agree, a database is what you need here, not a spreadsheet. If your company can stump up the cash, FileMaker is also worth looking at, it's probably easier to get into than Access.

But first you need a big sheet of paper and work out all your fields and their relationship to each other.

[Morrolan]

M.. I'm happy to get Access for Dummies if it's in there. Is it easy to do in Access or do I need to write code and stuff??

pretty sure it's in there, and don't worry: you won't have to wtite code, it's pretty easy once you have the basic idea.

[Kooky]

Neo would argue it's better to use Excel and a bit of VB. Being a non-programmer, I'd use Access.

I used to do this kind of thing for a living; these days I struggle with an IF statement. Maybe I should use my relocation budget for some refresher courses...

[Bender]

I'd like to have a go at this - it should be easy. Can you give us:

• a list of category/segment names
• for each category/segment name, a list of the words that put a person into that category/segment

And tell us if a person can be a member of more than one category/segment?

Ideally, it'd be great to see the spreadsheet you have, can you provide a sample with personal information like names removed?

[baloo]

Surely the best way to solve this is to poke your head outside of your office door, look for the geekiest looking grad, call him into your office and explain the special assignment you have for him.

[Morrolan]

Surely the best way to solve this is to poke your head outside of your office door, look for the geekiest looking grad, call him into your office and explain the special assignment you have for him.

[BoD]

Jedi works at KK's place?

[Jedi]

Huh? Jedi works at Jedi's place.