Oct 13 2021 05:39 PM - edited Oct 13 2021 05:41 PM
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,
Tom
Oct 13 2021 09:52 PM
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.
Oct 13 2021 10:07 PM - edited Oct 13 2021 10:09 PM
Solution@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.
Oct 14 2021 02:30 AM
transponer es tu función (transpose)
Si no lo incorpora tu excel el ARRAY calculation tendras que hacer el antiguo CSE (Control+Shift + Enter)
Saludos
Julian Ch
Oct 19 2021 12:17 PM
Oct 19 2021 12:19 PM
Oct 13 2021 10:07 PM - edited Oct 13 2021 10:09 PM
Solution@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.