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 [login to view URL]
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 [login to view URL]
- 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 [login to view URL]]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 [login to view URL] – spreadsheet from first assignment
- =VLOOKUP(T10,'[Broker Stats [login to view URL]]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 [login to view URL]”
- =VLOOKUP($E5,[Bound Prem MTD [login to view URL]]Bound Lines and Premium'!$B$6:$D$613,2,FALSE) --- to get Bound Lines
- =VLOOKUP($E5,[Bound Prem MTD [login to view URL]]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: