Käynnissä

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:

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

- 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 [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

Näytä lisää: vba macro vlookup, vlookup macro excel, vba add column copy formula, excel vlookup macro, vba sum items, vlookup excel vba, vba vlookup xls file, we excel, vba with, the p.i.c. group, premium assignment, p&a group, c.e. broker, bound table, b j's, excel vba add sheet copy data, create vlookup macro, excel vba copy sheet rename, excel vba filter different line, vba add line add line tab, select column copy excel vba macro, excel line draw vba, add lines vba, excel vba sum column filter, excel vba copy sheet sheet

Tietoa työnantajasta:
( 76 arvostelua ) Clifton, United States

Projektin tunnus: #402871