An improperly constructed table

[cmcreynolds]

I was just handed an Excel file with "tables" within it, however, there is no database structure to it - it is basically just a ledger - months at the top, but above that headers "merged and centered"; Categories and Subcategories on the left. I'm supposed to link (via Power Query) this data to another report and it's just impossible in the current format.

Before I start the mind-numbing process of converting this manually, is there a way for Excel (or another program) to convert this to a database table? (I'm preparing/fearing the response is "spend the next few days copying and pasting")

Thank you.

 

[cmcreynolds]

Spiller - yes, that's what I'm trying to do (put them all into one large table). I like using PQ, but when I tried using it, it did not read the worksheets correctly due to the way the creator formatted it.

 

[cmcreynolds]

What I (think) I want are the fields: Program (which Sheetspread has as A, B, C, etc.), Month, Leads, New Students, Enrollment, and Revenue. The result after the Pivot Table Wizard was it put Leads, New Students, Enrollment, and Revenue all in one column and repeated them. Would it not be a better design to have them each in their separate fields/columns? Then I can manipulate the PivotTable with columns/rows the way I need and format values (i.e. counts as whole numbers, revenue as $ amounts)?

(I'm completely okay if someone says "no", but please explain. )

 

[sheetspread]

pretend that Leads are in dollars? and New Enrollments and Returning Students are counts?

Then it sounds like Leads should be a separate table that you can combine with the New Enroll/Returning table for reporting purposes. Can you derive the counts from the dollars in the source data? Or are they independently determined?

You shouldn't have separate fields because it's repetitive, just like you wouldn't have Jan-Feb-Mar fields instead of a single "Month" field. A rule of database design is repeat rows, not columns.

Unless I've totally missed something and those fields aren't so related

 

[cmcreynolds]

The fields in the source document are in appropriate dollar amounts and whole numbers.

 

[cmcreynolds]

Unless I've totally missed something and those fields aren't so related

Exactly - I don't think they are related in that they were each created using separate methods.

 

[sheetspread]

Ok, given everything you've told me, do you think it would work to combine the 13 tabs, get them into that format I posted in #7, sort, and cut the Leads rows into their own table?

 

[sheetspread]

Exactly - I don't think they are related in that they were each created using separate methods.

Ok, then give it a try, or consider what I said in the previous post.

 

[cmcreynolds]

Yes? And I suppose I can do that to create a separate table for each "new field" I want? Luckily, there would be only 6 "new fields".

Oh! and one of those 6 is calculated, so, I can delete that and add a calculated field to the Data Model...it's getting easier! (lol I hope)

 

[sheetspread]

If you're sure you need all 6 and can't combine some together then try it, otherwise we'll think of something else

 

[cmcreynolds]

Since there were only 5 "fields" - I manually (well, kind of) combined each tab for the rows and then utilized the wizard. It was just a teencie bit mundane, but it only took me an hour or so.