Feb 13 2021 10:23 PM
Hello,
I have a panel data table. The value of an item is recorded each year. The column has values corresponding to the year ( table 1). So, there are 11 years and 11 different columns. I want to have only 2 columns, all years in 1 column (table 2). Simple transpose doesn't work. I also tried sumifs but didn't succeed. Please advise.
Feb 13 2021 11:11 PM
Feb 14 2021 04:05 AM
As Mr. Wyn Hopkins said, Power Query is the most suitable tool for this.
Here is some information and help for the first steps from Microsoft.
Combine data from multiple data sources (Power Query)
* Knowing the Excel version and operating system would also be an advantage.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
Feb 14 2021 06:31 AM
Unpivoting tables is core functionality of Power Query.
It is also possible to effect such a transformation using formulas but that involves detailed messing about with row and column indices. It is most elegantly performed in the latest 365 version of Excel.
= UNPIVOT(array, item, year)
which is a named version of
= LAMBDA(arr,Itm,Yr,
LET(
m, ROWS(arr),
n, COLUMNS(arr),
k, SEQUENCE(m*n),
YearLst, INDEX(Yr, 1+MOD(k-1,n)),
itemLst, INDEX(Itm, 1+QUOTIENT(k-1,n)),
CHOOSE({1,2,3}, itemLst, YearLst, ELEMENT(itemLst, YearLst) ) )
)(array, item, year)
The key elements of the calculation are the index 'k' numbering the output array and, from that, the MOD and QUOTIENT formulae that identify the row and column of element required from the array.
That should probably convince you to use PQ but it might be worth knowing other possibilities exist.
Feb 14 2021 07:11 AM
Just a glorified INDEX but specific to a particular array
= LAMBDA(itm,yr,
LET(
r0, XMATCH(itm, item),
c0, XMATCH(yr, year),
INDEX(array, r0, c0) )
)
The intention is that the Lambda function should be the Name the user knows the array by and the reference within the formula is to the range that the array occupies. That gives a more standard function notation than the INDEX formula.