Sorting Salesperson Info

Hello

I have a spreadsheet where 1 page records all the sales data relevant to the business, date, client, salesperson etc

What I am looking to do is to have the subsequant shhets in the workbook collate only the relevant sales information for the individual salesperson, i.e. if I have 8 salespeople, there will be 9 sheets in total, my main sheet with all sales recorded and then the 8 individual salespersons information.

Is there a way to have the individual sheet search the main sheet for the individual salesperson and then copy that information over. I have been playing around with some if statements (the extent of my knowledge with excel), and cant seem to get it working too well.

Any help would be appreciated.

Sincerely
Trilogy

Could you do it manually, once, and from then only update the individual saleasperson sheets, and create a new master sheet that sucks information from those individual sheets?

Bob

The functions you want are MATCH() and INDEX()
MATCH returns the position within a list of a given value (your sales person name in this case)
INDEX returns the value at a given position within a array.

So =INDEX(A:Z,MATCH("Ted Bundy",A:A,0),3) will return the value from column C that is on the same row as Ted.
This is essentially identical to VLOOKUP.

The problem that arises is that MATCH (and VLOOKUP) only finds the first match in the list.
To find the second entry you must re-start the search after the point where the first match is found.

This can be achieved by using the OFFSET function.

Mark,

I find that potentially very useful, can you show us how to use the offset function to continue the search, please.

Bob

See this thread where I responded a very similar question:

http://www.excelforum.com/showthread.php?t=613246

As you can see the trick is to use the result from the previous MATCH to alter the OFFSET for the start of the next MATCH.

Mark.

Looking again at my previous post, it could perhaps be improved as follows.
Rather than just using a large number for the "height" in the OFFSET function
one could use the actual length of the list as follows:

Assuming your data is in Sheet1 (columns A to C) and you are moving it to Sheet2 (columns A to C)
Sheet2!D1 : Enter a Sales Person Name.
Sheet2!D2 : =COUNTA(Sheet1!A:A)
Sheet2!E1 : =MATCH(D$1,Sheet1!A:A,0)
Sheet2!E2 : =MATCH(D$1,OFFSET(Sheet1!A$1,E1,,D$2-E1),0)+E1
Replicate E2 down.
Sheet2!A1 : =INDEX(Sheet1!A:A,$E1)
Replicate across to column C and then down as required.

Mark.

I used a variation of this for another project. Maybe the attachment will give you some ideas and get you going.

Dean

Thank you all for your feedback. It has been very helpful.

Sorry I did not post sooner, got swamped at work

Cheers
Trilogy