Extract Information from a File

[Pestomania]

Hi!

I am trying to identify a way to export information that is not in a standard format as it can change number of rows by process name. The only "standard" is when the "Process Name" restarts in the row, that is a new row record.

The script would need to run until there is no more processes in Column A because it could be 3 processes or 15 processes.

I would need it to run and create a table like the second upload below.

I tried to map below from Sheet 1 to Sheet 2:

For processes:

Sheet1 Column & Name	Sheet2 Column & Name	Format Changes?
Column A: Process Name	Column A: Process Name	No Format Change
Column A: Description	Column S: Description	No Format Change
Column B: Maximum Tolerable Downtime	Column AG: Maximum Tolerable Downtime	No Format Change
Column B: Impact if process is inactive	Column AD: Disruption Impact	No Format Change
Column B: Dependencies	Column M: Dependencies	No format change
Column B: Input	Column AA: Inbound	Remove bullet point (wingding Q)
Column B: Output	Column AB: Outbound	Remove bullet point (wingding Q)
Column B: Apps / System	I am going to skip and go below to explain that as each is a new row
Column B: Comments	Column AR: Comments	If the word "Comments" is the response, change to "N/A" but otherwise no format change
Column A: Process Name Work Around	Column AF: Disruption Workaround	All of the work arounds need to be as "wrap text" with no bullet points so that it all fits into one cell.

For apps / systems:
All Apps/Systems are a "new line" directly under the process name

Sheet1 Column & Name	Sheet2 Column & Name	Format Changes?
Column B: Application/System	Column A: Process Name	Indent once
Column A: Process Name (the "parent process" tht the app belongs to)	Column G: Parent Process	Input the Process Name that the app is supporting into Column G
Column C: RTO	Column AN: RTO	No Format Change
Column D: RPO	Column AO: RPO	No Format Change

Sheet1:

Process Name File.xlsx
	A	B	C	D
7	PROCESS NAME	Description and Data
8	PROCESS NAME:	Maximum Tolerable Downtime (MTD): TimeFrame
9		Impacts if process is inactive: Impact 1
10	Process 1	Dependencies: Dependency 1, Dependency 2, Dependency 3
11		Input:
12		Q All site functions
13		Output:
14	Process Description:	Q All site functions, External
15		Critical Applications and Systems:
16	Description 1	Application / System	RTO	RPO
17		App 1	Timeframe	Timeframe
18		App 2	Timeframe	Timeframe
19		App 3	Timeframe	Timeframe
20		App 4	Timeframe	Timeframe
21		App 5	Timeframe	Timeframe
22		App 6	Timeframe	Timeframe
23
24
25		Comments:
26
27		N/A
28	Process 1 WORK AROUND
29	Workplace Outage:
30	Q Outage 1 Workaround
31	System Outage:
32	Q Outage 2 Workaround
33	Q Outage 3 Workaround
34	Q Outage 4 Workaround
35	Q Outage 5 Workaround
36	Limited Staff Availability:
37	Q Outage 6 Workaround
38	PROCESS NAME:	Maximum Tolerable Downtime (MTD): TimeFrame
39		Impacts if process is inactive: Impact 2
40	Process 2	Dependencies: Dependency 1, Dependency 2, Dependency 3
41		Input:
42		Q Operations converted into a forecast. Requirements handed down from Corp.
43		Output:
44	Process Description:	Q Finance
45		Critical Applications and Systems:
46	Description 2	Application / System	RTO	RPO
47		App 1	Timeframe	Timeframe
48		App 2	Timeframe	Timeframe
49		App 3	Timeframe	Timeframe
50		App 4	Timeframe	Timeframe
51		App 5	Timeframe	Timeframe
52
53
54		Comments:
55		Comments
56	Process 2 WORK AROUND
57	Workplace Outage:
58	Q Outage 1 Workaround
59	System Outage:
60
61	Q Outage 2 Workaround
62	Limited Staff Availability:
63	Q Outage 3 Workaround
64	PROCESS NAME:	Maximum Tolerable Downtime (MTD): Time Frame
65		Impacts if process is inactive: Impact 3
66	Process 3	Dependencies: Dependency 1
67		Input:
68		Q Master Schedule (ICT), Operations (provides realization / expectations) & non-labor cost expectations.
69		Q Program Management (provides oversight, align ops with reality), contacts
70	Process Description:	Output:
71		Q Finance, Business Operations Leaders.
72	Description 3	Critical Applications and Systems:
73		Application / System	RTO	RPO
74		App 1	Timeframe	Timeframe
75		App 2	Timeframe	Timeframe
76		App 3	Timeframe	Timeframe
77		App 4	Timeframe	Timeframe
78		App 5	Timeframe	Timeframe
79
80
81		Comments:
82		Comments
83	Process 3 WORK AROUND
84	Workplace Outage:
85	Q Outage 1 Workaround
86	System Outage:
87	Q Outage 2 Workaround
88	Limited Staff Availability:
89	Q Outage 3 Workaround
Sheet1

