I'm facing the following problem:
Every day I have about 56 Excel files where the filenames fit the following 2 patterns:
- half of them are named STAGE1_PAID_DD_MM_YYYY: these files would each have 2 columns (Column A is in Text Format with no headers + Column B will also be in Text Format with no headers, but the entries will be a bunch of standardized error codes repeating themselves from one row to another).
- the other half are named CRON_DD_MM_YYYY: these files would each have 2 columns (Column A is in Text Format with no headers + Column B will also be in Number Format)
I will always have 56 files because I will always be interested in pulling out data from the Excel files starting with DD_MM_YYYY = TODAY()-29 up to DD_MM_YYYY = TODAY()-1.
What I need is a Macro that I can run on daily basis from an empty workbook which must:
- 1st gather all the data from all the CRONs into one unique 2 columns auxiliary list1 and arrange the data by Column A; if there will be values on column A which appear more than once in the CRONs, they must also appear more than once in the auxiliary list1.
- on a 2nd phase, this auxiliary list1 must be processed as follows:
* the already existing unique values by column A must remain as they are
* the values appearing more than once by column A must be turn into a unique vale by comparing the values on colum B and keeping only the row with the MAX number on column B
- on a 3rd and phase, all the rows where the value on the column B is lower than 100 must be eliminated(deleted) from the auxiliary list1
- on a 4th phase, an auxiliary list2 must be gathered by looking up the remaining values on column A from the auxiliary list1 in the columns A of all the STAGE1_PAID_DD_MM_YYYY files. The auxiliary list2 mist have unique values on column A, so if there will be values on column A which are found more than once in the STAGE1_PAIDs, they must appear only than once in the auxiliary list2. For this, the following rule must apply:
* the auxiliary list2 must have 3 columns: column A will have the values from the column A from the auxiliary list1 + column B will have the looked up values from the colums B of the STAGE1_PAIDs + column C must have the exact name of the STAGE1_PAID from where the data was pulled out
* if the value on the column A from the auxiliary list1 is not found at all in any of the STAGE1_PAID files, then this value is not to be included at all in the auxiliary list2
* if the value on the column A from the auxiliary list1 is only found once in all the STAGE1_PAIDs then the data for the auxiliary list2 must be pulled out in accordance with the above columns A,B and C explanations
* if the value on the column A from the auxiliary list1 is only found more than once in all the STAGE1_PAIDs then the data for the auxiliary list2 must take into account only the values found in the most recent of the STAGE1_PAIDs (this as per their filenames) and must be pulled out in accordance with the above columns A,B and C explanations
- on a 5th and final phase, the auxiliary lists 1 and 2 must be saved in the cureent workbook as 2 separate work-sheets and the weoksheet must be auto-saved on the computer with the filename FINAL_LIST_DD_MM_YYYY.XLSX, where DD_MM_YYYY.XLSX represent today's date.
Can anyone help me with the script for this Macro or at least give me some clues on the best way to implement it? Is it even doable?
PS: I;m a newbie with Macros but I'm a fast learner ;).