Project Detail

Description

I have similar project that was done just recently. macros, pivot tables, vlookups etc. if you are good with combaining spreadsheets with different data - you will be fine.

instructions:
Macro:
a) Open the file:
- BSS by Region 022009.xls

b) Do the following steps:
- add another sheet
- copy values only to new sheet
- add auto filter on line 9
- filter for nonblank’s in column A and copy the results to new added sheet
- column P line 9 add “Group #”
- column N line 9 add “ Region”
- column M line 10 add “1”
- column N line 10 add =A7 so ‘Carolina’ will appear in the cell
- column M line 11 should contain following formula: =IF((J11<>""),M10,M10+1)
- column N line 11 should contain following formula: =IF(M11=M10,N10,A10)
- run these formulas all the way down as long as the data is available for all lines
- add another sheet
- run pivot table on count of the client based on region so you would get something like this:
-

- Open the file “ BSS by Broker Name 022009.xls
- add another sheet
- copy values only to new sheet – name it MTD Data
- row S, line 9 – name it Group #
- run v lookup based on client name =VLOOKUP(A10,'[BSS by Region 022009.xls]Sheet2'!$A$10:$Q$1452,25,FALSE) so region will be filled.
- Column T, line 9 – name it Broker Name and Region
- Concave Broker name and region in column T beginning in line 10
- Add broker ID in column U line 9
- Do vlookup for broker ID in column U based on Broker Name and Region from column T and data from Broker Stats 022009.xls – spreadsheet from first assignment
- =VLOOKUP(T10,'[Broker Stats 022009.xls]Sheet2'!$P$10:$Q$1155,2,FALSE)
- Here is what we should get
-


- In new added sheet and named BBS Qouted Accounts run pivot table on broker ID and count of brokers. Output as below:
-

- add new sheet and rename it “BBS Quoted Lines and Premium”

- run pivot table on broker ID and “Sum of # Lines Quoted” and “Sum of Premium” columns M and N from MTD Data tab

- In column E create a table that will contain following items

- Column E – Broker ID, column F – Sum of # of Lines Quoted, column G – Sum of premium --- these taken from Pivot table just created.

- In colum H create Bound Lines, column I – Bound Premium, J Line Hit Ratio, K, Premium Hit Ratio

- Column H will be filled up with VLook up function based on broker ID from column E and spreadsheet “Bound Prem MTD 2009.xls”

- =VLOOKUP($E5,[Bound Prem MTD 2009.xls]Bound Lines and Premium'!$B$6:$D$613,2,FALSE) --- to get Bound Lines

- =VLOOKUP($E5,[Bound Prem MTD 2009.xls]Bound Lines and Premium'!$B$6:$D$613,3,FALSE) --- to get Bound Premium

- Column J is simple H/F

- Column K is simple I/G

- Save it.

- Outlook should look like this:


Additional information submitted:

03/16/2009 at 13:46 EDT:
picture number 1

03/16/2009 at 13:46 EDT:
picture number 2

03/16/2009 at 13:47 EDT:
picture number 3

03/16/2009 at 13:47 EDT:
picture number 4

Additional files submitted:
pic02.jpg
pic03.jpg
pic04.jpg
pic05.jpg