Forum Discussion
EXCEL
I WANT TO TRANSPOSE RECORDS IN A SINGLE COLUMN TO BE IN ROWS IN EXCEL
EACH RECORD IS 15 ROWS LONG
I HAVE DONE THE FIRST 3 IN MY DEMO FILE
I DO NOT WANT TO DO THEM ONE AT A TIME !!!! ITS TO TEDIOUS
PLEASE HELP
9 Replies
- TOO TEDIOUS I WANT TO DO ALL 84 IN THE FILE AT ONCE
USING ARRAY POSSIBLY OR OFFSETFirst, please turn off caps lock on your keyboard. Using that consistently is regarded as yelling on message boards. You don't need to do it.
Second, I used INDIRECT function to do a partial reorganization of your data. [Who did that in the first place? And why?]
But it's clear that there are inconsistencies in the way it's laid out, and I am not needed to do the clean up, nor fully qualified. It looks to me as if new sets of data were inserted from time to time. That "1st" at the very top is not, I"m thinking actually part of the first record. Rather, it's saying these next few records are the first batch of records. And then a little later on there's a "2nd" and a "3rd" and so forth. Each of those disrupts the regular pattern of 15 rows to a record.
So it's up to you to delete those rows.
Once you've done that, the formula I've written should turn all 84 records into 84 columnar records. After which the TRANSPOSE function will work as previously advised.
- sorry not meant to yell
cant delete rows
a abc
b
c
a abc
b
c
a abc
b
c
is my desired output
i do not want to do 1 at a time
you included formula ????
- Wants to do all what 84?
- IN MY ATTACHED FILE
1260 ROWS 84 RECS X 15 ROWS
- Easily, you can use the TRANSPOSE function
Let say you have the following data in Column A1 to A5: 299, 538, 281, 853, 843 respectively and you want to transpose all of them to be in B1 to B5
1. Select B1: B5
2. Type in =TRANSPOSE(
3. Select A1 to A5
4. Hit the CTRL + SHIFT + Enter...
You have the data transposed. Just apply the same steps in your data and you get the result.
If this reply answers your question, kindly do accept as the Best Response 😀
