Forum Discussion
EXCEL
USING ARRAY POSSIBLY OR OFFSET
First, 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 ????