sree


Hi

I have two worksheets in a workbook,

In sheet 1, i have columns Sector level 2 and 3, for some enterprises

In Sheet 2, i have columns sector level 1, for the same enterprises,

I wish to create a macro, which would look for corresponding enterprises which is the only common element in both sheets and then it should replace corresponding sector level 2 with sector level 1 information from sheet 2.

Please help me.

I need to create a macro because these sheets are updated everyday and they are dynamic.

Thank you in advance.





Re: VBA macro to replace

sjoo


hello,

I've read your post. but don't fully understand it.

plz explain it in detail or show the worksheet image.

and fyi, i add a reference

http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

best regards

sjoo






Re: VBA macro to replace

sree

image sheet 1

INDUSTRIE INDUSTRIE SOUS INDUSTRIE mediane IK 2008
Basic Resources Basic Resources Forestry & Paper 1,26
Basic Resources Industrial Metals 2,02
Basic Resources Mining 1,00
Chemicals Chemicals Chemicals 1,37
Automobiles & Parts Automobiles & Parts Automobiles & Parts 1,89
Food & Beverage Food & Beverage Beverages 1,26
Food & Beverage Food Producers 1,89
Personal & Household Goods Personal & Household Goods Household Goods 1,57
Personal & Household Goods Leisure Goods 2,21
Personal & Household Goods Personal Goods 2,21
Personal & Household Goods Tobacco 1,20
Media Media Media 0,71
Retail Retail Food & Drug Retailers 4,89
Retail General Retailers 2,99

image sheet 2

ICB Level 1 Sector ICB Level 2 Sector
Consumer Services Retail
Consumer Services Retail
Consumer Services Media
Telecommunications Telecommunications
Financials Insurance
Financials Insurance
Consumer Services Media
Consumer Services Media
Industrials Industrial Goods & Services
Consumer Services Travel & Leisure
Technology Technology
Financials Financial Services
Industrials Industrial Goods & Services
Consumer Goods Personal & Household Goods

this is just a small portion of the sheet i mailed to help you visualise my problem.

This is what i am looking for a macro to do. I would like to automatically delete the first and third columns in first image. Then i want to find identical items in column B in both images and replace column B in image 1 with coreesponding column A items in image 2.

I wish i am clear, my english may be ambigous.

Thank you for your help in advance







Re: VBA macro to replace

sjoo

hi sree

i've just send an example file.

i added some comments on the code.

plz check your gmail.

bye





Re: VBA macro to replace

sree

Hi Sjoo

Thats really kind of you, i wish i could get more help, got some problems with code while i was trying to modifying as required.

I mean compilation error "variable not defined". Don't understand why.

Please let me know your suggestions

Thanks in advance.

Sree






Re: VBA macro to replace

sree

Hi

I wish to get more help, Please

i wish to merge identical cells in the first column, then i would like to average corresponding medianes in all years.

I have highlighted an example

I would like to merge Basic Materials into one cell and then average different subsector medians [(1.26 + 2.00 + 0.99 + 1.36)/4], to get a result like

