HI,
Thank you in advance to anyone who can help me with this. I need a macro that will help me comparing over 100 statements month over, month (CM= Current Month) against the one received the prior month (PM= Prior Month). It should end with 3 or 4 ws's. (1. CM, 2. PM, 3. Variances and, 4. MOM). For the ws CM, This is populated with data from the file selected in a selection prompt. The data to copy is from ws named "Normalized Format" and pasted into this workbook on ws CM. PM would be populated similar to ws CM but pated into ws PM.
Here is the tricky part, or at least I think it is, I need the ws "Variances" to be populated in 1 section and ws MOM to have 2 sections. The ws Variances is based on the column header put in cell B1 and D1, it should go to both tabs and copy the complete row from each ws where the combination of those 2 column headers (B1 & D1) don't exist on one of the 2 worksheets and paste it on the variances ws beginning in row 4 with a cell in front identifying what ws it cam from, CM or PM. Sometimes the combination of column headers can change depending the statement. The second would on wa MOM and would copy the list in the column that matches the one listed in A1. This copied list from that column (A1) and would have duplicates removed, then it would be pasted beginning in cell A3. The data in columns B:M, next to each name in A, would update based on the formula locked in. Except for the 4 areas titled as " # of Accounts for CM" or "# of Accounts for PM'. I am struggling with this one for some reason. It should return the # of Account #'s or CTN's in this case, found if the value in col A is matched in that column from the CM or PM WS. Ex: "NO LIMIT Speed" is found on CM ws with only 1 distinct account # but on the PM ws, it has 2. The second piece would be similar to the The previous piece but with a different column header, listed in A30 or whatever row it ends up being. Pieces on ws MOM can certainly be placed on the Variance tab somehow if it's possible.
Mcontr01
Thank you in advance to anyone who can help me with this. I need a macro that will help me comparing over 100 statements month over, month (CM= Current Month) against the one received the prior month (PM= Prior Month). It should end with 3 or 4 ws's. (1. CM, 2. PM, 3. Variances and, 4. MOM). For the ws CM, This is populated with data from the file selected in a selection prompt. The data to copy is from ws named "Normalized Format" and pasted into this workbook on ws CM. PM would be populated similar to ws CM but pated into ws PM.
Here is the tricky part, or at least I think it is, I need the ws "Variances" to be populated in 1 section and ws MOM to have 2 sections. The ws Variances is based on the column header put in cell B1 and D1, it should go to both tabs and copy the complete row from each ws where the combination of those 2 column headers (B1 & D1) don't exist on one of the 2 worksheets and paste it on the variances ws beginning in row 4 with a cell in front identifying what ws it cam from, CM or PM. Sometimes the combination of column headers can change depending the statement. The second would on wa MOM and would copy the list in the column that matches the one listed in A1. This copied list from that column (A1) and would have duplicates removed, then it would be pasted beginning in cell A3. The data in columns B:M, next to each name in A, would update based on the formula locked in. Except for the 4 areas titled as " # of Accounts for CM" or "# of Accounts for PM'. I am struggling with this one for some reason. It should return the # of Account #'s or CTN's in this case, found if the value in col A is matched in that column from the CM or PM WS. Ex: "NO LIMIT Speed" is found on CM ws with only 1 distinct account # but on the PM ws, it has 2. The second piece would be similar to the The previous piece but with a different column header, listed in A30 or whatever row it ends up being. Pieces on ws MOM can certainly be placed on the Variance tab somehow if it's possible.
Mcontr01
| ABCDE_STN_Var_12.20_01.21.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | F | H | I | J | K | L | M | N | O | P | ||||||||
| 1 | CYCLE | STMT_PROVIDER_NAME | PROVIDER_CUSTOMER_NAME | PROVIDER_STM_DATE | INVOICE_DATE | SERVICE_DATE | CUSTOMER_PAYMENT_DATE | ACCOUNT_NUM | BTN | ORDER_NUMBER | CIRCUIT_ID | |||||||
| 2 | 2020-12 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830008 | #N/A | 2.173E+09 | ||||||||||
| 3 | 2020-12 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830008 | #N/A | 2.173E+09 | ||||||||||
| 4 | 2020-12 | Comp B | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830008 | #N/A | 2.173E+09 | ||||||||||
| 5 | 2020-12 | Comp C | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830008 | #N/A | 2.173E+09 | ||||||||||
| 6 | 2020-12 | Comp C | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT | CM | |||||||||||
| 7 | 2020-12 | Comp A | Sand SOLUTIONS | 2020-11 | 2020-11 | Sand SOLUTIONS2034286412 | #N/A | 2.034E+09 | ||||||||||
| 8 | 2020-12 | Comp A | Sand SOL | 2020-11 | 2020-11 | Sand SOL2034286412 | CM | 2.034E+09 | ||||||||||
| 9 | 2020-12 | Comp A | Sand SOLUTIONS | 2020-11 | 2020-11 | Sand SOLUTIONS2034286412 | #N/A | 2.034E+09 | ||||||||||
| 10 | 2020-12 | Comp C | Crow INC | 2020-11 | 2020-11 | Crow INC2177287271 | #N/A | 2.177E+09 | ||||||||||
| 11 | 2020-12 | Comp C | Crow INC | 2020-11 | 2020-11 | Crow INC2177287271 | #N/A | 2.177E+09 | ||||||||||
| 12 | 2020-12 | Comp A | Family HANSON | 2020-11 | 2020-11 | Family HANSON | CM | |||||||||||
| 13 | 2020-12 | Comp A | Family HANSON | 2020-11 | 2020-11 | Family HANSON2604784732 | #N/A | 2.605E+09 | ||||||||||
| 14 | 2020-12 | Comp A | Family HANSON | 2020-11 | 2020-11 | Family HANSON2604787773 | CM | 2.605E+09 | ||||||||||
| 15 | 2020-12 | Comp A | VENTURES | 2020-11 | 2020-11 | VENTURES2036371042 | #N/A | 2.036E+09 | ||||||||||
| 16 | 2020-12 | Comp A | VENTURES | 2020-11 | 2020-11 | VENTURES2036371042 | #N/A | 2.036E+09 | ||||||||||
| 17 | 2020-12 | Comp A | BLUE RID | 2020-11 | 2020-11 | BLUE RID2604837837 | #N/A | 2.605E+09 | ||||||||||
| 18 | 2020-12 | Comp A | BLUE RID | 2020-11 | 2020-11 | BLUE RID2604837837 | #N/A | 2.605E+09 | ||||||||||
| 19 | 2020-12 | Comp B | BLUE RID | 2020-11 | 2020-11 | BLUE RID2604837837 | #N/A | 2.605E+09 | ||||||||||
| 20 | 2020-12 | Comp B | LIVE YOUR Life | 2020-11 | 2020-11 | 2.038E+09 | ||||||||||||
CM | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L15:L19,L13,L9:L11,L7,L2:L5 | L2 | =VLOOKUP(K2,PM!K:K,1,FALSE) |
| K2:K19 | K2 | =CONCATENATE(H2,N2) |
| ABCDE_STN_Var_12.20_01.21.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | F | H | I | J | M | N | O | ||||||||||
| 1 | CYCLE | STMT_PROVIDER_NAME | PROVIDER_CUSTOMER_NAME | PROVIDER_STM_DATE | INVOICE_DATE | ACCOUNT_NUM | BTN | ORDER_NUMBER | |||||||||
| 2 | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-10 | 2020-10 | 2172830008 | |||||||||||
| 3 | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-10 | 2020-10 | 2172830008 | |||||||||||
| 4 | 2020-11 | Comp B | Lolipop CREDIT ACCEPTANCE LLC | 2020-10 | 2020-10 | 7723732388 | |||||||||||
| 5 | 2020-11 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | 7776800007 | |||||||||||
| 6 | 2020-11 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | 7776800007 | |||||||||||
| 7 | 2020-11 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | 7772120220 | |||||||||||
| 8 | 2020-11 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | 7772120220 | |||||||||||
| 9 | 2020-11 | Comp A | NO LIMIT Speeds | 2020-10 | 2020-10 | 7776800007 | |||||||||||
| 10 | 2020-11 | Comp C | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | 7.7727E+10 | |||||||||||
| 11 | 2020-11 | Comp C | No Tallant | 2020-09 | 2020-08 | 232969 | 001236-20033 | ||||||||||
| 12 | 2020-11 | Comp A | No Tallant | 2020-09 | 2020-08 | 639305 | 001236-20033 | ||||||||||
| 13 | 2020-11 | Comp A | Wings | 2020-09 | 2020-08 | 723314 | LW10387-11078 | ||||||||||
| 14 | 2020-11 | Comp B | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | 2172830008 | |||||||||||
| 15 | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | 2172830008 | |||||||||||
| 16 | 2020-11 | Comp F | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | 2172830008 | |||||||||||
| 17 | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | 2172830008 | |||||||||||
| 18 | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | 2172830007 | |||||||||||
| 19 | 2020-11 | Comp B | Sand SOLUTIONS | 2020-11 | 2020-11 | 2034286412 | |||||||||||
PM | |||||||||||||||||
| ABCDE_STN_Var_12.20_01.21.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Differences between | PROVIDER_CUSTOMER_NAME | and | CTN | ||||||||||
| 2 | ||||||||||||||
| 3 | From WS | CYCLE | AGENT_PARENT_ID | LEGACY_AGENT_ID | SALES_REP | PROVIDER_NAME | STMT_PROVIDER_NAME | CUSTOMER | PROVIDER_CUSTOMER_NAME | PROVIDER_STM_DATE | INVOICE_DATE | SERVICE_DATE | ||
| 4 | CM | 2020-12 | Comp C | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT | |||||||
| 5 | CM | 2020-12 | Comp A | Sand SOL | 2020-11 | 2020-11 | Sand SOL2034286412 | |||||||
| 6 | CM | 2020-12 | Comp A | Family HANSON | 2020-11 | 2020-11 | Family HANSON | |||||||
| 7 | CM | 2020-12 | Comp A | Family HANSON | 2020-11 | 2020-11 | Family HANSON2604787773 | |||||||
| 8 | CM | 2020-12 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830009 | |||||||
| 9 | CM | 2020-12 | Comp A | Lolipop CREDIT ACCEPTANCE LLC | 2020-10 | 2020-10 | Lolipop CREDIT ACCEPTANCE LLC9723932388 | |||||||
| 10 | CM | 2020-12 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | NO LIMIT Speed9796800009 | |||||||
| 11 | CM | 2020-12 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | NO LIMIT Speed9796800009 | |||||||
| 12 | CM | 2020-12 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | INDEPENDENCE We Stand CHEMICALS9792120550 | |||||||
| 13 | CM | 2020-12 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | INDEPENDENCE We Stand CHEMICALS9792120550 | |||||||
| 14 | PM | 2020-11 | Comp B | Lolipop CREDIT ACCEPTANCE LLC | 2020-10 | 2020-10 | Lolipop CREDIT ACCEPTANCE LLC7723732388 | |||||||
| 15 | PM | 2020-11 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | NO LIMIT Speed7776800007 | |||||||
| 16 | PM | 2020-11 | Comp C | NO LIMIT Speed | 2020-10 | 2020-10 | NO LIMIT Speed7776800007 | |||||||
| 17 | PM | 2020-11 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | INDEPENDENCE We Stand CHEMICALS7772120220 | |||||||
| 18 | PM | 2020-11 | Comp A | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | INDEPENDENCE We Stand CHEMICALS7772120220 | |||||||
| 19 | PM | 2020-11 | Comp A | NO LIMIT Speeds | 2020-10 | 2020-10 | NO LIMIT Speeds7776800007 | |||||||
| 20 | PM | 2020-11 | Comp C | INDEPENDENCE We Stand CHEMICALS | 2020-10 | 2020-10 | INDEPENDENCE We Stand CHEMICALS77727230220 | |||||||
| 21 | PM | 2020-11 | Comp A | COMMUNITY Crush INSTITUT | 2020-11 | 2020-11 | COMMUNITY Crush INSTITUT2172830007 | |||||||
| 22 | PM | 2020-11 | Comp A | Provo Craft | 2020-08 | 2020-07 | Provo Craft | |||||||
| 23 | PM | 2020-11 | Comp A | Cemex USA | 2020-08 | 2020-05 | Cemex USA | |||||||
Variance | ||||||||||||||
| ABCDE_STN_Var_12.20_01.21.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | PROVIDER_CUSTOMER_NAME | |||||||||||
| 2 | Names | # of lines CM | # of Accounts CM | REVENUE CM | REPORT_COMP PM | # of lines PM | # of Accounts PM | Revenue PM | REPORT_COMP PM | # of lines % Change MOM | ||
| 3 | BLUE RID | 6 | 0 | 139.98 | $ 28.00 | 0 | 0 | $ 1,500.00 | $ 225.00 | #DIV/0! | ||
| 4 | Cemex USA | 3 | 2 | 0 | $ - | |||||||
| 5 | COMMUNITY Crush INSTITUT | 3 | 2 | 227.36 | $ 45.49 | |||||||
| 6 | Credit Direct | 3 | 2 | 0 | $ - | |||||||
| 7 | Crow INC | 3 | 2 | 99.98 | $ 20.00 | |||||||
| 8 | Cusion Management Group, LLC | 3 | 2 | 600 | $ 90.00 | |||||||
| 9 | Family HANSON | 3 | 2 | 133.97 | $ 26.80 | |||||||
| 10 | Hampton Products | 3 | 2 | 0 | $ - | |||||||
| 11 | HANSON | 3 | 2 | 40.99 | $ 8.20 | |||||||
| 12 | INDEPENDENCE We Stand CHEMICALS | 3 | 2 | 69.99 | $ 14.00 | |||||||
| 13 | LIVE YOUR Life | 3 | 2 | 92.68 | $ 18.54 | |||||||
| 14 | LMG Holdings | 3 | 2 | 0 | $ - | |||||||
| 15 | Lolipop CREDIT ACCEPTANCE LLC | 3 | 2 | 10 | $ 2.00 | |||||||
| 16 | NO LIMIT Speed | 3 | 2 | 35.44 | $ 7.09 | |||||||
| 17 | NO LIMIT Speeds | 3 | 2 | 0 | $ - | |||||||
| 18 | No Tallant | 3 | 2 | 607 | $ 121.40 | |||||||
| 19 | PROVIDER_CUSTOMER_NAME | 3 | 2 | 0 | $ - | |||||||
| 20 | Provo Craft | 3 | 2 | 0 | $ - | |||||||
| 21 | Sand SOL | 3 | 2 | 20 | $ 4.00 | |||||||
| 22 | Sand SOLUTIONS | 3 | 2 | 369.98 | $ 74.00 | |||||||
| 23 | VENTURES | 3 | 2 | 69.98 | $ 14.00 | |||||||
| 24 | Wings | 3 | 2 | 174 | $ 34.80 | 5 | 1 | $ 435.00 | $ 65.25 | -40% | ||
| 25 | ||||||||||||
| 26 | ||||||||||||
| 27 | ||||||||||||
| 28 | ||||||||||||
| 29 | ||||||||||||
| 30 | STMT_PROVIDER_NAME | |||||||||||
| 31 | Names | # of lines CM | # of Accounts CM | REPORT_COMP CM | # of lines PM | # of Accounts PM | Revenue PM | Comm PM | # of lines % Change MOM | |||
| 32 | BLUE RID | 3 | 1 | #REF! | 1 | 1 | $ 100.00 | $ 15.00 | 200% | |||
| 33 | Cemex USA | |||||||||||
| 34 | COMMUNITY Crush INSTITUT | |||||||||||
MOM | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B3 | B3 | =COUNTIF(CM!$H:$H,MOM!A3) |
| C3 | C3 | =COUNTIF(CM!$T:$T,MOM!A3) |
| D3:D24 | D3 | =SUMIF(CM!$H:$H,MOM!A3,CM!$AI:$AI) |
| E3:E24 | E3 | =SUMIF(CM!$H:$H,MOM!A3,CM!$AS:$AS) |
| F3 | F3 | =COUNTIF(PM!$T:$T,MOM!A3) |
| G3 | G3 | =COUNTIF(PM!$T:$T,MOM!A3) |
| I3,I32,I24 | I3 | =H3*0.15 |
| J3,J32,J24 | J3 | =(B3-F3)/F3 |
| E32 | E32 | =#REF!^0.15 |
