VBA - insert column and vlookup data

I

iris1007

New Member
Joined
Jun 28, 2017
Messages
31
Hi,
I have a huge data and multiple worksheets and need to consolidate into 1 "Summary" worksheet. Below are the steps I am doing..

1. add new Column in "Summary" - "July-17"　

2. vlookup new number for the new month from ALL the worksheets　

3. If there is new customer in any worksheet, add new Row in "Summary" - "K" & "L"　

4. change the formula for U1, V1, W1, X1 - to capture the correct last 3 / 6 / 9 / 12 months data


Current Summary:
[TABLE="width: 1312"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]CODE[/TD]
[TD]Jan-16[/TD]
[TD]Feb-16[/TD]
[TD]Mar-16[/TD]
[TD]Apr-16[/TD]
[TD]May-16[/TD]
[TD]Jun-16[/TD]
[TD]Jul-16[/TD]
[TD]Aug-16[/TD]
[TD]Sep-16[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]LAST 3 MTHS[/TD]
[TD]LAST 6 MTHS[/TD]
[TD]LAST 9 MTHS[/TD]
[TD]LAST 1 year[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]310[/TD]
[TD]125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]251[/TD]
[TD]751[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]145[/TD]
[TD]254[/TD]
[TD]124[/TD]
[TD]435.00[/TD]
[TD]575.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]245.00[/TD]
[TD]245.00[/TD]
[TD]1255.00[/TD]
[TD]1778[/TD]
[TD]2529[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]110.00[/TD]
[TD]412.00[/TD]
[TD]470.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]328.00[/TD]
[TD]287.00[/TD]
[TD]336.00[/TD]
[TD]320.00[/TD]
[TD]145.00[/TD]
[TD] [/TD]
[TD]214.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]655.53[/TD]
[TD]869.53[/TD]
[TD]1334.53[/TD]
[TD]2285.53[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]231.00[/TD]
[TD]214.00[/TD]
[TD]254.00[/TD]
[TD]264.00[/TD]
[TD]258.00[/TD]
[TD]312.00[/TD]
[TD]330.00[/TD]
[TD]300.00[/TD]
[TD]312.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD]385.00[/TD]
[TD]138.00[/TD]
[TD]306.00[/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]345.02[/TD]
[TD]312.00[/TD]
[TD]977.55[/TD]
[TD]1756.55[/TD]
[TD]2466.55[/TD]
[TD]3408.55[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]110.00[/TD]
[TD]412.00[/TD]
[TD]470.00[/TD]
[TD]325.00[/TD]
[TD]333.00[/TD]
[TD]310.00[/TD]
[TD]251.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]329.00[/TD]
[TD] [/TD]
[TD]324.00[/TD]
[TD] [/TD]
[TD]246.00[/TD]
[TD]335.00[/TD]
[TD]581.00[/TD]
[TD]1234.00[/TD]
[TD]1234[/TD]
[TD]1485[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]326.00[/TD]
[TD]293.00[/TD]
[TD]330.00[/TD]
[TD]215.00[/TD]
[TD]258.00[/TD]
[TD]324.78[/TD]
[TD]797.78[/TD]
[TD]1746.78[/TD]
[TD]1746.78[/TD]
[TD]1746.78[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]301.00[/TD]
[TD] [/TD]
[TD]545.00[/TD]
[TD]128.00[/TD]
[TD]321.45[/TD]
[TD]994.45[/TD]
[TD]1295.45[/TD]
[TD]1295.45[/TD]
[TD]1295.45[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]333.00[/TD]
[TD]305.00[/TD]
[TD]345.00[/TD]
[TD] [/TD]
[TD]333.00[/TD]
[TD] [/TD]
[TD]333.00[/TD]
[TD]1316.00[/TD]
[TD]1316[/TD]
[TD]1316[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]231.00[/TD]
[TD]214.00[/TD]
[TD]254.00[/TD]
[TD]264.00[/TD]
[TD]258.00[/TD]
[TD]312.00[/TD]
[TD]330.00[/TD]
[TD]300.00[/TD]
[TD]312.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD]385.00[/TD]
[TD]138.00[/TD]
[TD]306.00[/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]345.02[/TD]
[TD]312.00[/TD]
[TD]977.55[/TD]
[TD]1756.55[/TD]
[TD]2466.55[/TD]
[TD]3408.55[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]123.00[/TD]
[TD]215.00[/TD]
[TD]145.00[/TD]
[TD]254.00[/TD]
[TD]268.00[/TD]
[TD]297.00[/TD]
[TD]315.00[/TD]
[TD]331.00[/TD]
[TD]364.00[/TD]
[TD]300.00[/TD]
[TD]512.00[/TD]
[TD] [/TD]
[TD]331.00[/TD]
[TD] [/TD]
[TD]325.00[/TD]
[TD]288.00[/TD]
[TD]242.00[/TD]
[TD]338.00[/TD]
[TD]868.00[/TD]
[TD]1524.00[/TD]
[TD]2336[/TD]
[TD]3346[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="19"><col span="3"></colgroup>[/TABLE]


New month number:
[TABLE="width: 610"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customers[/TD]
[TD]CODE[/TD]
[TD]Jul-17[/TD]
[TD][/TD]
[TD]Customers[/TD]
[TD]CODE[/TD]
[TD]Jul-17[/TD]
[TD][/TD]
[TD]Customers[/TD]
[TD]CODE[/TD]
[TD]Jul-17[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]124[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]124[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]214[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]214[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]3[/TD]
[TD]287[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]122[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]122[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]4[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]312[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD]312[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="9"></colgroup>[/TABLE]


Updated Summary:
[TABLE="width: 1366"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]CODE[/TD]
[TD]Jan-16[/TD]
[TD]Feb-16[/TD]
[TD]Mar-16[/TD]
[TD]Apr-16[/TD]
[TD]May-16[/TD]
[TD]Jun-16[/TD]
[TD]Jul-16[/TD]
[TD]Aug-16[/TD]
[TD]Sep-16[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]Jul-17[/TD]
[TD]LAST 3 MTHS[/TD]
[TD]LAST 6 MTHS[/TD]
[TD]LAST 9 MTHS[/TD]
[TD]LAST 1 year[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]310[/TD]
[TD]125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]251[/TD]
[TD]751[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]145[/TD]
[TD]254[/TD]
[TD]124[/TD]
[TD]435.00[/TD]
[TD]575.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]245.00[/TD]
[TD]124.00[/TD]
[TD]369[/TD]
[TD]944[/TD]
[TD]1757[/TD]
[TD]1902[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]110.00[/TD]
[TD]412.00[/TD]
[TD]470.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]328.00[/TD]
[TD]287.00[/TD]
[TD]336.00[/TD]
[TD]320.00[/TD]
[TD]145.00[/TD]
[TD] [/TD]
[TD]214.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]214.00[/TD]
[TD]870[/TD]
[TD]870[/TD]
[TD]1229[/TD]
[TD]2172[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]122.00[/TD]
[TD]122[/TD]
[TD]122[/TD]
[TD]122[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]312.00[/TD]
[TD]312[/TD]
[TD]312[/TD]
[TD]312[/TD]
[TD]312[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]231.00[/TD]
[TD]214.00[/TD]
[TD]254.00[/TD]
[TD]264.00[/TD]
[TD]258.00[/TD]
[TD]312.00[/TD]
[TD]330.00[/TD]
[TD]300.00[/TD]
[TD]312.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD]385.00[/TD]
[TD]138.00[/TD]
[TD]306.00[/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]345.02[/TD]
[TD]312.00[/TD]
[TD]124.00[/TD]
[TD]781[/TD]
[TD]1743[/TD]
[TD]2266[/TD]
[TD]3203[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]110.00[/TD]
[TD]412.00[/TD]
[TD]470.00[/TD]
[TD]325.00[/TD]
[TD]333.00[/TD]
[TD]310.00[/TD]
[TD]251.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]329.00[/TD]
[TD] [/TD]
[TD]324.00[/TD]
[TD] [/TD]
[TD]246.00[/TD]
[TD]335.00[/TD]
[TD]214.00[/TD]
[TD]795[/TD]
[TD]1119[/TD]
[TD]1448[/TD]
[TD]1448[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]326.00[/TD]
[TD]293.00[/TD]
[TD]330.00[/TD]
[TD]215.00[/TD]
[TD]258.00[/TD]
[TD]324.78[/TD]
[TD]122.00[/TD]
[TD]705[/TD]
[TD]1543[/TD]
[TD]1869[/TD]
[TD]1869[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]312.00[/TD]
[TD]312[/TD]
[TD]312[/TD]
[TD]312[/TD]
[TD]312[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]301.00[/TD]
[TD] [/TD]
[TD]545.00[/TD]
[TD]128.00[/TD]
[TD]321.45[/TD]
[TD]25.00[/TD]
[TD]474[/TD]
[TD]1320[/TD]
[TD]1320[/TD]
[TD]1320[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]333.00[/TD]
[TD]305.00[/TD]
[TD]345.00[/TD]
[TD] [/TD]
[TD]333.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]333[/TD]
[TD]983[/TD]
[TD]1316[/TD]
[TD]1316[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]231.00[/TD]
[TD]214.00[/TD]
[TD]254.00[/TD]
[TD]264.00[/TD]
[TD]258.00[/TD]
[TD]312.00[/TD]
[TD]330.00[/TD]
[TD]300.00[/TD]
[TD]312.00[/TD]
[TD]325.00[/TD]
[TD] [/TD]
[TD]385.00[/TD]
[TD]138.00[/TD]
[TD]306.00[/TD]
[TD]335.00[/TD]
[TD]320.53[/TD]
[TD]345.02[/TD]
[TD]312.00[/TD]
[TD]287.00[/TD]
[TD]944[/TD]
[TD]1906[/TD]
[TD]2429[/TD]
[TD]3366[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]123.00[/TD]
[TD]215.00[/TD]
[TD]145.00[/TD]
[TD]254.00[/TD]
[TD]268.00[/TD]
[TD]297.00[/TD]
[TD]315.00[/TD]
[TD]331.00[/TD]
[TD]364.00[/TD]
[TD]300.00[/TD]
[TD]512.00[/TD]
[TD] [/TD]
[TD]331.00[/TD]
[TD] [/TD]
[TD]325.00[/TD]
[TD]288.00[/TD]
[TD]242.00[/TD]
[TD]338.00[/TD]
[TD]147.00[/TD]
[TD]727[/TD]
[TD]1340[/TD]
[TD]2183[/TD]
[TD]3178[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="19"><col span="4"></colgroup>[/TABLE]


Each month, I need to spend almost a day to compile the data. Hope anyone of you can help me automate it.

Looking forward to hear the good news soon.
Thanks in advance for the helps.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I've read your post and I've been able to understand your request except for the following line:
3. If there is new customer in any worksheet, add new Row in "Summary" - "K" & "L"

I understand if there is a new customer add a new Row in Summary but what does "K" & "L" mean? In the Summary worksheet Columns K & L are simply months. Are you meaning that in your example you already have Customers A through J and K and L are just the next 2 customers?

Let me know, overall this doesn't look too hard to develop.
 
Upvote 0
Hi frank_AL,
Thanks for the replied.
Please ignore the "K & "L" that I mentioned.
Your understanding is correct. If there is Customer with New code in the new month, add it in Summary.
What I hope to have is, when I received the new month number, the macro will search the last month for all customer sheets and paste all in the Summary sheet.


Thanks in advance for the helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Best formula for data lookup from Store#, Dept #, and Date from a lot of data
Replies
1
Views
746
KLund1
K
G
  • Question Question
Countifs Multiple Criteria Met
Replies
5
Views
776
AhoyNC
A
G
  • Question Question
Help with complicated situation
Replies
9
Views
642
oldbrewer
O
M
  • Question Question
Vlookup? Match? Index? HELP
Replies
2
Views
705
njimack
N
A
  • Question Question
Nested IFs
2
Replies
10
Views
1K
azii
A

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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