Transpose multiple values and delete rows?

[Daeve]

Hi,

I have a large spreadsheet (several thousand lines) in Excel 2007 that is in the format:

A 0.66
A 0.77
A 0.88
B 0.44
B 0.33
C 0.55
C 0.66
C 0.88
C 0.99
C 0.44

Column 1 variables may be anything from single values to 20+

I need it in the format:

A 0.66 0.77 0.88
B 0.44 0.33
C 0.55 0.66 0.88 0.99 0.44

Currently i am manually copying, paste special and transposing the values from the same column 1 so they are all in a single row for each value then selecting all the duplicate rows with single values and deleting them (move rows up). This is very time consuming and prone to errors.

Any help to automate this would be greatly appreciated, thank you.

[pike]

Hi Daeve
Welcome to the forum
This is a very popular question. If you search the forum you will find many different answers. one way....

Please Login or Register  to view this content.

[Daeve]

Hi Pike,

That's great, thanks - it does exactly what I need it to do! I did try a search on "transpose" and read a few pages of threads, but I didn't see the exact same problem (usually they were slightly more complex). Many thanks for your patience.

[killerando]

Thank you very much for the reply. It works perfectly.
But one more question: Do you think is there any function that enables you to do the opposite?

I mean, we have a list like:
emai1l@address.com A1 A2 A3
emai2l@address.com A4 A5
emai3l@address.com A2 A3 A5
...................

And the output should be
emai1l@address.com A1
emai1l@address.com A2
emai1l@address.com A3
emai2l@address.com A4
emai2l@address.com A5
emai3l@address.com A2
emai3l@address.com A3
emai3l@address.com A5

I've trying to add a special character between A1 ~ A2 and then paste in word, replace ~ by a manual line break, then copy all back to excel, control + g, special, blanks, spaces, = control up... etc.

Any solution should be first class as usual.

Best regards.

[shashi4041]

Hi,
Can u help me to know how to utilize this above program in excel to transpose columns.I shall be thankful to you.

[arlu1201]

Shashi,

Do not post your email id, you will be flooded with spam emails.

Also,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.