# VBA, Macro, Excel

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 [url removed, login to view]

b) Do the following steps:

- 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&lt;&gt;&quot;&quot;),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

- 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 [url removed, login to view]

- 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 [url removed, login to view]]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 [url removed, login to view] – spreadsheet from first assignment

- =VLOOKUP(T10,'[Broker Stats [url removed, login to view]]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 [url removed, login to view]”

- =VLOOKUP(\$E5,[Bound Prem MTD [url removed, login to view]]Bound Lines and Premium'!\$B\$6:\$D\$613,2,FALSE) --- to get Bound Lines

- =VLOOKUP(\$E5,[Bound Prem MTD [url removed, login to view]]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:

picture number 1

picture number 2

picture number 3

picture number 4

Taidot: Excel, Visual Basic