insert three columns each month and get values

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
465
Office Version
  1. 2019
hi
I have two sheets (report1,report2) they should match COL B,C,D with sheet REPORTS also COL B,C,D if the data are matched then should summing the values between sheets (report1,report2) if there are items are repeated and put the values after insert three columns in COLS ARRIVES, SALES but the col STOCK should calculate by formula when insert the columns . so every month I insert three columns then it should with same borders and formatting and the formulas last thing is more complicated if there are any items in COL B,C,D are existed in sheets(report1,report2) but not existed in sheet reports then should match based on COL A and add it to sheet reports as highlighted by red

report1
REPORT.xlsm
ABCDEF
1CTGBRANDTYPEORIGINarrive sale
2LVR1200R20G580JAP205
3LVR13R22.5R187JAP100
4LVR1400R20VSJJAP20
5PSR175/70R14EP150THI70
6PSR185/65R14EP150INDO120
7PSR185/65R15T005INDO30
8LSR2195R14C613VJAP50
report1


report2
REPORT.xlsm
ABCDEF
1CTGBRANDTYPEORIGINarrive sale
2LVR1200R20G580JAP134100
3LVR1200R24G580JAP4530
4LSR2155R12CR623JAP20
5LSR2155R12CR624INDO50
report2


reports
REPORT.xlsm
ABCDEFG
1JAN
2CategorybrandtypeoriginARRIVESALESTOCK
3PSR185/65R14TECTHI1212
4185/65R14EP150INDO-
5185/65R15TC10INDO-
6185/65R15T005INDO-
7185/65R15T01JAP-
8185/65R15B250JAP-
9195/60R15AR20INDO-
10195/60R15EP150THI1010
11195/60R15T001JAP-
12TTL22-22
13LSR1215/45R17GR90THI-
14215/45R17RE001JAP-
15215/45R17T001JAP-
16215/50R17EP300THI-
17215/55R17AR20INDO-
18215/55R17GR90INDO-
19215/55R17T001JAP-
20215/55R17T005JAP-
21TTL---
22LSR2650R16R2301082
23700R16R23012PR-
24750R16R230-
25155R12CR624INDO-
26155R12CR623JAP-
27165R13CR624INDO-
28185R14C613VJAP-
29185R14CR624INDO-
30195R14CR624INDO-
31195R14C613VJAP-
32195R14CR623THI-
33205R14C613VJAP-
34205R14CR624INDO-
35215R14CR624INDO1212
36TTL22814
37Dueler825R16R180JAP
38TTL---
391400R20R180JAP1313
4011R22.5R187JAP-
4112R22.5R187JAP-
4213R22.5R18718PR-
43275/70R22.5R294JAP-
44295/80R22.5M840JAP-
45315/80R22 JAPR18418PR-
46315/80R22.5R294JAP-
47315/80R22 THIR18418PR-
48315/80R22 R152JAP-
49LVR315/80R22 G580INDO-
50425/65R22.5R164INDO2020
51385/65R22.5R164THI-
52385/65R22.5R164JAP-
53445/65R22.5R164JAP-
54325/95R24G582JAP-
55TTL33-33
reports
Cell Formulas
RangeFormula
E12:G12E12=SUM(E3:E11)
E21:G21E21=SUM(E13:E20)
G39:G54,G22:G35,G13:G20,G3:G11G3=E3-F3
E36:G36E36=SUM(E22:E35)
E38:G38E38=SUM(E37:E37)
E55:G55E55=SUM(E39:E54)



when I run macro should insert three columns every time
REPORT.xlsm
ABCDEFGHIJ
1JANFEB
2CategorybrandtypeoriginARRIVESALESTOCKARRIVESALESTOCK
3PSR175/70R14EP150THI-7070
4185/65R14TECTHI121212
5185/65R14EP150INDO-120120
6185/65R15TC10INDO--
7185/65R15T005INDO-3030
8185/65R15T01JAP--
9185/65R15B250JAP--
10195/60R15AR20INDO--
11195/60R15EP150THI101010
12195/60R15T001JAP--
13TTL22-22220-242
14LSR1215/45R17GR90THI--
15215/45R17RE001JAP--
16215/45R17T001JAP--
17215/50R17EP300THI--
18215/55R17AR20INDO--
19215/55R17GR90INDO--
20215/55R17T001JAP--
21215/55R17T005JAP--
22TTL------
23Dueler650R16R23010822
24700R16R23012PR--
25750R16R230--
26155R12CR624INDO-5050
27155R12CR623JAP-2020
28165R13CR624INDO--
29185R14C613VJAP--
30185R14CR624INDO--
31195R14CR624INDO--
32195R14C613VJAP-5050
33195R14CR623THI--
34205R14C613VJAP--
35205R14CR624INDO--
36215R14CR624INDO121212
37TTL22814120-134
38825R16R180JAP------
39TTL------
40LVR1200R20G580JAP-15410549
411400R20R180JAP13132033
4211R22.5R187JAP--
4312R22.5R187JAP--
4413R22.5R187JAP-100100
451200R24G580JAP-453015
46275/70R22.5R294JAP--
47295/80R22.5M840JAP--
48315/80R22 JAPR18418PR--
49315/80R22.5R294JAP--
50315/80R22 THIR18418PR--
51315/80R22 R152JAP--
52315/80R22 G580INDO--
53425/65R22.5R164INDO202020
54385/65R22.5R164THI--
55385/65R22.5R164JAP--
56445/65R22.5R164JAP--
57325/95R24G582JAP--
58TTL33-33319135217
expected result
Cell Formulas
RangeFormula
E13:J13E13=SUM(E3:E12)
E22:J22E22=SUM(E14:E21)
E37:J37E37=SUM(E23:E36)
E39:J39E39=SUM(E38:E38)
G40:G57,G38,G23:G36,G14:G21,G3:G12G3=E3-F3
J40:J57,J38,J23:J36,J14:J21,J3:J12J3=G3+H3-I3
E58:J58E58=SUM(E40:E57)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

A
  • Solved
Match three columns with one column and pull numeric values
Replies
4
Views
297
abdo meghari
A
A
  • Question
insert three columns with empty cells and keeping formulas and borders
Replies
8
Views
391
abdo meghari
A
S
  • Question
Create Script Button to Add Selected Values to the Next empty Row in the sheet
Replies
1
Views
420
shaunreeder
S
A
  • Question
ignore zero value when copy the firs three column & the last three columns
Replies
0
Views
254
abdo meghari
A
J
  • Solved
Forumla Error on Sum of lines Containing same Value
Replies
2
Views
237
jarett
J

Forum statistics

Threads
1,215,455
Messages
6,124,937
Members
449,196
Latest member
Maxkapoor

Share this page

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