I have one column with 5000+ rows of 'grouped' data (every 9 rows has 9 different data points for for an entity).
I want to, sequentially, transpose them to columnar format with each data point having its own column.
Using this 'OFFSET' formula, I was able to get it to work for the first group, but that's about it. Any insights on a formula I can use to achieve this?
=OFFSET($A$1,(ROW()-2)*1+INT((COLUMN()-3)),MOD(COLUMN()-9,1))
Hi,
OFFSET (and INDIRECT, another popular choice for this type of solution) are volatile functions and so should be avoided if possible.
=INDEX($A:$A,9*(ROWS(A$1:A1)-1)+COLUMNS($A1:A1))
and copy across and down.
Regards
That worked! * solved! Had no idea about the volatility, thank you!
Last edited by 2deJulio; 12-07-2018 at 05:57 PM. Reason: forgot to mark solved
You're welcome!
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks