Convert Merged cells to duplicates

[BrianGGG]

Hello. I have a worksheet that I don't have control over that looks like:

	A	B	C	D	E	F	G	H	I
a	b	c	d	e	f	g	h	i

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"]Category 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"]Category 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"]Category 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #F2F2F2"]Item 1[/TD]
[TD="bgcolor: #F2F2F2"]Item 2[/TD]
[TD="bgcolor: #F2F2F2"]Item 3[/TD]
[TD="bgcolor: #F2F2F2"]Item 4[/TD]
[TD="bgcolor: #F2F2F2"]Item 5[/TD]
[TD="bgcolor: #F2F2F2"]Item 6[/TD]
[TD="bgcolor: #F2F2F2"]Item 7[/TD]
[TD="bgcolor: #F2F2F2"]Item 8[/TD]
[TD="bgcolor: #F2F2F2"]Item 9[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet2​

In it, there is a variable number of categories that has a variable number of items. The categories are above the items and those cells are merged (another reason to hate merged cells).


What I need to do is create a single-cell array that contains the categories and items in two columns, but the categories should be Repeated rather than blank in the array.
The layout should be similar to the following (in array form of course):

	A	B
Category	Item
category 1	Item 1
category 1	Item 2
category 1	Item 3
Category 2	Item 4
Category 2	Item 5
Category 3	Item 6
Category 3	Item 7
Category 3	Item 8
Category 3	Item 9

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

</tbody>

Sheet2​

Any ideas? I'm struggling with a formula that will find "the non-zero value that is at or before the current column".



Thanks
BrianGGG

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Category[/td][td=bgcolor:#70AD47]Items[/td][td=bgcolor:#70AD47]Values[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Category 1[/td][td=bgcolor:#E2EFDA]Item 1[/td][td=bgcolor:#E2EFDA]a[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Category 1[/td][td]Item 2[/td][td]b[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Category 1[/td][td=bgcolor:#E2EFDA]Item 3[/td][td=bgcolor:#E2EFDA]c[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Category 2[/td][td]Item 4[/td][td]d[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Category 2[/td][td=bgcolor:#E2EFDA]Item 5[/td][td=bgcolor:#E2EFDA]e[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Category 3[/td][td]Item 6[/td][td]f[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Category 3[/td][td=bgcolor:#E2EFDA]Item 7[/td][td=bgcolor:#E2EFDA]g[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Category 3[/td][td]Item 8[/td][td]h[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Category 3[/td][td=bgcolor:#E2EFDA]Item 9[/td][td=bgcolor:#E2EFDA]i[/td][/tr]
[/table]

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Category"}, {"Column2", "Items"}, {"Column3", "Values"}})
in
    #"Renamed Columns"[/SIZE]

 

[BrianGGG]

Thanks, appreciated and what I want... however I need it to be solved in an Excel formula so that some of my calculations update real-time.

 

[sandy666]

I need it to be solved in an Excel formula

Sure
Have a nice day