Fill empty cells with the value from the left-most cell until it reaches a non-empty cell

[D_Y]

Hello

I have a data set that looks like this:

	Townhomes			Condos
	City 1	City 2		City 1	City 2
2020	10%	2%		8%	1%
2021	5%	5%		29%	8%

I'm trying to get the data into this format (only did the first two rows as example):

Year	Housing Type	City	Value
2020	Townhomes	City 1	10%
2020	Townhomes	City 2	2%

In terms of solution, as a first step I was thinking of copying and pasting the values "Townhomes" to the right until it reaches the next non-empty cell, in this case "Condos". Then do the same thing with "Condos". 

	Townhomes	Townhomes	Townhomes	Condos	Condos
	City 1	City 2		City 1	City 2
2020	10%	2%		8%	1%
2021	5%	5%		29%	8%

Not sure how to actually do this without losing the actual data by using the Transpose tool. 

Thanks!

[AngelosPachis · Accepted solution]

Hi @D_Y ,

Is your input an Excel file? If yes, checking option no.6 of the configuration pane 

should change your data structure as that shown below, with Records 1 and 2 containing your headers

You can then adopt a logic similar to that displayed below to get to your desired output

Hope that helps,

Angelos

[ArtApa]

Hi @D_Y - Here is a possible solution:

[D_Y]

 Thanks @AngelosPachis !

[D_Y]

Thanks so much @ArtApa !