INDUSTRIE mediane IK 2008 mediane IK 2007 mediane IK 2006 mediane IK 2005 mediane IK 2004 mediane IK 2003 mediane IK 2002 mediane IK 2001 mediane IK 2000 mediane IK 1999 mediane IK 1998 mediane IK 1997 mediane IK 1996
Basic Materials 1,26 1,02 1,11 0,94 0,92 0,87 0,75 0,81 0,93 0,95 0,99 0,88 1,01
2,00 2,09 2,24 1,76 1,78 1,29 1,15 1,08 1,01 1,07 1,09 1,10 1,28
0,99 0,86 0,98 0,92 0,81 0,74 0,69 0,64 0,59 0,61 0,64 0,54 0,38
Basic Materials 1,36 1,42 1,50 1,46 1,43 1,29 1,15 1,15 1,18 1,07 1,08 1,36 1,37
Consumer Goods 2,01 2,00 1,88 1,88 1,83 1,73 1,83 1,69 1,67 1,60 1,55 1,46 1,61
Consumer Goods 1,26 1,27 1,41 1,21 1,27 1,27 1,22 1,29 1,46 1,52 1,48 1,63 1,71
1,89 1,94 1,85 1,80 2,11 1,98 1,93 1,79 1,77 1,69 1,78 1,76 1,78
Consumer Goods 1,52 1,46 1,62 1,69 1,80 1,81 1,77 1,71 1,77 1,89 1,87 1,86 1,71
2,21 2,33 2,40 2,47 2,72 2,67 2,27 1,91 2,05 2,09 2,65 2,39 2,04
2,20 2,05 2,15 2,02 2,22 2,15 1,83 1,89 1,81 1,96 2,13 2,07 2,09
1,20 1,40 1,32 1,31 1,23 1,40 1,63 1,55 1,21 1,70 1,90 2,14 2,39
Consumer Services 0,74 0,78 0,84 0,81 1,03 0,77 0,72 0,70 0,79 0,75 0,67 0,87 0,89
Consumer Services 4,89 5,29 5,72 5,15 5,01 4,70 4,01 4,12 3,92 4,24 4,62 4,82 5,01
2,99 2,98 3,03 3,07 3,17 3,15 2,87 2,87 2,81 2,76 2,74 2,90 2,87
Consumer Services 1,40 1,38 1,36 1,38 1,43 1,40 1,28 1,28 1,28 1,38 1,38 1,53 1,44
Health Care 1,62 1,66 1,72 1,74 1,71 1,72 1,50 1,49 1,41 1,40 1,41 1,43 1,37
0,88 0,86 0,93 1,00 0,83 0,76 0,62 0,62 0,68 0,66 0,91 0,78 0,94
Construction & Materials 2,49 2,51 2,57 2,40 2,24 2,10 1,90 1,78 1,57 1,52 1,68 1,67 1,97
Industrial Goods & Services 1,30 1,37 1,47 1,48 1,47 1,35 1,25 1,44 1,36 1,28 1,31 1,51 1,30
1,62 1,60 1,67 1,56 1,56 1,46 1,32 1,44 1,53 1,54 1,82 1,99 2,15
1,46 1,49 1,48 1,43 1,41 1,31 1,23 1,27 1,30 1,23 1,29 1,35 1,47
1,63 1,69 1,82 1,75 1,64 1,49 1,29 1,34 1,33 1,34 1,50 1,62 1,66
2,15 2,03 2,02 2,18 2,02 2,07 1,73 1,85 1,65 1,56 1,43 1,48 1,49
1,92 1,77 2,00 2,07 2,03 2,02 1,79 1,76 1,74 1,69 1,71 1,81 1,96
Oil & Gas 0,54 0,57 0,56 0,60 0,58 0,53 0,33 0,44 0,50 0,34 0,34 0,44 0,40
1,04 1,12 1,10 1,06 0,89 0,89 0,79 0,86 0,70 0,62 0,87 0,92 0,76
Technology 1,73 1,77 1,80 2,00 2,28 2,20 1,76 1,75 1,64 1,82 2,19 2,36 3,02
1,82 1,79 1,81 1,75 1,74 1,48 1,34 1,42 1,65 1,53 1,76 1,82 2,12
Telecommunications 0,67 0,78 0,70 0,70 0,66 0,61 0,58 0,51 0,48 0,43 0,50 0,53 0,54
0,56 0,58 0,50 0,54 0,52 0,50 0,53 0,50 0,45 0,40 0,31 0,31 0,30
Utilities 0,54 0,52 0,56 0,52 0,48 0,49 0,66 0,75 0,76 0,77 0,76 0,70 0,62
0,72 0,63 0,67 0,70 0,61 0,62 0,62 0,76 0,76 0,63 0,71 0,70 0,69
Financial Services 0,96 1,03 0,96 0,97 0,76 0,76 0,81 0,91 0,92 0,81 1,26 2,09 1,45

INDUSTRIE mediane IK 2008 mediane IK 2007 mediane IK 2006 mediane IK 2005 mediane IK 2004 mediane IK 2003 mediane IK 2002 mediane IK 2001 mediane IK 2000 mediane IK 1999 mediane IK 1998 mediane IK 1997 mediane IK 1996
Basic Materials 1,40 1,35 1,46 1,27 1,23 1,05 0,94 0,92 0,93 0,93 0,95 0,97 1,01

Please help me with this, Actually i wish to create a line graph of average median performance of these industrial sectors which displays trend of IK over past ten years.

Thank you in advance

Sree