Comparing monthly data from separate workbooks

mcontr01

New Member
Joined
Jun 30, 2016
Messages
3
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

ABCDE_STN_Var_12.20_01.21.xlsx
AFHIJKLMNOP
1CYCLESTMT_PROVIDER_NAMEPROVIDER_CUSTOMER_NAMEPROVIDER_STM_DATEINVOICE_DATESERVICE_DATECUSTOMER_PAYMENT_DATEACCOUNT_NUMBTNORDER_NUMBERCIRCUIT_ID
22020-12Comp ACOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUT2172830008#N/A2.173E+09
32020-12Comp ACOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUT2172830008#N/A2.173E+09
42020-12Comp BCOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUT2172830008#N/A2.173E+09
52020-12Comp CCOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUT2172830008#N/A2.173E+09
62020-12Comp CCOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUTCM
72020-12Comp ASand SOLUTIONS 2020-11 2020-11Sand SOLUTIONS2034286412#N/A2.034E+09
82020-12Comp ASand SOL 2020-11 2020-11Sand SOL2034286412CM2.034E+09
92020-12Comp ASand SOLUTIONS 2020-11 2020-11Sand SOLUTIONS2034286412#N/A2.034E+09
102020-12Comp CCrow INC 2020-11 2020-11Crow INC2177287271#N/A2.177E+09
112020-12Comp CCrow INC 2020-11 2020-11Crow INC2177287271#N/A2.177E+09
122020-12Comp AFamily HANSON 2020-11 2020-11Family HANSONCM
132020-12Comp AFamily HANSON 2020-11 2020-11Family HANSON2604784732#N/A2.605E+09
142020-12Comp AFamily HANSON 2020-11 2020-11Family HANSON2604787773CM2.605E+09
152020-12Comp AVENTURES 2020-11 2020-11VENTURES2036371042#N/A2.036E+09
162020-12Comp AVENTURES 2020-11 2020-11VENTURES2036371042#N/A2.036E+09
172020-12Comp ABLUE RID 2020-11 2020-11BLUE RID2604837837#N/A2.605E+09
182020-12Comp ABLUE RID 2020-11 2020-11BLUE RID2604837837#N/A2.605E+09
192020-12Comp BBLUE RID 2020-11 2020-11BLUE RID2604837837#N/A2.605E+09
202020-12Comp BLIVE YOUR Life 2020-11 2020-112.038E+09
CM
Cell Formulas
RangeFormula
L15:L19,L13,L9:L11,L7,L2:L5L2=VLOOKUP(K2,PM!K:K,1,FALSE)
K2:K19K2=CONCATENATE(H2,N2)
ABCDE_STN_Var_12.20_01.21.xlsx
AFHIJMNO
1CYCLESTMT_PROVIDER_NAMEPROVIDER_CUSTOMER_NAMEPROVIDER_STM_DATEINVOICE_DATEACCOUNT_NUMBTNORDER_NUMBER
22020-11Comp ACOMMUNITY Crush INSTITUT 2020-10 2020-102172830008
32020-11Comp ACOMMUNITY Crush INSTITUT 2020-10 2020-102172830008
42020-11Comp BLolipop CREDIT ACCEPTANCE LLC 2020-10 2020-107723732388
52020-11Comp CNO LIMIT Speed 2020-10 2020-107776800007
62020-11Comp CNO LIMIT Speed 2020-10 2020-107776800007
72020-11Comp AINDEPENDENCE We Stand CHEMICALS 2020-10 2020-107772120220
82020-11Comp AINDEPENDENCE We Stand CHEMICALS 2020-10 2020-107772120220
92020-11Comp ANO LIMIT Speeds 2020-10 2020-107776800007
102020-11Comp CINDEPENDENCE We Stand CHEMICALS 2020-10 2020-107.7727E+10
112020-11Comp CNo Tallant 2020-09 2020-08232969001236-20033
122020-11Comp ANo Tallant 2020-09 2020-08639305001236-20033
132020-11Comp AWings2020-092020-08723314LW10387-11078
142020-11Comp BCOMMUNITY Crush INSTITUT2020-112020-112172830008
152020-11Comp ACOMMUNITY Crush INSTITUT2020-112020-112172830008
162020-11Comp FCOMMUNITY Crush INSTITUT2020-112020-112172830008
172020-11Comp ACOMMUNITY Crush INSTITUT2020-112020-112172830008
182020-11Comp ACOMMUNITY Crush INSTITUT2020-112020-112172830007
192020-11Comp BSand SOLUTIONS2020-112020-112034286412
PM
ABCDE_STN_Var_12.20_01.21.xlsx
ABCDEFGHIJKL
1Differences betweenPROVIDER_CUSTOMER_NAMEandCTN
2
3From WSCYCLEAGENT_PARENT_IDLEGACY_AGENT_IDSALES_REPPROVIDER_NAMESTMT_PROVIDER_NAMECUSTOMERPROVIDER_CUSTOMER_NAMEPROVIDER_STM_DATEINVOICE_DATESERVICE_DATE
4CM2020-12Comp CCOMMUNITY Crush INSTITUT 2020-11 2020-11COMMUNITY Crush INSTITUT
5CM2020-12Comp ASand SOL 2020-11 2020-11Sand SOL2034286412
6CM2020-12Comp AFamily HANSON 2020-11 2020-11Family HANSON
7CM2020-12Comp AFamily HANSON 2020-11 2020-11Family HANSON2604787773
8CM2020-12Comp ACOMMUNITY Crush INSTITUT2020-112020-11COMMUNITY Crush INSTITUT2172830009
9CM2020-12Comp ALolipop CREDIT ACCEPTANCE LLC2020-102020-10Lolipop CREDIT ACCEPTANCE LLC9723932388
10CM2020-12Comp CNO LIMIT Speed2020-102020-10NO LIMIT Speed9796800009
11CM2020-12Comp CNO LIMIT Speed2020-102020-10NO LIMIT Speed9796800009
12CM2020-12Comp AINDEPENDENCE We Stand CHEMICALS2020-102020-10INDEPENDENCE We Stand CHEMICALS9792120550
13CM2020-12Comp AINDEPENDENCE We Stand CHEMICALS2020-102020-10INDEPENDENCE We Stand CHEMICALS9792120550
14PM2020-11Comp BLolipop CREDIT ACCEPTANCE LLC 2020-10 2020-10Lolipop CREDIT ACCEPTANCE LLC7723732388
15PM2020-11Comp CNO LIMIT Speed 2020-10 2020-10NO LIMIT Speed7776800007
16PM2020-11Comp CNO LIMIT Speed 2020-10 2020-10NO LIMIT Speed7776800007
17PM2020-11Comp AINDEPENDENCE We Stand CHEMICALS 2020-10 2020-10INDEPENDENCE We Stand CHEMICALS7772120220
18PM2020-11Comp AINDEPENDENCE We Stand CHEMICALS 2020-10 2020-10INDEPENDENCE We Stand CHEMICALS7772120220
19PM2020-11Comp ANO LIMIT Speeds 2020-10 2020-10NO LIMIT Speeds7776800007
20PM2020-11Comp CINDEPENDENCE We Stand CHEMICALS 2020-10 2020-10INDEPENDENCE We Stand CHEMICALS77727230220
21PM2020-11Comp ACOMMUNITY Crush INSTITUT2020-112020-11COMMUNITY Crush INSTITUT2172830007
22PM2020-11Comp AProvo Craft2020-082020-07Provo Craft
23PM2020-11Comp ACemex USA2020-082020-05Cemex USA
Variance
ABCDE_STN_Var_12.20_01.21.xlsx
ABCDEFGHIJ
1PROVIDER_CUSTOMER_NAME
2Names # of lines CM# of Accounts CMREVENUE CMREPORT_COMP PM# of lines PM# of Accounts PMRevenue PMREPORT_COMP PM# of lines % Change MOM
3BLUE RID60139.98$ 28.0000$ 1,500.00$ 225.00#DIV/0!
4Cemex USA320$ -
5COMMUNITY Crush INSTITUT32227.36$ 45.49
6Credit Direct320$ -
7Crow INC3299.98$ 20.00
8Cusion Management Group, LLC32600$ 90.00
9Family HANSON32133.97$ 26.80
10Hampton Products320$ -
11HANSON3240.99$ 8.20
12INDEPENDENCE We Stand CHEMICALS3269.99$ 14.00
13LIVE YOUR Life3292.68$ 18.54
14LMG Holdings320$ -
15Lolipop CREDIT ACCEPTANCE LLC3210$ 2.00
16NO LIMIT Speed3235.44$ 7.09
17NO LIMIT Speeds320$ -
18No Tallant32607$ 121.40
19PROVIDER_CUSTOMER_NAME320$ -
20Provo Craft320$ -
21Sand SOL3220$ 4.00
22Sand SOLUTIONS32369.98$ 74.00
23VENTURES3269.98$ 14.00
24Wings32174$ 34.8051$ 435.00$ 65.25-40%
25
26
27
28
29
30STMT_PROVIDER_NAME
31Names # of lines CM# of Accounts CMREPORT_COMP CM# of lines PM# of Accounts PMRevenue PMComm PM# of lines % Change MOM
32BLUE RID31#REF!11$ 100.00$ 15.00200%
33Cemex USA
34COMMUNITY Crush INSTITUT
MOM
Cell Formulas
RangeFormula
B3B3=COUNTIF(CM!$H:$H,MOM!A3)
C3C3=COUNTIF(CM!$T:$T,MOM!A3)
D3:D24D3=SUMIF(CM!$H:$H,MOM!A3,CM!$AI:$AI)
E3:E24E3=SUMIF(CM!$H:$H,MOM!A3,CM!$AS:$AS)
F3F3=COUNTIF(PM!$T:$T,MOM!A3)
G3G3=COUNTIF(PM!$T:$T,MOM!A3)
I3,I32,I24I3=H3*0.15
J3,J32,J24J3=(B3-F3)/F3
E32E32=#REF!^0.15
 

Attachments

  • Worksheets .png
    Worksheets .png
    3.7 KB · Views: 10

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top