Hi,
It would really help my work if I could compare reports from 2
different months and have a type of biggest movers index or rank.
In these case, the numbers have to do with sales by each affiliate.
Each of them have unique ids, their sales might change from month to
month, so basically I would like to get 2 individual reports, then
excel would look into them and tell me which affiliates moved the most
up or down, selling more than previously or selling less.

Any ideas on how to get started or whats the best approach?

I appreciate your input.

Thanks


--
skuba
------------------------------------------------------------------------
skuba's Profile: http://www.excelforum.com/member.php...fo&userid=6696
View this thread: http://www.excelforum.com/showthread...hreadid=503110

Might want to consider a pivot table. Assuming your data is in database
format, and from the sound of it it is, add a month column if you don't
already have one, and label each block of data with the right month, such
that if your block of data was in say A2:D100, then in E2:E100 you put
Jan-06, and with your second block of data underneath in say A101:D220, in
E101:E220 put Feb-06. Give the column a header and call it month.

Select all your data and do Data / Pivot table and PivotChart report, then
hit next / next / finish.

Drag your salesman ID field into the ROW areas and then drag the Sales field
into the DATA field. Lastly drag the Month field into the COLUMN area.

You will now have a table of values, and to sort by sales amount, click on
one of the salesman IDs, then right click and choose Field Settings /
Advanced, and then select 'Descending' in the autosort options, and 'Sum of
sales' in the 'using field' section and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"skuba" wrote in
message ...

Hi,
It would really help my work if I could compare reports from 2
different months and have a type of biggest movers index or rank.
In these case, the numbers have to do with sales by each affiliate.
Each of them have unique ids, their sales might change from month to
month, so basically I would like to get 2 individual reports, then
excel would look into them and tell me which affiliates moved the most
up or down, selling more than previously or selling less.

Any ideas on how to get started or whats the best approach?

I appreciate your input.

Thanks


--
skuba
------------------------------------------------------------------------
skuba's Profile:
http://www.excelforum.com/member.php...fo&userid=6696
View this thread: http://www.excelforum.com/showthread...hreadid=503110

Apologies, just realised you wanted the biggest changes as opposed to the
biggest sales over that period.

Regards
Ken...................

"Ken Wright" wrote in message
...
Might want to consider a pivot table. Assuming your data is in database
format, and from the sound of it it is, add a month column if you don't
already have one, and label each block of data with the right month, such
that if your block of data was in say A2:D100, then in E2:E100 you put
Jan-06, and with your second block of data underneath in say A101:D220, in
E101:E220 put Feb-06. Give the column a header and call it month.

Select all your data and do Data / Pivot table and PivotChart report, then
hit next / next / finish.

Drag your salesman ID field into the ROW areas and then drag the Sales
field into the DATA field. Lastly drag the Month field into the COLUMN
area.

You will now have a table of values, and to sort by sales amount, click on
one of the salesman IDs, then right click and choose Field Settings /
Advanced, and then select 'Descending' in the autosort options, and 'Sum
of sales' in the 'using field' section and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"skuba" wrote in
message ...

Hi,
It would really help my work if I could compare reports from 2
different months and have a type of biggest movers index or rank.
In these case, the numbers have to do with sales by each affiliate.
Each of them have unique ids, their sales might change from month to
month, so basically I would like to get 2 individual reports, then
excel would look into them and tell me which affiliates moved the most
up or down, selling more than previously or selling less.

Any ideas on how to get started or whats the best approach?

I appreciate your input.

Thanks


--
skuba
------------------------------------------------------------------------
skuba's Profile:
http://www.excelforum.com/member.php...fo&userid=6696
View this thread:
http://www.excelforum.com/showthread...hreadid=503110

So do you have any suggestion?
It's important to know also that maybe 1 year ago some of those
affiliates didn't exist, so they might be new.


--
skuba
------------------------------------------------------------------------
skuba's Profile: http://www.excelforum.com/member.php...fo&userid=6696
View this thread: http://www.excelforum.com/showthread...hreadid=503110

Suppose you have a list of Affiliates in one column (maybe with their
ID to reference more easily). In two adjacent columns you want the
sales figures for last month and the sales figures for this month, then
you can work out the percentage change (or difference, depending on how
you class the "movement"). In a column next to that you can make use of
the RANK( ) function to tell you the ranking based on the movement.

Your sales figures could be obtained from another sheet where the
details are kept - you need to decide whether to have one sheet for
each month or one composite sheet with each month's data going across.
You may already have something at present which records the sales
figures, but your posting is not clear on this.

If you want some more specific help you will have to give more details
on what you have at present.

Hope these general comments help.

Pete

So, I guess to do this I have to order both reports by affiliate ID, so
I can actually compare sales from each month.
But, is there way to do this automatically?
for instance, look at a certain affiliate, locate it on the first
report and copy the sales, then go to the next sheet locate the same
affiliate ID and copy the sales from that month?

Or do I have to manually put them in ID order and then copy them side
by side?

Thanks


--
skuba
------------------------------------------------------------------------
skuba's Profile: http://www.excelforum.com/member.php...fo&userid=6696
View this thread: http://www.excelforum.com/showthread...hreadid=503110

So, I guess to do this I have to order both reports by affiliate ID, so
I can actually compare sales from each month.
But, is there way to do this automatically?
for instance, look at a certain affiliate, locate it on the first
report and copy the sales, then go to the next sheet locate the same
affiliate ID and copy the sales from that month?

Or do I have to manually put them in ID order and then copy them side
by side?

Thanks


--
skuba
------------------------------------------------------------------------
skuba's Profile: http://www.excelforum.com/member.php...fo&userid=6696
View this thread: http://www.excelforum.com/showthread...hreadid=503110