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

I was able to do the rank, but I don't how this will really help with my
goal.
I need to be able to capture a value (sales) from 2 different sheets
corresponding to the same value (affiliate ID)...

so lets say sheet 1 is like this

Affiliate number sales
2 $10
5 $5

sheet 2 is like
Affiliate number sales
2 $13
1 $12
5 $3

then the final result would be

Affiliate number sales 04 sales 05
1 - $12
2 $10 $13
5 $5 $3

Then I can get the percentage of change, and then rank by percentage of
rank.

Any ideas?

Thanks


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

In your comparison sheet you would have (pre-filled) the ID and Name of
the Affiliate, with other columns as I outlined earlier, so:

A --ID, B --Name, C --Previous Sales, D -- Latest Sales, E --
Movement, F -- Rank

The data will begin on row 2 and let's say it goes down to row 20. In
C2 you would have a formula along the lines of:

= VLOOKUP(A2,range_in_previous,2,0) and in D2:

= VLOOKUP(A2,range_in_current,2,0)

where range_in_previous is the range covered in the sheet for the
previous month and will look something like "Previous!$A$2:$B$20" if
the name of the sheet is "Previous". Similarly, range_in_current would
look like "Current!$A$2:$B$20", where Current is the name of this
sheet. The formula in C will look in the previous sheet to get the
value that corresponds to a match with the ID stored in column A,
whereas the formula in D will look in the current month's sheet. None
of the 3 sheets need to be in the same sequence for this to work, as
you are looking for an exact match by setting the final parameter in
the formulae to "0".

You say you understand how to get the "movement" and rank values, so I
hope this helps.

Pete

Pete, thanks for trying to help. But I don't know how to look into the
sales value from 2 different sheets but for the same affiliate ID.

I don't know if that's what I am supposed to use the vlookup for.


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

This is how each sheet is setup


CID ORGANIZATION SALE_AMOUNT PUBLISHER_COMMISSION
746688 XYX 38990.33 5848.64
7837 BBBBB 10848.36 1301.81
1160017 ABCD 6252.26 750.27



So, I have 2 sheets, one with information from Dec 04 and another Dec
05... So on the third sheet I would compare both.

But the 2 sheets are not exact the same, some affiliates are in one but
not the other, I need something that will look at each affiliate ID and
then get the sales value from each sheet and put it side by side on the
third sheet.

Does it make sense?

Thanks


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

Set up your third sheet with appropriate headings on row 1. These are
based on my previous posting, updated to reflect your values:

CID (A1), Organisation (B1), 04 Sales (C1), 05 Sales (D1), Movement
(E1), Rank (F1)

You will then have to list in column A the CID and in column B the
Organisation, as follows:

746688 XYX
7837 BBBBB
1160017 ABCD
etc

Then in C2 of the third sheet you need to enter this formula:

= IF(ISNA(VLOOKUP('Data 04'!A$2:C$180,1,0)),0,VLOOKUP('Data
04'!A$2:C$180,3,0)) and in D2:

= IF(ISNA(VLOOKUP(A2,'Dec 05'!A$2:C$200,1,0)),0,(VLOOKUP(A2,'Dec
05'!A$2:C$200,3,0))

The first formula assumes the data in the "Dec 04" sheet extends to row
180, and the second formula assumes the data in the "Dec 05" sheet
extends to row 200 - adjust these ranges as necessary, then copy the
formulae down.

You said in your earlier posting that you knew how to get the
"Movement" and "Rank" columns, so I leave this for you to enter in
columns E and F.

I hope this is a bit clearer now.

Pete

I did it!
Thank you guys! Pete specially!


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

Thanks for the feedback - glad you got it working.

Pete