I've got the following data
---------A---B---C------D
1 JOE 23 24 18
2 KIM 56
3 BOB 19 40
4 LIZ 79 41 42
5 AMY 88 32
I want to do something so that I get the following from the same data:
----------A---B---C------D
1 JOE 23
2 JOE 24
3 JOE 18
4 KIM 56
5 BOB 19
6 BOB 40
7 LIZ 79
8 LIZ 41
9 LIZ 42
10 AMY 88
11 AMY 32
So basically moving everything in columns C and D into column B whilst keeping it's related column A data.
Does anyone have any idea how? I'm stumped!
2007-02-13 03:24:02 · 5 answers · asked by joey_mook 1 in Science & Mathematics ➔ Mathematics
Not a very clean answer but it should work.
In Cell E1, make the formula
= A1 & " " & B1
(The " " puts thee space between the values)
Cell F1:
= A1 & " " & C1
Cell G1:
= A1 & " " & D1
Then, copy cells E1 to G1 and paste for all the other rows.
This will produce all your results you require but in 3 columns.
Copy all of Column E and do a Paste Special of Values onto a new sheet/document. Repeat above for columns F and G, but put them at the bottom of the cells you have just pasted in the new document. If you then select the entire new column you have created and sort by name, it should give the results you require.
2007-02-13 03:39:22 · answer #1 · answered by Marky 6 · 0⤊ 0⤋
Duplicate the information so that you have at least one line for each line of information.
JOE 18 23 24
KIM 56
.
.
JOE 18 23 24
KIM 56
.
.
then sort by name
JOE 18 23 24
JOE 18 23 24
.
KIM 56
KIM 56
.
then in E count the number of occurences of the name
E1:=1
E2: if(A2=a1,e1+1,1)
E3: if(A3=a2,e2+1,1)
JOE 18 23 24 1
JOE 18 23 24 2
KIM 56 _ _ 1
KIM 56 _ _ 2
then in F put the score for that occurence
F1: =(b1*(e1=1) + c1*(e1=2) + d1*(e1=3))
JOE 18 23 24 1 18
JOE 18 23 24 2 23
KIM 56 _ _ 1 56
KIM 56 _ _ 2 _
copy the data and paste special values only
sort by F.
Delete the lines where F is zero
2007-02-13 04:16:18 · answer #2 · answered by kinvadave 5 · 0⤊ 0⤋
Can't think of a function to do it either. I'd be fascinated if anyone can come up with one. In the meantime, I suspect it's cut 'n paste time.
2007-02-13 03:34:14 · answer #3 · answered by champer 7 · 0⤊ 0⤋
The only way I know how to do that is by hand... I don't know that there's really a formula or function that will do it.
2007-02-13 03:26:43 · answer #4 · answered by Mathematica 7 · 0⤊ 0⤋
Use the formula "Left" then 6.
This will give you all characters from the left and in six,
2007-02-13 03:51:33 · answer #5 · answered by Loader2000 4 · 0⤊ 1⤋