VBA - Match two values from Table1 to two in Table2 and move/align

[Welshy1491]

Hi there, first time post here so i apologize in advance.

I have looked everywhere for VBA code to help me with this task but can't seem to find what i need.

I have a large table of engineers stock count which i add to once a month when we run our stock reports, the table grows by two columns every month and usually by a number of rows depending on stock intake. Columns A, B & C are Engineers Name, Part No. and Description. The values i add to the table every month are two columns wide which consists of Value and Quantity, so D,E are first months Value and Quantity, F,G are second months Value and Quantity and so on.

My process at the minute is to paste 5 columns of data (Engineers Name, Part No., Description, Value and Quantity) from this months stock report in to the next available columns of my worksheet. I then manually align the 5 columns of copied in data by matching the Engineers Name and Part No. to columns A and B of my original table. If an engineer has added parts to his stock not listed in the table i must create a new line and add the new Part No. in to my table and if he has used stock and no longer has it on the report i have to add zero's in. This can be very time consuming (having to do it for 15 engineers at the moments) and mistakes can be made when manually moving the rows of data to line up with the original data.

I am looking for VBA code which will allow me to automate this if possible? I have added a few pictures hopefully explain the issue clearer. "Initial Tables 1 & 2" show the two tables i begin with, "Intermediate1 & 2" shows the steps i take to align the data and "Final1 & 2" shows my eventual layout of the table.

Any help at all would be greatly appreciated

 

[rpaulson]

I can probably help you out with this. I want to make sure I understand.
You have a master list of Engineers with Material in Column A&B.
When you import a new list every month, you want to match the Engineers and Material ad add 2 new columns to the end (Colums H & I) in this case.
If the Engineer and Material are not in the master list you want to add them.
If the Engineer and Material are not on the new import list then add Zeros for that month.

Question. - if the list you are importing an Excel file or a text file (A text file could actually make the process easier)

-Ross

 

[Welshy1491]

Hi Ross, thank you for your response.

Yes, i have a master list that i keep, which has the Engineers Name in Column A and Material No. in Column B and Description in Column C.
Yes, after i paste in the 5 columns of new data at the beginning of the month I would like it to match Engineers Name and Material No. of the new data to those of the master list and sort it so it is in the correct row of the master list. It would be then great if it can delete the Engineers Name, Material Number and Description of the new data so i am only left with Quantity and Value Columns of the new data in the next two columns in the master list.
Yes, it would be great if it can add any new materials to the master list that are found in the newly pasted in data.
Yes, correct again.

The list i import is copy and pasted from am excel file the team leaders are sent every month from our control system, unfortunately i wouldn't be able to have this in a text file.

Thanks again,
Paul

 

[rpaulson]

I can make this work for you. Can you upload an actual excel file somewhere, so I can work with some real data?

 

[Welshy1491]

Unfortunately i cannot share the actual file i work off as it has the engineers names along with part numbers and prices from my organization but i can share the one i put together that is in the pictures?

Microsoft OneDrive

1drv.ms

Hopefully this works

 

[rpaulson]

I cannot get your files from the one drive. No permission or Files no longer available error.

 

[Welshy1491]

It was deleted by a 3rd party before you could get to it i believe. What is the best way for me to share the file directly with you?

 

[rpaulson]

upload it again. Ill wait for you to post the link

 

[Welshy1491]

Template

 

[rpaulson]

I have the file.