Parse out data with merged cells and several headings in one column

[cubbear]

Hello Community,

I have a raw data in which displays as merged cells and several headings within one column.

Here is what I have

Key	Text	Repair Time
123	Job 1	Days
		1
		Hours
		3
234	Job 2	Days
		0
		Hours
		4

I have been attempting to use If with vlookup and hlookup to accomplish this by using the Key as the unique identifier and then hlookup for either "Days" or "Hours".

Key	Text	Days	Hours
123	Job 1	1	3
234	Job 2	0	4

My goal is to setup the table and be able to refresh it when I get a new data dump. All ideas are welcomed.

Thank you in advance!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Nothing in your post appears to show merged cells. Could you post the sample data and expected results again with XL2BB?

 

[cubbear]

I appreciate the suggestion Peter.

MrExcel_parse_20220211.xlsb
	E
12
Raw

MrExcel_parse_20220211.xlsb
	E
24
End Resut

 

[Peter_SSs]

Thanks for updating your version information.

To use XL2BB you need to select the range in your sheet that you want to show before clicking 'Mini Sheet' and then pasting in the forum.

 

[cubbear]

Thanks for your patience

MrExcel_parse_20220211.xlsb
	A	B	C
1	Key	Text	Repair Time
2	123	Job 1	Days
3			1
4			Hours
5			3
6	234	Job 2	Days
7			0
8			Hours
9			4
Raw

MrExcel_parse_20220211.xlsb
	A	B	C	D
1	Key	Text	Days	Hours
2	123	Job 1	1	3
3	234	Job 2	0	4
End Resut

 

[Peter_SSs]

Thanks for the XL2BB sample data.

See if this is any use. The result is not a formal table but will automatically update (without manually refreshing) when new data is added in columns A:C
Where I have 100 (& 99) in my formulas you just need to make sure that in place of 100 you use a number at least as big as your data will ever be (& one less than that in place of the 99)

22 02 16.xlsm
	A	B	C	D	E	F	G	H	I
1	Key	Text	Repair Time			Key	Text	Days	Hours
2	123	Job 1	Days			123	Job 1	1	3
3			1			234	Job 2	0	4
4			Hours
5			3
6	234	Job 2	Days
7			0
8			Hours
9			4
10
Convert Layout

Cell Formulas
Range	Formula
F2:G3	F2	=FILTER(A2:B100,A2:A100<>"")
H2:I3	H2	=FILTER($C3:$C100,$C2:$C99=H1)
Dynamic array formulas.

 

[cubbear]

Thank you for your patience Peter! The formula works brilliantly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.