software

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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: www.excelforum.com/showthread...hreadid=503110Might 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 A2100, then in E2:E100 you put
Jan-06, and with your second block of data underneath in say A101220, 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 :-)
------------------------------*------------------------------*----------------
quot;skubaquot; gt; wrote in
message ...
gt;
gt; Hi,
gt; It would really help my work if I could compare reports from 2
gt; different months and have a type of biggest movers index or rank.
gt; In these case, the numbers have to do with sales by each affiliate.
gt; Each of them have unique ids, their sales might change from month to
gt; month, so basically I would like to get 2 individual reports, then
gt; excel would look into them and tell me which affiliates moved the most
gt; up or down, selling more than previously or selling less.
gt;
gt; Any ideas on how to get started or whats the best approach?
gt;
gt; I appreciate your input.
gt;
gt; Thanks
gt;
gt;
gt; --
gt; skuba
gt; ------------------------------------------------------------------------
gt; skuba's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6696
gt; View this thread: www.excelforum.com/showthread...hreadid=503110
gt;
Apologies, just realised you wanted the biggest changes as opposed to the
biggest sales over that period.

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

quot;Ken Wrightquot; gt; wrote in message
...
gt; Might want to consider a pivot table. Assuming your data is in database
gt; format, and from the sound of it it is, add a month column if you don't
gt; already have one, and label each block of data with the right month, such
gt; that if your block of data was in say A2100, then in E2:E100 you put
gt; Jan-06, and with your second block of data underneath in say A101220, in
gt; E101:E220 put Feb-06. Give the column a header and call it month.
gt;
gt; Select all your data and do Data / Pivot table and PivotChart report, then
gt; hit next / next / finish.
gt;
gt; Drag your salesman ID field into the ROW areas and then drag the Sales
gt; field into the DATA field. Lastly drag the Month field into the COLUMN
gt; area.
gt;
gt; You will now have a table of values, and to sort by sales amount, click on
gt; one of the salesman IDs, then right click and choose Field Settings /
gt; Advanced, and then select 'Descending' in the autosort options, and 'Sum
gt; of sales' in the 'using field' section and hit OK.
gt;
gt; --
gt; Regards
gt; Ken....................... Microsoft MVP - Excel
gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;
gt; ------------------------------*------------------------------*----------------
gt; It's easier to beg forgiveness than ask permission :-)
gt; ------------------------------*------------------------------*----------------
gt;
gt;
gt;
gt; quot;skubaquot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; Hi,
gt;gt; It would really help my work if I could compare reports from 2
gt;gt; different months and have a type of biggest movers index or rank.
gt;gt; In these case, the numbers have to do with sales by each affiliate.
gt;gt; Each of them have unique ids, their sales might change from month to
gt;gt; month, so basically I would like to get 2 individual reports, then
gt;gt; excel would look into them and tell me which affiliates moved the most
gt;gt; up or down, selling more than previously or selling less.
gt;gt;
gt;gt; Any ideas on how to get started or whats the best approach?
gt;gt;
gt;gt; I appreciate your input.
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt;
gt;gt; --
gt;gt; skuba
gt;gt; ------------------------------------------------------------------------
gt;gt; skuba's Profile:
gt;gt; www.excelforum.com/member.php...foamp;userid=6696
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=503110
gt;gt;
gt;
gt;

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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: www.excelforum.com/showthread...hreadid=503110Suppose 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 quot;movementquot;). 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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: 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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: 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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: www.excelforum.com/showthread...hreadid=503110In your comparison sheet you would have (pre-filled) the ID and Name of
the Affiliate, with other columns as I outlined earlier, so:

A --gt;ID, B --gt;Name, C --gt;Previous Sales, D --gt; Latest Sales, E --gt;
Movement, F --gt; 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 quot;Previous!$A$2:$B$20quot; if
the name of the sheet is quot;Previousquot;. Similarly, range_in_current would
look like quot;Current!$A$2:$B$20quot;, 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 quot;0quot;.

You say you understand how to get the quot;movementquot; 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: www.excelforum.com/member.php...foamp;userid=6696
View this thread: www.excelforum.com/showthread...hreadid=503110