Compare data - month vs month

[flashdavies]

Hi

I would like to be able to compare data between two tables. Each table
contains around 60 brands; however,the exact number of brands varies
month to month, but the top brands are always there.
This is the data I have each month:

Column 1 = rank
Column 2 = brand
Column 3 = points (relates to performance)

But I would like to compare the month with the previous month and for
this I need to include two more columns:
Column 4 = percentage change
Column 5 = rank last month

I have been calculating the figure for columns 4 & 5 with formulas one
at a time. Can anyone suggest how I might automate the task, most
likely by using a macro?

Typically I paste the data onto different worksheets (but they don't
have to be, if that makes it any easier -- they could be on the same
worksheet).
Here is a sample to illustrate the data I need to compare (I have used
commas instead of columns in this example).


Worksheet 1 - June

1,GM,10000
2,Toyota,9000
3,Ford,8900
[etc - up to 60 brands]

Worksheet 2 - May

1,GM,9800
2,Ford,9000
3,Toyota,8000

If I had the same brands and the same number of brands each month, I
would just sort them alphabetically and use a formula to compare, but
this doesn't work for me because the number of brands can change.
However the top brands do remain, and they are the ones I am
interested in.

At the moment I do this.

a = Toyota in June
b = Toyota in May

Percentage change = (a-b)/b

I think I need some kind of loop macro that stars with one of the
brands in one list, then searches for the brand int the other list,
and then performs the calcution.

I am trying to learn about macro's at the moment but I don't know
quite enough yet to create what I need.

Regards,
Harry

 

[Roger Govier]

Hi
One way
On June sheet in D1
=IF(ISERROR(INDEX(May!C:C,MATCH(May!B:B,A1,0))),"",
INDEX(May!C:C,MATCH(May!B:B,A1,0))
in E1
=IF(D1,(D1-C1)/C1,"")

Copy D1:E1 down as far as required.

 

[flashdavies]

Hi
One way
On June sheet in D1
=IF(ISERROR(INDEX(May!C:C,MATCH(May!B:B,A1,0))),"",
INDEX(May!C:C,MATCH(May!B:B,A1,0))

I pasted this formula, but Excel suggested a slight correction: it put
one more bracket at the very end. Does that sound right to you?
However when I pressed return nothing happened. The square goes blank,
does that sound right too?

in E1
=IF(D1,(D1-C1)/C1,"")

Copy D1:E1 down as far as required.

I set up an example workbook, with everything the same as my example.
(There were no column headers.)
So A1, had the rank, cell B1 the name of the company, cell C3 the
number of points.
Worksheet 1 was named May, and the second sheet named June.
I entered the formula as suggested, but added one more bracket to the
Column D formula (as suggested by Excel).
I then dragged the formulas down the column.
The result:
Column D: blank
Column E: #VALUE!

I was hoping that cell D1 on the June worksheet would display the
percentage increase, and cell E1 the rank for the previous month.

Thanks for trying to help me. I shall read up on INDEX and MATCH etc
to see if I can understand the solution that you have given me a
little better -- it may just need some slight tweaking.

Regards,
Harry

 

[Roger Govier]

Hi

Very sorry.
Typed straight into reply before first infusion of caffeine for the
day!!!!
It should of course be

=IF(ISERROR(INDEX(May!C:C,MATCH(B1,May!B:B,0))),"",
INDEX(May!C:C,MATCH(B1,May!B:B,0)))

The second formula was fine (need to format the cell as % of course)

 

[flashdavies]

Hi

Very sorry.
Typed straight into reply before first infusion of caffeine for the
day!!!!
It should of course be

=IF(ISERROR(INDEX(May!C:C,MATCH(B1,May!B:B,0))),"",
INDEX(May!C:C,MATCH(B1,May!B:B,0)))

The second formula was fine (need to format the cell as % of course)

Thanks very much. Works perfectly.
Btw, just out of interest (especially to any beginners like me who
stumble upon this thread), I was able to pretty much achieve the same
thing using VLOOKUP, which I came across while reading about INDEX and
MATCH.

Regards,
Harry

 

[Roger Govier]

Hi Harry

Yes, you're quite right. Vlookup has to have the item being looked up in
the first column of the lookup table, whereas Index() Match() can deal
with any situation.

=VLOOKUP(B1,May!B:C,2,0)
would achieve the same result.