Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Learning Querys (Excel 2000)

    Would anyone know of a web based source, where I could learn in laymans terms how to do a query in Excel. I have never done one, and the help file does not seem to be helping me. I have tried the wizard and can't get anywhere. I am obviously not an advance user but am willing to learn.

    I process/ download sales leads and need to get them sorted by state/territory for reps and then forward the Excel file on to each rep. This will be done on a weekly basis, so I am trying to automate it, rather than having to pick through them manually. I appreciate any help or referrals to sources of "dummies" type help.

    Thanks
    Heather

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Learning Querys (Excel 2000)

    Could you be a little more specific?
    What kind of Query are trying to do?
    Are you trying to get info from a website?
    Are you trying to get info from a database?
    Are you trying to get info from another program?
    Are you trying to summarize spreadsheet data using a Pivot Table?

    The more details you can supply the more we can help you?

    Steve

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Learning Querys (Excel 2000)

    ok, I download a list of leads into a text file. I import that into Excel. That part is easy.
    After that, what I will now have to do is distribute the leads to the reps according to their territory.
    IE: If Joe Rep has Texas, Arkansas and everything in Wisconsin except area code 715 as his territory, I would like to be able to get something set up so that I can just click on a query or something with his name and then it will automatically filter the leads for me.
    Now I am sorting by state, then copying and pasting into a new sheet, but this gets cumbersome as the number of leads and reps increases. Also I have to manually look at area codes in phone numbers etc.

    I'm not even sure if what I need is a query or just some kind of filter. Thanks again.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Learning Querys (Excel 2000)

    Sounds like a good time to try Tools/Filter.
    Sort the data on Rep name, highlight all the information and click on Tools, select Filter, then choose AutoFilter.
    You could also play around with Advanced Filter, but I would think Auto Filter would do the job.
    Maybe you could send an example of the workbook, with dummy data.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Learning Querys (Excel 2000)

    As Chuck mentioned: Autofilter sounds like what you might want. Some additional comments/expansion:
    Autofilter will filter in place. WIth autofilter you can use the SUBTOTAL function to get statistics on the displayed data
    In addition you can set up charts using the entire database and filtering will "remove" those points from the chart (charts only show visible data)
    You can filter on several items, each one ANDing together.

    Additionally:
    You can also use (data - filter) Advanced filter to "copy" filtered elements to other parts of the workbook. You can criteria established to "extract" the particular data or use the criteria to filter in place.

    You can also extract summary stats of the database using the (Data - )pivot table report wizard.

    Hope this helps,
    Steve

  6. #6
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Learning Querys (Excel 2000)

    Aha, herein lies the problem. I have to put the rep name in and email the leads to him. The leads don't come in with a rep name.

    So if for example I get 60 contact leads from 32 different states, I have to currently look at a list of states (which I have on paper), figure out which rep has that state, then copy that line (or set of lines if I have filtered by state). I have to go through all the lines and pull them out. Also, filtering will do it by state alphabetically but if a guy has AL, GA, MS, NC and SC as his states, I want to be able to put in his name and it will return ALL the data from all those states, or country or zip code as the case may be. This is going to be an ongoing thing every week, whole new set of data, possibly new reps etc. to sift through.

    Maybe I would be best to pull them all into Goldmine or some lead management program like that and create a group for each rep. I was hoping not to have to do that though.

    Thanks again.
    Heather

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Learning Querys (Excel 2000)

    Why not create a lookup list in an excel sheet: state and rep name (or zipcode and name, whatever). you could have a column with the formula to take the state in that row, lookup in the list who has the state and have excel list the name. Then you could filter on the name.

    If this is not clear, if you provide an example data table, and a partial list, I could demo it for you. The function you would use is VLOOKUP.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Learning Querys (Excel 2000)

    Heather,

    As Steve suggested, VLookups should help. I attached a small sample workbook.
    The Reps are listed on one tab, the imported list on the other.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  9. #9
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Learning Querys (Excel 2000)

    This is great. This will work! Thanks so much for your patience!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •