Macro to automate a daily job and save time!!

S

Shiwani

New Member
Joined
Oct 9, 2013
Messages
4
Can any one help me with this:
I have an excel file which needs to be updated daily from another file as per below; currently it is manual but surely there must be a macro to automate it. (Never written a macro, so this would be the first one)
Format
File 1: Lets say:
Product no Month Actual quantity Projected No1 Projected no2
1000 JUL 175 200 250
1000 AUG 200 200 350
1000 SEP 0 300 350
2000..........and so on

File 2
Product JUL AUG SEP Quarter total Committed

1000 175 200 300 675 Projected

LOGIC needed
For each product id, I need to get the number from file 1 for respective month, if actual exists. If actual for the month is zero, then I need to take projected value. Projected value should be the lowest of the two projected numbers (no1 and no2)
Further In case, If I have taken any of the projected number, column named "committed" here should be written as "Projected" and highlighted so that It can be manually reviewed.

Appreciate any help.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
upload your file on a hosting site then paste the link, you can not upload on this forum
 
Upvote 0
patel45 said:
upload your file on a hosting site then paste the link, you can not upload on this forum
Click to expand...

[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 531"]
<TBODY>[TR]
[TD]Product</SPAN>
[/TD]
[TD]Jul '13 </SPAN></SPAN>
[/TD]
[TD]Aug '13 </SPAN></SPAN>
[/TD]
[TD]Sep '13 </SPAN></SPAN>
[/TD]
[TD]Q2 13-14 </SPAN>
[/TD]
[TD]Certainty</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]97,586 </SPAN>
[/TD]
[TD]79,373 </SPAN>
[/TD]
[TD]94,840 </SPAN>
[/TD]
[TD]271,799 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]48,507 </SPAN>
[/TD]
[TD]45,947 </SPAN>
[/TD]
[TD]51,781 </SPAN>
[/TD]
[TD]146,235 </SPAN>
[/TD]
[TD]Unconfirmed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]3,097 </SPAN>
[/TD]
[TD][/TD]
[TD]7,373 </SPAN>
[/TD]
[TD]10,470 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]43,895 </SPAN>
[/TD]
[TD]41,335 </SPAN>
[/TD]
[TD]47,318 </SPAN>
[/TD]
[TD]132,548 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]45,499 </SPAN>
[/TD]
[TD]34,047 </SPAN>
[/TD]
[TD]44,956 </SPAN>
[/TD]
[TD]124,503 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 344"]
<TBODY>[TR]
[TD]Product</SPAN>
[/TD]
[TD]Month</SPAN>
[/TD]
[TD]Actual</SPAN>
[/TD]
[TD]Projected 1</SPAN>
[/TD]
[TD]Projected 2</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]43,895 </SPAN>
[/TD]
[TD]71,100 </SPAN>
[/TD]
[TD]86,160 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]41,335 </SPAN>
[/TD]
[TD]73,100 </SPAN>
[/TD]
[TD]97,360 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]47,318 </SPAN>
[/TD]
[TD]75,100 </SPAN>
[/TD]
[TD]108,560 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]3,097 </SPAN>
[/TD]
[TD]70,800 </SPAN>
[/TD]
[TD]84,480 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]7,373 </SPAN>
[/TD]
[TD]74,800 </SPAN>
[/TD]
[TD]106,880 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD][/TD]
[TD]48,507 </SPAN>
[/TD]
[TD]82,800 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD][/TD]
[TD]45,947 </SPAN>
[/TD]
[TD]94,000 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD][/TD]
[TD]105,200 </SPAN>
[/TD]
[TD]51,781 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]45,499 </SPAN>
[/TD]
[TD]71,400 </SPAN>
[/TD]
[TD]87,840 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]34,047 </SPAN>
[/TD]
[TD]73,400 </SPAN>
[/TD]
[TD]99,040 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]44,956 </SPAN>
[/TD]
[TD]75,400 </SPAN>
[/TD]
[TD]110,240 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]97,586 </SPAN>
[/TD]
[TD]70,000 </SPAN>
[/TD]
[TD]80,000 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]79,373 </SPAN>
[/TD]
[TD]72,000 </SPAN>
[/TD]
[TD]91,200 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]94,840 </SPAN>
[/TD]
[TD]74,000 </SPAN>
[/TD]
[TD]102,400 </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
Does this help???? Now you can see first file is prepared from second one. e.g. product id 2153309 has actuals and hence all actuals are taken, while 2204497 has projected numbers in second file and hence marked uncomitted, also for sep projected 2 is smaller than projected 1 and hence projected one is to be taken. Hope you can resolve my query.

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
patel45 said:
upload your file on a hosting site then paste the link, you can not upload on this forum
Click to expand...

