Hi AhmedSolmn,
1. Are you going to know before starting the diagram which 3 statuses you need to split on?
If you know then what are they?
i. Valid, Invalid should go into ValidInvalid.xlsx
ii. Registered, UnRegistered should go into RegUnReg.xlsx
iii. ????
2. Do you need the rows in the split files to be exactly in the same order as in the Original Excel? Or is it okay to first have all Valid ones, then Invalid ones?
For eg, if original Excel file has below row.
Row1 - Valid
Row2 - Invalid
Row3 - Valid
Row4 - Invalid
Then how should the rows of VaildInvalid.xlsx be?
Option A:
Row1 - Valid
Row2 - Invalid
Row3 - Valid
Row4 - Invalid
Or Option B:
can they be?
Row1 - Valid
Row2 - Valid
Row3 - Invalid
Row4 - Invalid
Post as much details a possible while answering above questions.
Thank you for the quick reply. I will answer your questions below. I have already found a solution but will give more detail to help others if anyone falls in the same problem.
To answer your questions:
The statuses are known and are,
i. Valid and Invalid go into ValidExcel.xlsx
ii. Registered and Unregistered go into RegExcel.xlsx
iii. Created and Not Created go into CreatedExcel.xlsx
The original excel file has no pattern to what the statuses are(any status can be in any row)
Both of the examples you gave above are both possibilities.
The solution I found is this:
Opening excel instance for original file and filtering the collection with collection manipulation as follows,
I filtered all six possible status' then ended the instance. Then merged each of the respective status' together.
Then started one instance each for the 3 new workbooks and saved as follows:
And thats that.
hope it helps someone. Or if someone knows an easier way to do this then please comment.
