Copy paste TITLE to empty cell, beside subordinate

[xdenama]

I have a Group Title at top for every group. How to fill Group Title (red text) besides subordinate for every group as shown below.

Book1.xlsm
	C	D	E	F	G	H	I
1
2	ALPHA	Lime				ALPHA	Lime
3		Orange				ALPHA	Orange
4		Papaya				ALPHA	Papaya
5		Manggo				ALPHA	Manggo
6		Corn				ALPHA	Corn
7	BETA	Banana				BETA	Banana
8		Corn				BETA	Corn
9		Apple				BETA	Apple
10	CHARLIE	Grape				CHARLIE	Grape
11		Orange				CHARLIE	Orange
12		Lemon				CHARLIE	Lemon
13		Strawbery				CHARLIE	Strawbery
14		Cucumber				CHARLIE	Cucumber
Sheet1

 

[sandy666]

with Power Query

Column1	Column2
ALPHA	Lime
ALPHA	Orange
ALPHA	Papaya
ALPHA	Manggo
ALPHA	Corn
BETA	Banana
BETA	Corn
BETA	Apple
CHARLIE	Grape
CHARLIE	Orange
CHARLIE	Lemon
CHARLIE	Strawbery
CHARLIE	Cucumber

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillDown = Table.FillDown(Source,{"Column1"})
in
    FillDown

 

[xdenama]

with Power Query

Column1	Column2
ALPHA	Lime
ALPHA	Orange
ALPHA	Papaya
ALPHA	Manggo
ALPHA	Corn
BETA	Banana
BETA	Corn
BETA	Apple
CHARLIE	Grape
CHARLIE	Orange
CHARLIE	Lemon
CHARLIE	Strawbery
CHARLIE	Cucumber

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillDown = Table.FillDown(Source,{"Column1"})
in
    FillDown

Done, thank you again.

 

[sandy666]

You are welcome

 

[xdenama]

with Power Query

Column1	Column2
ALPHA	Lime
ALPHA	Orange
ALPHA	Papaya
ALPHA	Manggo
ALPHA	Corn
BETA	Banana
BETA	Corn
BETA	Apple
CHARLIE	Grape
CHARLIE	Orange
CHARLIE	Lemon
CHARLIE	Strawbery
CHARLIE	Cucumber

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillDown = Table.FillDown(Source,{"Column1"})
in
    FillDown

On this process Power Query automatic create a "query" and a "Worksheet". How to specific naming the new sheet, like "data" instead of "Sheet147". Or load to specific worksheet that already exist.

 

[sandy666]

if your query is ready then select Close&Load to....

then you should see Queries and choose your place: sheet and cell where you want to load table
right click on your query/table

then

As you can see Load to has option to load query table into existing worksheet and specified cell

hth