Microsoft Excel - need macro foe some data - Asked By usha anu on 29-May-13 07:34 AM

Hi I have some data in sheet no 1 like below,

NAME

BRANCH

sadsa

CUSTOMER NAME

organization code

as

as

as

asa

Revised

Billd/Accrual

asa

AMOUNT

MACHINENO

BW/Clr

MODEL

SOURCE

CONTRACT TYPE

RATE

OPENING METER

CLOSING METER

READING DATE

CV

sdsa

sdsd

sadsa

sadsa

sadsa

sadsa

sdsd

Location

Region

Model

Stand Model

PG

PS.CS

Mono/Color M/c

Counter

asa

xyz

weqe

weqe

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41367

2070.8

sds

sds

1316

sdsdsdsd

RENTAL

10

27496

32673

22-Mar-13

5177

30404

sds

61343

41103

42197

8

sds

Delhi

North

446132

446132

IR

CS

Mono

Mono

asas

xyz

weqe

weqe

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41370

637.12

sdsa

sdsa

446

sdsdsdsd

RENTAL

10

34887

36335

23-Mar-13

1448

30404

sdsa

61343

40690

41785

8

sdsa

Delhi

North

446132

446132

IR

CS

Mono

Mono

asas

xyz

weqe

weqe

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41370

993.52

sd

sd

44232

sdsdsdsd

RENTAL

10

4966

7224

22-Mar-13

2258

30404

sd

61343

41240

42334

8

sd

Delhi

North

446132

446132

IRC

CS

Mono

color

asas

xyz

weqe

weqe

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41370

1591.04

df

df

44232

sdsdsdsd

RENTAL

10

14493

18109

21-Mar-13

3616

30404

df

61343

41242

42336

8

df

Delhi

North

446132

446132

IR

CS

Mono

Mono

asas

xyz

fdg

fdg

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41370

1174.36

dfd

dfd

44232

sdsdsdsd

RENTAL

10

5737

8406

23-Mar-13

2669

30404

dfd

61343

41239

42333

8

dfd

Delhi

North

446132

446132

IRC

CS

Mono

color

asas

xyz

fdg

fdg

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41370

1229.8

dsf

dsf

44232

sdsdsdsd

RENTAL

10

11712

14507

19-Mar-13

2795

30404

dsf

61343

41243

42337

8

dsf

Delhi

North

446132

446132

IRC

CS

Mono

Mono

asas

xyz

ty

ty

as

1.31E+09

Revenue

Billed Revenue

Billed

Billed

41367

4240.08

dsf

dsf

44232

sdsdsdsd

RENTAL

10

261801

272673

22-Mar-13

10872

30404

dsf

59327

39855

41374

8

dsf

Delhi

North

446132

446132

IRC

CS

Mono

color

, I want some macro to convert this data to like below

in sheet 2 convert calculate Cv

	IR	IRC
Customer Name	Mono	Mono	Color	Total Color%

and in sheet 3 calculate amount

	IR	IRC			Total
Customer Name	Mono	Mono	Color	Color%

thanks
anu

Hello Anu,
Just to clarify.
You want to count the number of times that Mono and Color occur in column Counter for the categories IR and IRC in Column PG for each Customer Name and sum the values in Column CV.  This is to be on Sheet2.
On Sheet 3, you want the same data reproduced but to calculate an Amount.  Presumably this is the summation of the relevant values in the Amount column.  Is there any particular reason why it has to be on a separate sheet?
Regards
Harry

Hello again Anu,
This file possibly does what you want but using formulae
usha_5.zip
Regards
Harry

hi harry,

