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]

But when I follow the video, it puts those into the same column. It's like a rubik's cube that I'm wanting to rotate! lol

Edit: I need them each in their own field, not all in one field...does that make sense? (remember: sinus headache)

 

[sheetspread]

Oh if you mean how do you separate the group field from the name field after concatenating and unpivoting, use text to columns in the data menu.

 

[cmcreynolds]

I'm able to do that. Maybe I'm not expressing what I need - Later, I will have to group by Leads, Enrollments, New Students, etc. So, don't they have to be their own fields instead of lumped into one (as it is now)?

I suppose I can keep them together in the field and label it "category"?

 

[cmcreynolds]

But the problem is that some of them are counts of students and some are $ amounts (revenue and cost). Wouldn't it be better if they are separated into their own fields?

 

[sheetspread]

Yes, keep them in one field and either pivot like this:

Excel 2010
	A	B	C	D
1
2
3	Sum of Value	Column Labels
4	Row Labels	Leads	New Enrollment	Returning Students
5	A
6	Jan	52	15	8
7	July	52	0	0
8	Feb	52	0	0
9	August	52	15	3
10	Oct	52	8	4
11	Nov	52	0	0
12	Dec	52	0	0
13	Sept	52	0	0
14	A Total	416	38	15
15	B
16	Jan	8	5	18
17	July	17	0	0
18	Feb	8	0	0
19	August	17	5	13
20	Oct	17	0	12
21	Nov	17	0	0
22	Dec	17	0	0
23	Sept	17	0	0
24	B Total	118	10	43
25	C
26	Jan	29	5	17
27	July	29	0	0
28	Feb	29	0	0
29	August	29	10	13
30	Oct	29	0	20
31	Nov	29	0	0
32	Dec	29	0	0
33	Sept	29	0	0
34	C Total	232	15	50
35	D
36	Jan	31	12	67
37	July	31	0	0
38	Feb	31	0	0
39	August	31	2	89
40	Oct	31	2	65
41	Nov	31	0	0
42	Dec	31	0	0
43	Sept	31	0	0
44	D Total	248	16	221
45	E
46	Jan	24	10	27
47	July	36	0	0
48	Feb	24	0	0
49	August	36	7	26
50	Oct	36	3	33
51	Nov	36	0	0
52	Dec	36	0	0
53	Sept	36	0	0
54	E Total	264	20	86
55	F
56	Jan	57	35	54
57	July	114	0	0
58	Feb	57	0	0
59	August	114	35	36
60	Oct	114	0	68
61	Nov	114	0	0
62	Dec	114	0	0
63	Sept	114	0	0
64	F Total	798	70	158
65	G
66	Jan	28	5	12
67	July	28	0	0
68	Feb	28	0	0
69	August	28	5	0
70	Oct	28	5	8
71	Nov	28	0	0
72	Dec	28	0	0
73	Sept	28	0	0
74	G Total	224	15	20
Sheet4

Or sort/filter. Remember, you can present data any way you want, but store it in the 1-to-1 normal data format rather than the separate sheet crosstab jumble you unfortunately inherited.

 

[sheetspread]

But the problem is that some of them are counts of students and some are $ amounts (revenue and cost). Wouldn't it be better if they are separated into their own fields?

In that case Value would still be its own field, and you can add a calculated field for count

 

[cmcreynolds]

Okay - I've done that, but can you format some as dollar amounts and some as counts? Doesn't Excel treat them as the same "kind" of number if they are in the same field? I didn't put all the categories down in my original, but some are going to be revenue/cost related. (A-January = $10,000, A-February, $750, etc)

 

[sheetspread]

Keep each field in the same format yes, but can you show me a before/after example (in simplest form)? I can't tell from the original example which are dollar amounts and which are counts.

 

[cmcreynolds]

Yes, I apologize. Can we pretend that Leads are in dollars? and New Enrollments and Returning Students are counts?

 

[SpillerBD]

Great - this is starting to get me somewhere. The person who created these went just for visual layout and not for any type of database usage. So, there are thirteen tabs with all this data. My goal is to combine them all into one data table, then use pivot tables and slicers (since the tabs will never be compared to one another, I think slicers would be much easier to manipulate).

Anyway, I'm progressing - even with a horrible sinus headache this morning.

Thank you very much.

PowerQuery has that lovely UNPIVOT function in it which looks like it may have been a possible angle on your original data (not seen it).
You can have a Pivot Table on data from multiple tabs/tables using Multiple Consolidation Ranges (Pivot Table Wizard, Alt,D,P) though I do support having a single table as the primary choice.