How to split excel file

[joannasokolowska]

Dear colleagues,

I would like to ask you about how I can split one excel file which contains information about each person in the same TAB (excel file attached). There is always the same number of rows with information about the report which are not needed. 

Report
Number 23456
date 01.07.2020
Data for John Smith
		January	February	March
	module 1	103	203	0.5
	type 05	55	102	0.6
	module 3	80	100	0.5
Report
Number 01020
date 01.07.2020
Data for Anna Green
		January	February	March
	module 1	103	203	0.5
	module 2	55	102	0.6
	extract 3	80	100	0.5
	part 2	20	11	80

Desired outcome is like this (under each month summary data from all rows for each person) 

Name	January	February	March
Anna Green	258	416	81.6
John Smith	238	405	1.6

Thank you in advance,

Asia

[atcodedog05]

Hi @joannasokolowska 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[BrandonB]

Here you go! Workflow is attached. 

 

[BrandonB]

@atcodedog05 has a good solution as well! I leveraged a transpose and crosstab as an alternative to the Summarize tool to account for a dynamic set of months so that you don't have to add new ones as they are added. 

[joannasokolowska]

Thanks for a quick help! What would be the the workflow if below the name there is also some data ? 

Data for John Smith
street 24
		January	February	March
	module 1	103	203	0.5
	type 05	55	102	0.6
	module 3	80	100	0.5
Report
Number 01020
date 01.07.2020
Data for Anna Green
street 22
London
		January	February	March
	module 1	103	203	0.5
	module 2	55	102	0.6
	extract 3	80	100	0.5
	part 2	20	11	80

[atcodedog05]

Nope it wouldn't be we would need to modify.

[joannasokolowska]

How this should be modified ?:) 

[atcodedog05]

Hi @joannasokolowska 

Here is modified workflow. Just needed to change a condition.

Input:

Output:

Please check and let me know.

[BrandonB]

I made a tweak to my multi row formula to instead be:

IF Contains([Row-1:Report], "Data for ") AND !Contains([Report], "Data for ")
THEN [Row-1:Report]
ELSE [Report]
ENDIF

This should account for the changes

[joannasokolowska]

It's great! and if there is no text "data for" but simply name and surname different for each person. What other condition should be used in multi-row formula?