PDA

View Full Version : How to analyze and sort data using VBA



pominoz
05-30-2012, 04:32 PM
Hi All,

I am completely new to this site and very new to the idea of VBA.

I am hoping that some of the experts here may be able to help me out.

I have a large amount of data, original data worksheet contains around 11000 rows of data (I have attached a spreadsheet containing 100 rows of this data)

Currently I manually analyze this data (using pivot tables) to create a table which I can then calculate lower/upper qaurtile and median salary + percentages of benfits etc (all of this is included as an example in my spreadsheet. From the data table that I create I then create individual worksheets of each role and lay it out ready for export into a pdf file.

Basically I need a sheet for each role that contains the data shown on each role worksheet in my example.

Some of the difficulties are brought about by the fact that the original data often needs to be correctly formatted before it can be analyzed and contains numbers in text format etc.

Please see attachment and let me know if anyone might be able to help.

Thanks a lot

pominoz

BrianMH
05-31-2012, 12:29 AM
This isn't an overall solution but I note you talk about having to reformat numbers stored as text and such. I just wanted to point you to a kb article I submitted after having to do something very similar quite often. It can take quite a while if the numbers stored as text have any spaces on them as you need to click into each cell. This makes all those conversions much faster and it includes changing to dates, currency, etc.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1118

pominoz
06-06-2012, 04:12 PM
Hi Brian,

Thnaks for your suggestion and code.

I have tried to use it but it doesn't seem to deal with some of the data in my original spreadsheet.

Also, I am looking for a way to format the data correctly, then automatically create a worksheet for each role, providing the data as per the example spreadsheet.

I will continue to look.

Thanks for your help.

pominoz

Paul_Hossler
06-07-2012, 10:28 AM
Formating the data ...


Option Explicit
Sub FixData()
With Worksheets("Original Data").Range("A1").CurrentRegion
.Replace What:="/hr", Replacement:=vbNullString, LookAt:=xlPart
.Replace What:="$", Replacement:=vbNullString, LookAt:=xlPart
.NumberFormat = "General"
.HorizontalAlignment = xlGeneral
End With
End Sub


Don't understand the second requirement

Paul