lookup a master list by comparing other lists , then append as "existing" or "new"

[meghanavmr]

1) input a master list of items (this gets built as data collected)

2) get data from sources

3) combine data from sources then, lookup the master list, if the product is in master list append field "existing" else "new". The issue I have is that if there are products that get appended in master list are duplicated and "new" items are ignored. 

[binuacs]

@meghanavmr Can you upload the input files? either export the workflow (option-export workflow) and attach or just attach the input files 

[meghanavmr]

attached are input files

[binuacs]

@meghanavmr I updated the workflow based on my understanding. 

1. Combined the Source1 and Source using the wild character functionality in the input tool

2. Vlookup - the Combined source files and the Master files based on the field Name

3. If the Name is not present in the Master File created a flag column and updated it as "New", the name present is then updated as "existing".

do you want to append the names which are not present in the master file to the existing master file? 

Let me know how you want to proceed.

[meghanavmr]

Yes! Thank you,

[meghanavmr]

am unable to open the workflow; is it possible to share the solution in any other way?

[binuacs]

@meghanavmr attaching the workflow

[meghanavmr · Accepted solution]

@binuacsthank you, I was able to download the solution and yes please would you be able to append the names which are not present in the master file to the existing master file or create a new list which loops back and saves it as a new whole list? . Also, what if every supplier file shared same attribute of data but is present in multiple files and cannot be merged. And how can we add a macro to pick these files (browse file/ folder?)

[binuacs]

@meghanavmr Updated the workflow to append the new names in the same master file.

Can you elaborate a little bit more about your second requirement?

[meghanavmr]

I am unable to share the exact output. However, if the files that were merged (supp) had different column names and could not be merged in excel (consider each supp file separate), then we would need to have every single data source as a separate input. In such a case how would the find and replace work? There would be 3 inputs instead of 1. Secondly, what are the ways we can use a macro to pull the files into the workflow?