Error data summary for Incorrect data

[Kaish]

Hi all, I am working in a file and I want to get a solution for my output. I have file in which I have applied macro according to the schema, there are multiple files with different sheets, the macros help to consolidate the file according to matched schema. Now, I have a new request from my team 1) I want to know if all the files have the data or not, if the data is coming from all the files or not, I want to get the summary for how many files have the data and how many don't have the data. 2) In my input file I have few rows which have different data rather than the usual data, Ex- Date column has few data in which some random texts or numbers are there, So I need to push only the data which has correct value, if the column has incorrect data type then the file will push only the correct data and if there is incorrect data I will get a new summary of the incorrect data file. 

Can this be done ? I can provide examples if anyone can help me out. Thanks

[Kaish]

Hi @jdminton Actually I have multiple files, so I am working using directory tool, I have used these macros in directory tool to read these files. My requirement is: I have many files and in these files there are multiple tabs. out of all the tabs in those files, there is one such tab which is similar for all, that one single tab has same schema attributes and my data is extracted with the schema and positional value and in the end I need the consolidation of the data which is appended. When I used my macros it ran properly and my consolidated data was appended accordingly. Can you guide me how can I work on directory tool with the dynamic input to achieve my solution as I mentioned in my blog. Thanks

[jdminton]

Ok, it was a bit confusing since the macros provided were not working as you sent them. There are ways to test the data, but you've not provided any data except years and numbers. You could validate the years exist in each table, but I don't know how you would validate the numbers.

Is there a reason the data is coming from random locations in Excel files? Is this data available from another source? I don't think the validation will help if you are trying to validate using the same files.

[Kaish]

Hi @jdminton kindly find the example of my data in this blog, I have attached the files https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Consolidation-of-different-tab... . You can add one more file "Order 4" with only the header with blank data. Because as my requirement is to find the number of files from which the data is not coming, so if you add one more file without any data will work as my example. Kindly guide me if I can resolve this. I get the files from a particular tool, extract the data and then store it in a single folder, there are 15-20 files in that folder. Kindly guide me through the steps.

[jdminton]

@Kaish your request is not making sense to me in relation to the previous requests. The files you are now referencing are different than the ones you referenced in this thread. Maybe this is a separate question, but it isn't clear.

Based solely on this question, you can use the directory tool to bring in the files from the folder, use the macros (see in attached workflow) to bring in the data from Excel, and compare back to the directory list.

 

[Raj]

@Kaish 
find the workflow attached 
mark done if solved.