1. Pull renewal data for quarter – “Distributor VAR In-Qtr Renewal Rate (xyz)”

  1. Remove rows 1-13
  2. Remove all lines tied to Distributor State: Florida (Column G)
  3. Add Remaining Opp Column to standard amounts and adjust amounts

 

 

  1. Remove all lines with zero in DT (Remaining Opp) and DW (Remaining Adj Amt)
  2. Should be left with just “not renewed” in columns DM and DY
  3. Make sure to keep remaining columns that were added
  4. Remove the following: DZ-EI, DX, DL, DK, ETC….(You must do it in correct order)

DZ-EI

BI-BL

AH-AI

DX

BF-BG

U-V

DL

BD

A-I

DK

AY

 

CU-DI

AV

 

CC-CT

AS-AT

 

BV-CB

AQ-AR

 

BO-BS

AJ-AO

 

  1. Add “Customer Type”, “Sales Terr #”, “Sales Terr Name”, and “Master Cust #” to end columns.  Should be columns AX-BA
  2. Use mapping file for VLOOKUPS to fill in columns in step 9

 

Finished product is attached