I would like to ask for help as I’m stuck with my task related to excel documents merging.
I’m trying to merge multiple excel documents, which mostly contain same column headers, but some columns names and position vary from one excel document to another and I’m trying to put all document data in one single excel file.
In this case if document 1 contains column with name “AAA” and document 2 also contains column “AAA” then just add rows with data one after another, but if document 3 doesn’t contain column “AAA” then leave those fields blank and move to next column.
Also if document 4 contains column with name “DDD” which wasn’t in document 1,2,3 then add data to that column(not from the beginning(*1 cell) but from the new row to not mix data from previous documents) as all columns are known and already is added to temp table.
And this is the issue, I don’t know how to perform this check/search for column name and don’t know how to paste information there as these columns in each document could be in different position(I mean in first doc column “AAA” could be in column A but in doc 2 this column “AAA” could be in column F).
Steps I’m performing now:
By “read range” activity I’m copying column headers and all rows with data from first document then I’m adding other headers manually with “add data column” to temp table as I know all column names from all documents.
After this step I’m performing “append range” activity from temp table to my created document with already added headers in same order as in temp table.
All those steps are in “for each” activity until all files from folders are copied
In addition I have last column with file name from which all data is taken for later filter in excel, may be it could be for a some sort of pointer.
Could you please suggest me the way I could solve this issue ?
Thank you in advance for help.