need help sorting a list in excel

truthandjustice_2

I have a list of items numeric in excel and they are all in A column e.g. (0,1,0,0,0,1) and I need them in fields A-f is there anyway this can be done without me going through all 28570 of them manually?

Thank you so much for your help. I need this for Uni

KimYeovil

Paste Special... > check 'transpose' in options box

.
.
.

[Deleted User]

KimYeovil wrote: »

Paste Special... > check 'transpose' in options box

.
.
.

Would have been my answer too

More4me

I think clarification is required in order to understand what you trying to achive, from your example combinition of numbers are not unique set to use IF formula, also you already have list/data in column A therefore impossible to spread data across A to F unless another worksheet is used, consider columns B to G on the same worksheet. What would be helpful to assist you would be to let us know what is the driving factor for the data to be allocated across the columns.

truthandjustice_2

More4me wrote: »

I think clarification is required in order to understand what you trying to achive, from your example combinition of numbers are not unique set to use IF formula, also you already have list/data in column A therefore impossible to spread data across A to F unless another worksheet is used, consider columns B to G on the same worksheet. What would be helpful to assist you would be to let us know what is the driving factor for the data to be allocated across the columns.

The numbers relate to data on astrophysics and relate to the star size location and classification etc. That is why I need them in A through F with correct subject headings in A1,B1 etc.

[Deleted User]

truthandjustice wrote: »

The numbers relate to data on astrophysics and relate to the star size location and classification etc. That is why I need them in A through F with correct subject headings in A1,B1 etc.

Did you read post 2?! Select the data, edit -> copy, select cell A1, edit -> paste special, tick transpose...

truthandjustice_2

I tried the transpose and it just puts the sets of numbers across the rows rather than how it is now down the columns.

what I have as an example from A1 - A4 is:

(A1) 0,0,0,0,1,1 I need it to read A1 0 B1 0 C1 0 D1 0 E1 1 F1 1
(A2) 0,6,0,0,1,1 I need it to read A2 0 B2 6 C2 0 D2 0 E2 1 F2 1
(A3) 1,3,0,7,0,1 I need it to read A3 1 B3 3 C3 0 D3 7 E3 0 F3 1
(A4) 3,0,1,0,1,4 I need it to read A4 3 B4 0 C4 1 D4 0 E4 1 F4 4

What I need to do is split that into Column's A through F hopefully without having to manually doing it. As I say I have 28570 lines of data and to do it manually would take forever.

More4me

Am I correct in thinkink that the last none zero number decides which column to be populated?

magpiecottage

If I read this correctly, you have 28570 cells of data in column A each with 6 columns of data separated by comma.

There are various ways of doing this. I would probably take a fairly long winded approach by starting with the first row.

Assuming no headers, then the first cell is A1.
In B1 enter "=right(A1,9)"
In C1 enter "=right(A1,7)"
and so on to F1
In G1 enter"=left(a1,1)
Copy G1
Highlight H1:L1 and paste.
Highlight B1:L1 and copy
Go to B1
Press the shift Key (keep pressing it until I say to release it)
Press the left key
Press the Ctrl key and keep pressing that (as well as the shift key)
Press the down key
release the down key and Ctrl key but keep pressing the shift key
Press the right key until you have columns B to L highlighted.
Release both the right and shift key.
Press the Ctrl key and hold it while you press the V key.

You should now have the data you want to work with in columns G to L.

However, it is reliant on what is in columns A to F. If you want to get rid of them you will need to copy cells G1 to L 28570 and paste them as values rather than formulae. If I remember correctly that is done by alt then e then s then v in excel.

If using Open Office use semicolons instead of commas in the formulae and copy as values is Ctrl, shift and V altogether. You then get a menu come up where you untick "paste all" and tick numbers.

Stompa

Select all your data in column A, go to 'Data > Text to Columns...', choose delimited, and specify a comma as your delimiter. Job done.

Sambucus_Nigra

Stompa wrote: »

Select all your data in column A, go to 'Data > Text to Columns...', choose delimited, and specify a comma as your delimiter. Job done.

This one.....