Sheet2:

Process Name File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	Process Name	Tabs Needed	BIA Input Complete Date	Concatenate Process Name	WBS	Sorting Column	Parent Process	Process Type	Site Name	Building Location	Program Name	Category Holder?	Dependencies	Contact Name	Organization	Work Phone	Cell Phone	Email Address	Description	Alt Resource Available	Alt Resource Description	Single Point-of-Failure	Equipment Identifier	Criticality	Other Location	Column Number / Section	Inbound	Outbound	Peak Period	Disruption Impact	Internal or External	Disruption Workaround	Maximum Tolerable Downtime	Qty in Use	Qty Spare	Human Resources Required	Recovery Strategy	Digital Location	Physical Copy Location	RTO	RPO	Date Obtained	Confirmed Process Number	Comments	Ancestor Count	Created By	Created	Modified By	Latest Comment	Modified	Title
2	Process 1												Dependency 1, Dependency 2, Dependency 3						Description 1								All Site Functions	All site functions, External		Impact 1		Workplace Outage:  Outage 1 Workaround System Outage:  Outage 2 Workaround  Outage 3 Workaround  Outage 4 Workaround  Outage 5 Workaround Limited Staff Availability:  Outage 6 Workaround	Timeframe											N/A
3	App 1						Process 1																																	Timeframe	Timeframe
4	App 2						Process 1																																	Timeframe	Timeframe
5	App 3						Process 1																																	Timeframe	Timeframe
6	App 4						Process 1																																	Timeframe	Timeframe
7	App 5						Process 1																																	Timeframe	Timeframe
8	Process 2												Dependency 1, Dependency 2, Dependency 3						Description 2								Operations converted into a forecast. Requirements handed down from Corp.	Finance		Impact 2		Workplace Outage:  Outage 1 Workaround System Outage:  Outage 2 Workaround Limited Staff Availability:  Outage 3 Workaround	Timeframe											Comments
9	App 1						Process 2																																	Timeframe	Timeframe
10	App 2						Process 2																																	Timeframe	Timeframe
11	App 3						Process 2																																	Timeframe	Timeframe
12	App 4						Process 2																																	Timeframe	Timeframe
13	App 5						Process 2																																	Timeframe	Timeframe
14	Process 3												Dependency 1						Description 3								Master Schedule (ICT), Operations (provides realization / expectations) & non-labor cost expectations.	Finance, Business Operations Leaders.		Impact 3		Workplace Outage:  Outage 1 Workaround System Outage:  Outage 2 Workaround Limited Staff Availability:  Outage 3 Workaround	Timeframe											Comments
15	App 1						Process 3																																	Timeframe	Timeframe
16	App 2						Process 3																																	Timeframe	Timeframe
17	App 3						Process 3																																	Timeframe	Timeframe
18	App 4						Process 3																																	Timeframe	Timeframe
19	App 5						Process 3																																	Timeframe	Timeframe
Sheet2