+ Reply to Thread
Results 1 to 2 of 2

Extracting specific data from large raw data

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2010
    Posts
    27

    Extracting specific data from large raw data

    I am trying to create an Excel tool to manage salespeople's daily activities (tasks). We are actually using a CRM system to manage their tasks, but unfortunately it is still under development and does not have any reporting features (so salespeople are inputting their daily tasks but there is no way for managers to see the overall data on the screen). So the idea of this tool is to copy/paste the downloadable raw data and show various reports.

    I have most of the tool completed, most importantly the "Dashboard" sheet where it allows shows the number of tasks completed and the purpose of those tasks in the previous week, and also tasks scheduled for next week. It also includes the monthly cumulative data. This sheet is linked to "Tasks by Salesperson" and "Purposes by Salesperson", where it extracts and counts from the Raw Data.

    I have having EXTREME difficulty working on "Completed Task Summary" sheet. Basically, I want to show all the details of the tasks completed by the particular salesperson during the previous week. The name of the salesperson is in C3, the week start/end is in G3 and G4. All I need to do is show the details (Customer Name, Action taken, Purpose of the action, date scheduled / completed, and comments) of each task completed by the selected salesperson and compelted between those dates. The raw data is found in "RawData" sheet.

    I've been playing around with combinations of VLOOKUP, INDEX, MATCH, LARGE, SMALL, etc... but I've had no luck. I would appreciate any suggestion or help on this.

    P.S. the Raw Data is in Korean, but this tool will be used across the world and therefore should be made so that the language of the raw data does not matter (or is controllable, as I did in "Master Setting" sheet).
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Extracting specific data from large raw data

    Hi tekobayashi,

    I'm not sure if I understood your trouble precisely as I'm not familiar with this level of using excel (reports and whatnot) but I think I have at least a partial solution.

    You would need another column in your 'Comp. Task Summary' sheet which stated which date you were referring to, just for example I'm using column A in that same sheet here. Then you would select whichever column in the RawData worksheet you want to apply to that cell/column and drag down.

    e.g. For the first 'Action' cell (B8) you'd have something like:

    Please Login or Register  to view this content.
    Where in A8 you would have "2012/11/09".

    Since this wouldn't filter for salesperson you'd have to amend it with an IF and AND fuction - though I don't have time to test this it might look something like:

    Please Login or Register  to view this content.
    Hope that helps a little!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1