• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to concatenate columns in 2 sheets, then vlookup notes

T

tpatpl

New Member
Hello,

I am attempting to put together a macro in order to automate the following steps:

We receive a report daily. Henceforth Workbook 1.
Workbook 1 will have columns A and B, for "Sales Order" and "Line"

I would like to concatenate these into a column in front of A, then insert a column between "Sales Order" and "Line" named "Notes" or "Comments"

Therein, the user can insert their notes. The next day, we would receive Workbook 2. Many of these "Sales Order" and "Line" will be the same - rather than redo or copy/paste every note, I would like to find a way to pull over the notes from Workbook 1 into a similar column in Workbook 2. Thereafter, it would be able to go from Workbook 2 to Workbook 3 the ensuing day, and so forth.

Is there a way to do this, and does the sheet name factor in here? For instance, I would name Sheet A on Workbook 1 a certain way, if I did not name Sheet A on Workbook 2 the same would it cause an issue?

Any advice would be greatly appreciated :)
 
vletm said:
Upload a Sample File to get a quicker response
with Sample data
Click to expand...

Hi vletm thank you, please see attached. For instance, Book3 is yesterday's report with comments put in. Book4 is today's report. I want to automate the process of putting Book3 into the form of Book3 with concatenate field. Then, I would like to have it pull over the notes into the form of Book4 with vlookup field.

The goal is that we can reduce time spent checking lines that have already been worked on and cleared through using a macro. And then repeat this action day to day. Therefore anything that pulled over as #N/A would simply show the user what has to be worked fresh as it was not on the previous report. These reports will typically have upwards of 300 lines, these are simply pruned examples.

I'm wracking my brain trying to pull the data from book to book without having to do it manually but unfortunately my macro expertise is limited (albeit growing ;) )
 

Attachments

  • Book4.xlsm
    11.2 KB · Views: 3
  • Book4 with vlookup field.xlsm
    12.9 KB · Views: 4
  • Book3.xlsm
    11.2 KB · Views: 3
I apologize here is the 4th sample

Also please note there will be several tabs like this on each book, would I be able to make this work on a sheet by sheet basis, through naming conventions?
 

Attachments

  • Book3 with concatenate field.xlsm
    11.6 KB · Views: 1
@tpatpl
Do You mean that "Sales Order" and "Line" are now "Sales Order" and "Order Item"? No problem ..
Next You want someway 'new column' between those. That's possible to do.
It should do via 'Book4' or any of 'the main book'.
'VLookUp' works until You have both (all) files,
I recommend to copy values from 'somewhere' to 'the main book'.
What would You do
if Book3 (3=date) and Book4 (4=date) same 'Sales Order' has both different 'Notes' ... which one wins?
Or Book3 has column 'Notes' and Book4 has column 'Comments'.
which one is stronger ( or You're using only 'Notes'?)
And You've many 'tabs'.
You should have 'a fixed idea of the whole packet' or You have to set everytime which tab should copy and so on.
>> Anyway, this is also possible to do after clear plan.
Try to figure the clear plan and ask again.
 
@vletm

Hello I think I was not clarifying well

Let us say I receive a workbook without any notes. I know in order to pull the notes over, I will need to have a column that is in both books that can work for a vlookup - that is why I am concatenating the "Sales Order" and "Line" fields. then, I want to be able to pull the notes from the first workbook into a new column.

I am not sure how to upload a good sample that represents what I am trying to do
 
Looking at Book3 here is a step by step of what I need to do

Starting with first book
Insert Column A - Concatenate "Sales Order" and "Order Item" into that column.
Repeat process in second book
Insert Column B for notes in second book
Vlookup the notes, using the inserted Column A to the notes column as the table array

And I need to do this across multiple tabs, and be able to repeat this every day when we get the new book
 
@tpatpl
I've tried to think this case too.
If I understood that You wanted like Book2 -> Book3 -> Book4 ... Book-chain
and information should be able to update with every step,
it would need external file!
I recommend this:
If You'll get daily new same kind of file then
You should have 'the Book_Master'.
It's possible to 'add NOTES-column to daily-file'.
Please, open and read instructions.
I can try to do this step-by-step.
 

Attachments

  • Book_Master.xlsm
    26.4 KB · Views: 5
vletm said:
@tpatpl
I've tried to think this case too.
If I understood that You wanted like Book2 -> Book3 -> Book4 ... Book-chain
and information should be able to update with every step,
it would need external file!
I recommend this:
If You'll get daily new same kind of file then
You should have 'the Book_Master'.
It's possible to 'add NOTES-column to daily-file'.
Please, open and read instructions.
I can try to do this step-by-step.
Click to expand...
Hello, this is causing an error when I select the Book3
 
You must log in or register to reply here.
Back
Top