how to transpose data from one column into multiple rows?

Hi,

I have tex data in one column. It represents strings of POS tags assigned to each word in coherent sentences, e.g.:

nv
j
pp
n1
n2
v

n1
pp
n2
v


The strings are separated by one-cell space and are of variable length as they represent real sentences from n actual text.

How to transpose these strings from a column into multiple rows so that a sentence string goes into a row at a time?
My knowledge is limited to the simplest use of the transpose function which does not work for it transposes the entire column into one row only.
Thanks,
G

Hi Arlette,

I suppose no need for attaching my real data as Pete has already provided a solution. I guess there might be other ways to deal with that but, as his way is working, I'm happy with that .

Thanks for response!
Gabriele

Originally Posted by arlu1201

You will need to explain some more as its not very clear.

Why not attach a sample file showing how your data looks like and how you want it to look like?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

In the attached file I put your example data in column A starting with A2, and this formula in B2:

Formula:

=IF(A2="",IF(A3="","",INT(MAX(B$1:B1))+1),ROUND(B1+0.01,2))

This was then copied down to row 20 (i.e. beyond your data), and it sets up a simple sequence which will allow up to 99 tags in one sentence.

Then I put this formula in D2:

Formula:

=IFERROR(INDEX($A:$A,MATCH(ROWS($1:1)-1+COLUMNS($A:A)/100,$B:$B,0)),"")

and this was then copied across (to column L, but it could be further if required), and the formulae from D2:L2 were then copied down (to row 5, but could be further), to give you the results you require.

Hope this helps.

Pete

Cool! Even I understand .
Many thanks,
Gabriele

Originally Posted by Pete_UK

In the first formula I gave you, this part:

ROUND(B1+0.01,2)

just adds 0.01 to the previous value, so it forms a simple counter as it is copied down the column, but this is part of an IF statement, so it only does that if A2 is not blank. If it is blank, then the formula will return a value which is the next whole number by means of this part:

INT(MAX(B$1:B1))+1

(i.e. find the largest number of all the previous cells, take the whole number part of it, and then add 1), but it will only do this if the next cell in column A is not blank - in this case it will return a blank, as that represents the end of the series of tags. So, we end up in column B with a series of numbers, where the decimal values are a count of the number of tags we have in a particular sentence, and the whole number part represents the sentence number, and a whole number with no decimal values indicates a new sentence. That means that we then need another formula which will make use of rows and columns to enable us to extract data in the correct sequence - we effectively want the tags from column A which have this pattern of numbers:

0.01, 0.02, 0.03, 0.04, 0.05, and so on
1.01, 1.02, 1.03, 1.04,
and so on.

That is what the second formula does - it uses ROWS($1:1)-1 to form the integer part, (and this will increment on successive rows), together with COLUMNS($A:A)/100 to form the decimal part (which will increment on successive columns), and the MATCH function attempts to find where this number exists in column B. If there is an exact match, the INDEX function will return the corresponding value from column A, but if not the IFERROR function ensures that a blank cell is returned instead.

Hope this helps.

Pete