[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 531"]
<TBODY>[TR]
[TD]Product</SPAN>
[/TD]
[TD]Jul '13 </SPAN></SPAN>
[/TD]
[TD]Aug '13 </SPAN></SPAN>
[/TD]
[TD]Sep '13 </SPAN></SPAN>
[/TD]
[TD]Q2 13-14 </SPAN>
[/TD]
[TD]Certainty</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]97,586 </SPAN>
[/TD]
[TD]79,373 </SPAN>
[/TD]
[TD]94,840 </SPAN>
[/TD]
[TD]271,799 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]48,507 </SPAN>
[/TD]
[TD]45,947 </SPAN>
[/TD]
[TD]51,781 </SPAN>
[/TD]
[TD]146,235 </SPAN>
[/TD]
[TD]Unconfirmed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]3,097 </SPAN>
[/TD]
[TD][/TD]
[TD]7,373 </SPAN>
[/TD]
[TD]10,470 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]43,895 </SPAN>
[/TD]
[TD]41,335 </SPAN>
[/TD]
[TD]47,318 </SPAN>
[/TD]
[TD]132,548 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]45,499 </SPAN>
[/TD]
[TD]34,047 </SPAN>
[/TD]
[TD]44,956 </SPAN>
[/TD]
[TD]124,503 </SPAN>
[/TD]
[TD]Committed</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 344"]
<TBODY>[TR]
[TD]Product</SPAN>
[/TD]
[TD]Month</SPAN>
[/TD]
[TD]Actual</SPAN>
[/TD]
[TD]Projected 1</SPAN>
[/TD]
[TD]Projected 2</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]43,895 </SPAN>
[/TD]
[TD]71,100 </SPAN>
[/TD]
[TD]86,160 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]41,335 </SPAN>
[/TD]
[TD]73,100 </SPAN>
[/TD]
[TD]97,360 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2153309</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]47,318 </SPAN>
[/TD]
[TD]75,100 </SPAN>
[/TD]
[TD]108,560 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]3,097 </SPAN>
[/TD]
[TD]70,800 </SPAN>
[/TD]
[TD]84,480 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2193811</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]7,373 </SPAN>
[/TD]
[TD]74,800 </SPAN>
[/TD]
[TD]106,880 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD][/TD]
[TD]48,507 </SPAN>
[/TD]
[TD]82,800 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD][/TD]
[TD]45,947 </SPAN>
[/TD]
[TD]94,000 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2204497</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD][/TD]
[TD]105,200 </SPAN>
[/TD]
[TD]51,781 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]45,499 </SPAN>
[/TD]
[TD]71,400 </SPAN>
[/TD]
[TD]87,840 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]34,047 </SPAN>
[/TD]
[TD]73,400 </SPAN>
[/TD]
[TD]99,040 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2208974</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]44,956 </SPAN>
[/TD]
[TD]75,400 </SPAN>
[/TD]
[TD]110,240 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]97,586 </SPAN>
[/TD]
[TD]70,000 </SPAN>
[/TD]
[TD]80,000 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]79,373 </SPAN>
[/TD]
[TD]72,000 </SPAN>
[/TD]
[TD]91,200 </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2505195</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]94,840 </SPAN>
[/TD]
[TD]74,000 </SPAN>
[/TD]
[TD]102,400 </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
Does this help???? Now you can see first file is prepared from second one. e.g. product id 2153309 has actuals and hence all actuals are taken, while 2204497 has projected numbers in second file and hence marked uncomitted, also for sep projected 2 is smaller than projected 1 and hence projected one is to be taken. Hope you can resolve my query.

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
I suppose data starting from A2 with header in the first row
Code: 
Sub a()
dcol = 7
drow = 2
c = 1
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To LR
    Cells(drow, dcol) = Cells(r, 1)
    mval = Cells(r, 3)
    If mval = "" Then mval = WorksheetFunction.Min(Cells(r, 4).Value, Cells(r, 5).Value)
    Cells(drow, dcol + c) = mval
    c = c + 1
    msum = msum + mval
    If c > 3 Then
      Cells(drow, dcol + c) = msum
      c = 1
      msum = 0
      drow = drow + 1
    End If
  Next
  drow = drow + 1
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
195
Peter_SSs
Peter_SSs
R
  • Question Question
Automate data pull from one file to another
Replies
1
Views
466
John_w
J
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
288
Tarkemelion
T
N
  • Question Question
Trying to cleanup many individual, macro-enabled workbooks with tables to rollup data with Power BI
Replies
0
Views
420
npasha1
N
M
  • Question Question
Index Match function List based on date
Replies
7
Views
271
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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