Help Systematically Cleansing a Terribly Formatted File

brian_farrer

Hi everyone,

Someone at my company sent me a file that I'm struggling to line up systematically. I have this same file, each month (different tab), across 3 different files. I'm less concerned about aggregating the tabs and files, but wanted to provide that for reference for context that I need to find a way to resolve this systematically.

I need to know, by initial, the amount of draw at each location (each month, each year). The hardest part is I can't figure out how to get all the draws on a single line for each location.

Extra points for separating the initials from the numbers in parentheses. 

Distribution File Example.xlsx

Luke_C

Hi @brian_farrer 

Here's one way you could go about this. If you have multiple files you could modify this to be in a batch macro. I laid this data out vertically as it makes more sense to me that way, but you could go on to format however you'd like now that it's cleaned up.

1. Populate location using a multi row formula tool
2. Remove empty rows
3. Add tile tool and divide tile sequence by 2. This groups data for locations with multiple rows of data
4. Transpose data
5. Filter Draw records from non-draw records and join back together to get data side by side
6. Parse numbers from header
7. Remove empty rows

You may want to look at Location J's data. It is missing the word 'Draw' in one of the rows.

New Workflow3.yxmd