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. 

[Luke_C · Accepted solution]

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.

[Adrian_T]

Hey @brian_farrer,

Here's another way to achieving your use case! 😊

[brian_farrer]

Thanks @Luke_C ! I had to make a couple of small tweaks given my unsanitized dataset was slightly different, but this worked well for my purpose! Appreciate the very quick response!

[brian_farrer]

@Adrian_T - I think this would have worked well, too! But was pretty focused on fitting @Luke_C 's solution to my problem. Thanks for responding and being helpful.