thanku so much it`s works fine but my data is very large so can you make some macro for the same as when i put the formula in whole file speed is very slow.

Thanks
anu

Hello Anu,
Another option is to use a pivot table.
You put CustomerName in the column and PG and Counter in the Row.  Sum of CV will give you the CV values and Sum of Amount will give you the Amount.  The percentages you would have to calculate outside the pivot table.
Try that and let me know how it goes in terms of speed.
Regards
Harry

hi harry,
 thank you for your support  harry  at present i am using pivot table itself  . Actually I need some way out when i put data in data sheet atomically calculate things

Regards
Usha

Hello Usha,
I assume that you want to change the range of data used for the pivot table as you add/delete more data.
If so,  there is an option to ChangeDataSource on the PivotTables toolbar under the Analyze Tab.  If you click on that it gives you a dialog box where you can change the range.  Slightly different if you are using early versions of Excel.
Another alternative is to specify a range bigger than your data set (more rows).  This will put an extra row for (blank) in your table but then just doing a Refresh will take into account the new data.  You can set an option to automatically refresh when the workbook is opened but as for the Manual Refresh, this only works on the specified data range.
It is possible to write a macro to do this for you but how it needs to work will depend on what data has to be selected.  For example, is it all of the data on the data page?  Or is it a subset?  If it is a subset, how is that determined?
Regards
Harry

hi harry ,

if i have put on data sheet only req, data like customer name , amount,Cv,Pg,Counter like

CUSTOMER NAME	AMOUNT	CV	PG	Counter
abc	2943.6	13380	IR	Mono
abc	8662.5	39375	IR	Mono
abc	194.7	885	IR	Mono
abc	38345.6	83360	IR	Mono
xyz	1201.06	2611	IR	Mono
xyz	5545.98	25209	IR	Mono
xyz	663.78	1443	IRC	Mono
xyz	110.5	17	IRC	Color
xyz	3750.34	17047	IRC	Mono

it`s any scop for macro .

regards
Usha

Hello Usha,
What I was saying, was that it would be possible to write a macro to change the range that the Pivot Table uses provided that we know what the data selection criteria are.  For instance select data between 1 May 2026 and 31 May 2026 or select all data on data page etc.
I can't see any point in writing a macro to just do what the built-in Pivot Table function already does.
Regards
Harry

Hello Usha,
This simple macro will select all of the data range on the Data worksheet and update the Pivot Tables.  However, if you have other Pivot Tables based on other data ranges in the WorkBook then this would not be the thing to use.  If you want only a subset of the data range then the Range parameters will have to be set to suit.

Sub ReRange()
Dim PC As PivotCache

For Each PC In ThisWorkbook.PivotCaches
    PC.SourceData = "Data!" & Worksheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Next PC

End Sub

Regards
Harry

hi , harry,

you are very helpful for me , thank you very much ....... ones again thank you sooooo much.

regards
Anu

hello  harry,

, i want to learn making macro. can you help me. or sugeest me how i can learn.

Regards
usha

Hello Usha,
If you just do a search for excel VBA tutorials you will get plenty of hits.  These sites will give you some basics.

http://excelvbatutor.com/vba_tutorial.html
http://chandoo.org/wp/excel-vba/

Good luck.
Harry

hi harry,

i hope you are fine,once again i need your help in http://chandoo.org/wp/excel-vba/examples/ i found one excel chart  example . and it `s fit to onr of my work but i can`t canges on it according to my data. can you help me out.

on-demand-details-in-excel-demo.zip.

now my data is like :

Calling.zip

can you convert my data to that file .

like when we select total summry it`s shows total rating .when Geeta it show geeta sheet chart, like ..

thanks
 anu

Hello Anu,
I'll have a look as soon as I get a chance.  I have a few thing on at the moment, it might take a day or so.
Harry

Hello Anu,
I have set up what I think you wanted.  Not exactly as in the example.  Click on the left column of the small table to the right to select the chart to display.  You might need to redo some of your conditional formatting on the chart.

reCalling.zip

This will adjust the size up to a certain limit but there are things that will need adjusting on the calculation page if your actual data content is much greater.
Regards
Harry

thank u harry ,

haary is this possible in chart if my value below 8 bar should in red colr  and if it`s 8 it should be  in yellow and if it`s >=9 it` green.

thans anu.

Hello Anu,

Calling.zip

Regards
Harry

Thank you sooooomuch , you are great........

thanks
anu