Right now, I have this:
name data1 data2 data3 data4 data5
But I'd like this:
name data1
name data2
name data3
name data4
name data5
The "Transpose" function doesn't quite do it correctly; any ideas ion how to proceed? I'd like to use a function if possible
Is there some reason other than formatting this has to be done using functions? You could always transpose the data, move the name to the left of data1, and then double click the bottom right corner of the "name" cell to auto-fill down for the last data point.
Spread the love, add to the Rep
"None of us are as smart as all of us."
Is it always the same amount of data in each row (i.e. 5) or does it vary with each name?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
same amount of rows
sorry --- same amount of data for each "name"
With 5 data points in each row, in H1 copied down
Formula:
Please Login or Register to view this content.
In I1 copied down
Formula:Please Login or Register to view this content.
Does that work for you?
Works PERFECTLY !!! Thank you for your help!!!
OK --- I spoke too soon. Please see attached file. The formulas work only for 5 student IDs and/or 5 classes picked; how can I modify the formula so that there can be up to 1000 student IDs (column A) transposed with 15 classes picked? (please see attached file)
In N2 copied down, you need to modify the divisor and the starting row (2). According to your table B:M = 12 columns so
=IFERROR(INDEX($A$2:$A$1000, INT((ROWS($A$1:$A1)-1)/12)+1),"")
In O2 copied down,
=IFERROR(INDEX($B$2:$M$1000, INT((ROWS($A$1:$A1)-1)/12)+1, MOD(ROWS($A$1:$A1)-1,12)+1),"")
As I mentioned originally, this assumes the same number of data points for each student which does not look to be the case so there will be blank spaces or zeros.
Not sure what your final aim is. If you are copying the newly organized data to a new sheet, it would be easy to "Paste as Values" and then filter on the zeros and delete those rows. If you want the blank rows removed automatically, here is a solution that uses ARRAYED Formulas.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
In Q2 copied down is this arrayed formula
In R2 copied down is this arrayed formulaFormula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
OK the previous solution did the trick ---- thanks so very much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks