excel transposition specific

New Contributor

Hello, I have a specific problem where I need to put values from a collumn into different collumns based on the category - see file attached.

The values from collumn H should be put into collumns I:O on the row that corresponds to the specific district (value in collumn D).

The problem is, there is no specific number of rows after which a new district starts - sometimes it is 3, sometimes 4 etc, based on the number of categories a specific district contains. So I cannot use a standard formula that would transpose values every fourth row for example.

A formula would probably be preferred as I have never done macros.
I hope I made the question clear enough and would appreciate your advice a lot!

Thank you,





5 Replies


Remove the merged cells. Fill the empty cells with the text from above.

Create a pivot table:

Subdistrict in rows area.

Land Tenure in columns area.

Total Area of the Farm Land (Ha) in values area.


best response confirmed by uhertommy (New Contributor)

@uhertommy I would structure the data as demonstrated in the "rawData" tab in the attached workbook. Then create a pivot table (PivotTable tab) on that raw data. It will produce the overview you described, instantly. Adjust the underlying data? No problem, just press the Refresh button on the Data ribbon and the pivot table will be updated. No formulae or VBA needed. Just standard Excel functionality. If you are not familiar with pivot tables, search for "pivot table excel" on-line and you'll find all you need to know.


Edit: Didn't see @Detlef Lewin 's answer before I posted mine. It does exactly what he described.

@Detlef Lewin 

transponer es tu función (transpose)

Si no lo incorpora tu excel el ARRAY calculation tendras que hacer el antiguo CSE (Control+Shift + Enter)



Julian Ch

Thank you very much for the advice to all of you! A pivot table was a good solution and I learned a new skill.
Thank you very much for the advice! A pivot table was a good solution and I learned a new skill.