How to link certain rows to specific headlines?

[gregthenovelist]

Hey Community,

I quickly skimmed through a few related articles on the forum, but I couldn't find a good answer. Maybe you can help me here!

I have transformed a pdf into an excel spreadsheet. I want to use this excel spreadsheet for some data analysis with STATA. Just that you understand what I am talking about, here is the pdf and the new excel spreadsheet:

To use the Excel data in STATA, I have to keep the structure of the pdf document somehow. So, I need a further section that, for instance, indicates that this is "Official Creditor" embedded in "of which interest arrears on LDOD" embedded in "short-term debt" embedded in "total debt stocks" under the category of "Albania". This is necessary so that I can compare them with a table from the year before and after.

One not very efficient option would be to introduce new columns to the left of the written part and indicate: Albania; Total Debt Stock; etc. and then merge them. This would be quite time-consuming as there are 147 countries on 10 pdfs.

Does anyone here know how I can most efficiently solve this problem? Is there potentially a "if - then" command that I could employ? How can I keep the "embeddedness" structure of the pdf in my excel spreadsheet? I would be very grateful for some ideas.

All the best,
Greg

 

[Toadstool]

Hi GregTheNovelist,

If the country entries are all the same format you could just handcraft a single non-ambiguous title template and copy and paste it into each country.

I don't know STATA but I would expect it wants key fields repeated. Maybe you could identify which are headings to be repeated and where they're needed? Something like this:

GregTheNovelist.xlsx
	C	D	E	F	G	H											S	T	U	V	W	X	Y
1																				Country		Phrase	Itself a sub-heading?
2																				USA		Use of IMF credit
3																				Albania		Short-term debt
4																				Chile		of which interest arrears on LDOD
5	Albania		Albania		Albania												1					Memo: principal arrears on LDOD	N
6	Albania		0														0					Disbursements
7	Albania		1. SUMMARY DEBT		1. SUMMARY DEBT												1					Principal repayments	N
8	Albania		TOTAL DEBT STOCKS (EDT)		TOTAL DEBT STOCKS (EDT)	..											1					Net flows on debt	N
9	Albania		Long-term debt (LDOD)		Long-term debt (LDOD)	..											1					Interest payments (INT)	N
10	Albania		Public and publicly guaranteed		Public and publicly guaranteed	..											1					Total debt service paid (TDS)	N
11	Albania		Private nonguaranteed		Private nonguaranteed	..											1					TOTAL DEBT FLOWS	N
12	Albania		Use of IMF credit		Use of IMF credit	0.0											1
13	Albania	Short-term debt:	Short-term debt: Short-term debt		Short-term debt	..											2
14	Albania	Short-term debt:	of which interest arrears on LDOD		of which interest arrears on LDOD	..											1
15	Albania	Short-term debt:	Short-term debt: Official creditors		Official creditors	..											2
16	Albania	Short-term debt:	Short-term debt: Private creditors		Private creditors	..											2
17	Albania	Short-term debt:	Memo: principal arrears on LDOD		Memo: principal arrears on LDOD	..											1
18	Albania	Short-term debt:	Short-term debt: Official creditors		Official creditors	..											2
19	Albania	Short-term debt:	Short-term debt: Private creditors		Private creditors	..											2
20	Albania	Short-term debt:	Memo: export credits		Memo: export credits	..											1
21	Albania	Short-term debt:	0														0
22	Albania	Short-term debt:	TOTAL DEBT FLOWS		TOTAL DEBT FLOWS												1
23	Albania	Short-term debt:	Disbursements		Disbursements	..											1
24	Albania	Short-term debt:	Short-term debt: Long-term debt		Long-term debt	..											4
25	Albania	Short-term debt:	IMF purchases		IMF purchases	0.0											1
26	Albania	Principal repayments:	Principal repayments		Principal repayments	..											1
27	Albania	Principal repayments:	Principal repayments: Long-term debt		Long-term debt	..											4
28	Albania	Principal repayments:	IMF repurchases		IMF repurchases	0.0											1
29	Albania	Principal repayments:	Net flows on debt		Net flows on debt	..											1
30	Albania	Principal repayments:	of which short-term debt		of which short-term debt	..											1
31	Albania	Principal repayments:	Interest payments (INT)		Interest payments (INT)	..											1
32	Albania	Principal repayments:	Principal repayments: Long-term debt		Long-term debt	..											4
33	Albania	Principal repayments:	IMF charges		IMF charges	0.0											1
34	Albania	Principal repayments: Short-term debt:	Principal repayments: Short-term debt: Short-term debt		Short-term debt	..											2
35	Albania	Principal repayments: Short-term debt:	Net transfers on debt		Net transfers on debt	..											1
36	Albania	Principal repayments: Short-term debt:	Total debt service paid (TDS)		Total debt service paid (TDS)	..											1
37	Albania	Principal repayments: Short-term debt:	Principal repayments: Short-term debt: Long-term debt		Long-term debt	..											4
38	Albania	Principal repayments: Short-term debt:	IMF repurchases and charges		IMF repurchases and charges	0.0											1
39	Albania	Principal repayments: Short-term debt:	Short-term debt (interest only)		Short-term debt (interest only)	..											1
BuildIt

