Consolidate rows on susequent sheets.

  • Thread starter Thread starter Rick Patterson
  • Start date Start date
R

Rick Patterson

Excel 2002
On sheet 1 I have data imported from an Access database. The data consists
of individual sales that are made by a number of different sales people. In
column "B" are the salesperson's names, and going accross in the subsequent
columns is related information about that particular sale.
There are quite a number of sales by each person on the sheet and so I want
to copy all the sales from each person to a seperate sheet in the same book.
I would also like to rename the Tab of that sheet with the salesperson's
name if possible.
The problem is that each time I import data to sheet 1 those names may
change.
If this makes any sense to anyone I would appreciate some direction.
Thanks in advance.
Rick
 
Evidently this doesn't make sense (Not Surprising! :))
To simplify:
I want to consolidate all rows with common names in column "B" to subsequent
sheets. If there are 2 names in "B" then to the next 2 sheets, if there are
6 names in "B" then copy to the next 6 sheets.
Sorry for such novice, bothersome questions. Some day I'll get better at
this.
Thanks
 
Hi Rick,

Your first post makes sense to me, but I get a bit confused by the second
message and "then copy to the next 6 sheets".

If you want the sales for each salesperson on a separate sheet, you will get
quite far with the help of a Pivot table or an Advanced Filter. The process can
be more or less automated with macros, but it depends on how often you need to
do this if it's worth the effort.

Regards
Anders Silven
 
Thanks for the reply Anders,
In column "B" there may be 25 rows of data (Names) in that list of 25 there
may only be 5 unique names and the rest are duplicates. I want to be able
to copy each unique group of names and their associated columns to one of
the next sheets so that each of the following sheets has only information
pertinent to that one salesperson. I then am going to have to work with the
sorted data for each tech on that new sheet.
The purpose is to generate reports such as "Pay Stubs" for each of the
salespeople based on the data from their sales that week.
I will run this particular process once each week after the data is entered
into Access and then imported into Excel.

I hope this helps, not hurts your understanding of my problem.

I am not versed in pivot tables though.

Thanks again,
Rick
 
Rick,

With an Advanced Filter there is an option to copy the filtered records to
another location (sheet). I think it does what you want.
See Excel help for more info.

Regards,
Anders Silven
 
You are quite right, my error. Sorry.

Since we can't filter to another sheet, there is no point in filtering, one
could just as well sort on the names column and then copy and paste into the
other sheets. Doing that each week for 5 salespersons is a bit too much work to
do manually, so maybe you need a macro.

Another way is to have the salespeople's sheets pull data from the master sheet
with the VLOOKUP function. If there is not too much variation in the number of
salespersons and deals each week, a VLOOKUP strategy should be pretty easy to
set up and maintain.

Regards,
Anders Silven
 
Thanks I'll look that direction.
I think that if I potentially could have 15 salespeople I could set up 15
additional sheets in the book and have them look for unique names to
transfer data from the master to the individuals. Thats the way I would
like to lean any way.
Thanks for the input.
Rick
 
Aaargh, this is not my day! VLOOKUP will *not* work, at least not easily. Sorry
for misleading you again. I'll think of something that really works, and test it
before posting next time :=

At least I think I have a good idea of what you want.

Regards
Anders Silven
 
Duh! Thanks a lot, Debra, there it is just off the shelf. One never learns...
Rick, by mistake I first downloaded the next sample file "Create New Sheets from
Filtered List". You can take a look at that too.

Best regards,
Anders Silven
 
Back
Top