Normalizing Different Excel Template formats That Are in a Single Workbook

[smoskowitz]

Hi All –

I was asked to be part of a proof-of-concept project at my company that potentially could be a big deal for me. What’s going on is that our Agencies send our Network heads various client information templates that all get put into a single workbook by Network and sent to corporate. Some workbooks could have several tabs with information. Much of the information is somewhat standard, but the format could be wildly different. Now in the past, I have aggregated hundreds of templates via batch macro, but my experience has always been that the templates are all the same and locked down – so not to bad. However, these templates are not locked down with no intention of doing so) and the format could change, but the information should always be somewhat consistent.

What happens right now is that all these templates are sent to a couple of (non-Alteryx) developers in Hong Kong and they spend some time getting the information into PowerBi via the internally built Power query. Much of it is still manual and laborious. There could be 10-15 workbook with varying amount of tabs in them,

Technology management decided that Alteryx could be the answer, but I have always predicated this with some consistency in the source data format. I am now grappling more with how to make this more dynamic where Alteryx could adjust for some of these differences.

Attached are some examples of how data could come in. Each workbook might have a mix of these in different tabs. The goal would be to line up as much of the data in an automated way as possible. I am looking for advice on handling workbooks like the one attached where each workbook could have different data structures like the one attached.

If you have a workflow to share – or want to try your hand at these – I would love to see the techniques, you employ in bringing structure to the unknown (Never stop learning.).

Each template has some core information, like entity, client, Net Revenue (USD), but not all of it. The goal is to create a single dataset that lines up as best as possible (all entities in column A, Name in B, Net Revenue (USD) in a column and Net Revenue (Local) in another column…etc. Not every bit of information will be in every spreadsheet, but there is some sort of consistency. But dynamic enough to adjust to some changes or even new templates that get added.

Thanks in advance for your assistance!

The data has all been randomized.

Seth

[Ben_H]

Hi @smoskowitz,

I gotta say, this isn't a straightforward thing to do at all.

What you're saying is you have multiple workbooks with data across multiple sheets (I'm assuming with no clear naming convention), with fields that may or may not be common across the diferrent sheets,with fields that have different naming conventions and are sometimes split across multiple rows/merged cells - and any of this structure may change at any time.

Wouldn't it be easier to work with your agencies to standardise the template that they use?

Regards,

Ben

[PhilippK · Accepted solution]

Hi @smoskowitz ,

that sounds like a promising, but sophisticated use case.

As a starting point - do you know the following macros?

• Excel Input (different schema)
  • https://gallery.alteryx.com/#!app/Excel-Input--different-schema-/58ff1a7fa18e9e18fcaefd68
• Wildcard Input tools of the CREW Macro package
  https://community.alteryx.com/t5/Engine-Works/Crew-Macro-Pack-2016-Q2-Release/ba-p/26482

In addition, you could leverage the virtual solution center of Alteryx, where you get 1on1 support of Alteryx experts:

https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc

In the end, I think this really requires man power / consultancy work to build a solution that works for all exceptions, too.

Best regards

Phil

[smoskowitz]

Thank you @Ben_H and @PhilippK for the responses.

I was aware of the links provided and I did reach out to Alteryx. I am currently working with my account executive on this as it could be a good project/use case/proof-of-concept. It is still early days of the project, but one that I am very excited about,

If we can crack this, I will share it with the community because I cannot be the only one with this kind of issue.

If you have suggestions, please share!

Thank,

Seth

[Ben_H · Accepted solution]

Hi @smoskowitz 

What we're saying is that this is not something you're are going to get a solution for on the forum.

There is a serious amount of work involved in creating something like this, and the reality is that it will be heavily tied to your specific requirements.

For example - creating a process to reliably take in the data below, work out the what is/isn't part of the data table, and then identify/construct useable headers is non-trivial. Especially as any and all of it is subject to change.

Regards,

Ben

[smoskowitz]

@Ben_H -

Thank you for the response. What you said is a correct statement -- but it never hurts to ask. I am working with my account executive on this.

Thanks,

Seth

[Ben_H]

HI @smoskowitz,

It is definitely a worthwhile (and highly valuable) project, it's just that the scope is too great for this forum at the moment.

(Someone may yet prove me wrong)

I think that once you get further into the project and can break it down into specific problems/areas of improvement you'll find the community more useful.

Regards,

Ben