Cell Formulas
Range	Formula
C5:C39	C5	=IF(ISNA(MATCH(G5,$V$2:$V$5,0)),C4,G5)
D5:D39	D5	=IF(ISNA(MATCH(G5,$X$2:$X$99,0)),D4&"",IF(INDEX($Y$2:$Y$9999,MATCH(G5,$X$2:$X$99,0))="N",G5&": ",D4&G5&": "))
E5:E39	E5	=IF(COUNTIF(G:G,G5)>1,D5&G5,G5)
S5:S39	S5	=COUNTIF(G:G,G5)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=BuildIt!$G$5	E5:E39, S5:S39, C5:D5
Print_Area	=BuildIt!$G$4:$Q$40	E5:E39, S5:S39

 

[gregthenovelist]

This is absolutely outstanding! That is exactly what I envisioned.

Ill see whether I can replicate this.

Thanks a lot for now!

All the best
Greg

 

[gregthenovelist]

One question @Toadstool: can I somehow save the functions to reapply them to the next country which would be in the exact same format? Or do I have to copy and past those functions for each country seperately?

Thanks a lot!

 

[Toadstool]

Thanks a lot for now!

You're welcome!

One question @Toadstool: can I somehow save the functions to reapply them to the next country which would be in the exact same format? Or do I have to copy and past those functions for each country seperately?

Thanks a lot!

As I said, I don't know STATA. It's very possible it will want a new row for each number (year) so the first thing I'd do is confirm what format is needed to upload to STATA.

You don't say how you currently have the data in Excel. 147 workbooks? 147 tabs (worksheets)? 10 worksheets with multiple countries on each? It's therefore difficult to make a recommendation on how to proceed.

If you have multiple countries per worksheet but each country has exactly the same number of rows of data with the same headings then personally I would just use the Country part of the formula but handcraft the non-ambiguous headings and paste them for each country.

To get the best answer for your question I think you need to give a precise format you want for STATA (including an example and what to do about the year headings omitted from your Excel sheet) and explain how your existing Excel format for the 147 countries, preferably with some XL2BB mini-sheets or a Dropbox/Google Drive upload.



P.S. I just found a boo-boo caused by artifacts in the sheet I sourced for my test data. I've amended below:

GregTheNovelist-V2.xlsx
	B	C	D	E	F	G									P	Q	R	S	T	U	V	W
1																			Country		Phrase	Itself a sub-heading?
2																			USA		Use of IMF credit
3																			Albania		of which interest arrears on LDOD	N
4																			Chile		Memo: principal arrears on LDOD	N
5	Albania		Albania		Albania																Disbursements
6	Albania		0																		Principal repayments	N
7	Albania		1. SUMMARY DEBT		1. SUMMARY DEBT																Net flows on debt	N
8	Albania		TOTAL DEBT STOCKS (EDT)		TOTAL DEBT STOCKS (EDT)	..									784.1						Interest payments (INT)	N
9	Albania		Long-term debt (LDOD)		Long-term debt (LDOD)	..									659.1						Total debt service paid (TDS)	N
10	Albania		Public and publicly guaranteed		Public and publicly guaranteed	..									644.2						TOTAL DEBT FLOWS	N
11	Albania		Private nonguaranteed		Private nonguaranteed	..									14.9
12	Albania	Use of IMF credit:	Use of IMF credit		Use of IMF credit	0.0									87.9
13	Albania	Use of IMF credit:	Use of IMF credit: Short-term debt		Short-term debt	..									37.1
14	Albania	of which interest arrears on LDOD:	of which interest arrears on LDOD		of which interest arrears on LDOD	..									3.8
15	Albania	of which interest arrears on LDOD:	of which interest arrears on LDOD: Official creditors		Official creditors	..									1.0
16	Albania	of which interest arrears on LDOD:	of which interest arrears on LDOD: Private creditors		Private creditors	..									2.8
17	Albania	Memo: principal arrears on LDOD:	Memo: principal arrears on LDOD		Memo: principal arrears on LDOD	..									33.4
18	Albania	Memo: principal arrears on LDOD:	Memo: principal arrears on LDOD: Official creditors		Official creditors	..									17.7
19	Albania	Memo: principal arrears on LDOD:	Memo: principal arrears on LDOD: Private creditors		Private creditors	..									15.8
20	Albania	Memo: principal arrears on LDOD:	Memo: export credits		Memo: export credits	..									25.9
BuildIt

Cell Formulas
Range	Formula
B5:B20	B5	=IF(ISNA(MATCH(F5,$T$2:$T$5,0)),B4,F5)
C5:C20	C5	=IF(ISNA(MATCH(F5,$V$2:$V$99,0)),C4&"",IF(INDEX($W$2:$W$9999,MATCH(F5,$V$2:$V$99,0))="N",F5&": ",C4&F5&": "))
D5:D20	D5	=IF(COUNTIF(F:F,F5)>1,C5&F5,F5)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=BuildIt!$F$5	D5:D20, B5:C5

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Connect a Heading with Subheadings
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.