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.

 

[sheetspread]

(I'm preparing/fearing the response is "spend the next few days copying and pasting")

Certainly not. You can use formulas, VBA, the reverse pivot table with concatenate operators, a union query in SQL/MS Query/Power Query. Can you post the structure?

 

[cmcreynolds]

Can I e-mail it to you?

 

[Smitty]

Welcome to the Board!

We don't support file uploads here, but if you follow the HTML Maker link in my sig you'll be able to post a screen shot(s).

 

[sheetspread]

ADVERTISEMENT

So after running your picture through OCR and deleting rows/columns while repeating labels with F5(Goto):

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1				July	August	Sept	Oct	Nov	Dec	Jan	Feb
2	Leads	Avg Conv
3	Leads	27%	A	52	52	52	52	52	52	52	52
4	Leads	16%	B	17	17	17	17	17	17	8	8
5	Leads	7%	C	29	29	29	29	29	29	29	29
6	Leads	7%	D	31	31	31	31	31	31	31	31
7	Leads	18%	E	36	36	36	36	36	36	24	24
8	Leads	18%	F	114	114	114	114	114	114	57	57
9	Leads	12%	G	28	28	28	28	28	28	28	28
10	New Enrollment	Program
11	New Enrollment		A		15		8			15
12	New Enrollment		B		5					5
13	New Enrollment		C		10					5
14	New Enrollment		D		2		2			12
15	New Enrollment		E		7		3			10
16	New Enrollment		F		35					35
17	New Enrollment		G		5		5			5
18	Returning Students		A		3	0	4	0	0	8
19	Returning Students		B		13	0	12	0	0	18
20	Returning Students		C		13	0	20	0	0	17
21	Returning Students		D		89	0	65	0	0	67
22	Returning Students		E		26	0	33	0	0	27
23	Returning Students		F		36	0	68	0	0	54
24	Returning Students		G		0	0	8	0	0	12
Sheet1 (2)

What is column B for? Do you need it? If so, then keep the labels, concatenate, and alt-D, P unpivot (the full process can be seen here: https://www.youtube.com/watch?v=xmqTN0X-AgY) you'll have the datasheet.

 

[cmcreynolds]

Thank you very much - Column B could probably be deleted. Thanks for the link to the video. I "hoped" it could be done easily.

Cheers-

Clifton

 

[sheetspread]

ADVERTISEMENT

Well this macro I posted last week for a similar problem helps: http://www.mrexcel.com/forum/excel-questions/859948-conditional-transpose.html (#6)

Deleting Col B, filling the blanks with zeroes using F5-special-blanks-(type a formula to fill group names or zero to change blanks to zero)-ctrl-enter:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Group	Name	July	August	Sept	Oct	Nov	Dec	Jan	Feb
2	Leads	A	52	52	52	52	52	52	52	52
3	Leads	B	17	17	17	17	17	17	8	8
4	Leads	C	29	29	29	29	29	29	29	29
5	Leads	D	31	31	31	31	31	31	31	31
6	Leads	E	36	36	36	36	36	36	24	24
7	Leads	F	114	114	114	114	114	114	57	57
8	Leads	G	28	28	28	28	28	28	28	28
9	New Enrollment	A	0	15	0	8	0	0	15	0
10	New Enrollment	B	0	5	0	0	0	0	5	0
11	New Enrollment	C	0	10	0	0	0	0	5	0
12	New Enrollment	D	0	2	0	2	0	0	12	0
13	New Enrollment	E	0	7	0	3	0	0	10	0
14	New Enrollment	F	0	35	0	0	0	0	35	0
15	New Enrollment	G	0	5	0	5	0	0	5	0
16	Returning Students	A	0	3	0	4	0	0	8	0
17	Returning Students	B	0	13	0	12	0	0	18	0
18	Returning Students	C	0	13	0	20	0	0	17	0
19	Returning Students	D	0	89	0	65	0	0	67	0
20	Returning Students	E	0	26	0	33	0	0	27	0
21	Returning Students	F	0	36	0	68	0	0	54	0
22	Returning Students	G	0	0	0	8	0	0	12	0
Sheet1 (3)

then running the macro (rename the sheet "Source" first):

Excel 2010
	A	B	C	D
1	Group	Name	Month	Value
2	Leads	A	July	52
3	Leads	A	August	52
4	Leads	A	Sept	52
5	Leads	A	Oct	52
6	Leads	A	Nov	52
7	Leads	A	Dec	52
8	Leads	A	Jan	52
9	Leads	A	Feb	52
10	Leads	B	July	17
11	Leads	B	August	17
12	Leads	B	Sept	17
13	Leads	B	Oct	17
14	Leads	B	Nov	17
15	Leads	B	Dec	17
16	Leads	B	Jan	8
17	Leads	B	Feb	8
18	Leads	C	July	29
19	Leads	C	August	29
20	Leads	C	Sept	29
21	Leads	C	Oct	29
22	Leads	C	Nov	29
23	Leads	C	Dec	29
24	Leads	C	Jan	29
25	Leads	C	Feb	29
26	Leads	D	July	31
27	Leads	D	August	31
28	Leads	D	Sept	31
29	Leads	D	Oct	31
30	Leads	D	Nov	31
31	Leads	D	Dec	31
32	Leads	D	Jan	31
33	Leads	D	Feb	31
34	Leads	E	July	36
35	Leads	E	August	36
36	Leads	E	Sept	36
37	Leads	E	Oct	36
38	Leads	E	Nov	36
39	Leads	E	Dec	36
40	Leads	E	Jan	24
41	Leads	E	Feb	24
42	Leads	F	July	114
43	Leads	F	August	114
44	Leads	F	Sept	114
45	Leads	F	Oct	114
46	Leads	F	Nov	114
47	Leads	F	Dec	114
48	Leads	F	Jan	57
49	Leads	F	Feb	57
50	Leads	G	July	28
Sheet3

(the HTML poster only allows 50 rows but you get the idea)

 

[cmcreynolds]

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.

 

[cmcreynolds]

Is there a way to make Leads a column, New Enrollments a column (field), Returning Students a column, etc.?

 

[sheetspread]

Yes, the pivot table, but don't store your data in this format obviously