How to combine this data?.please...
Posted by Ronald on November 01, 2000 4:06 AM
let say i have cell A1:E1
all the cell are for grade from A to E
what i like to do is is to combine all the data
in F1..lets say like this :
A1=>A B1==>A C1==>B D1==>A E1==>
what i like to do is in cell F1..the data shows like
this : 4A 1B
and one more thing when there is C and and D grade
in cell A1:E1..the result will not shown in cell F1
is it possible.thank you
Posted by Tim Francis-Wright on November 01, 2000 7:28 AM
It's possible, all right.
Try this in F1 and copy it down as you need:-
=IF(COUNTIF(A1:E1,"C")+COUNTIF(A1:E1,"D")>0,"",COUNTIF(A1:E1,"A") & "A " & COUNTIF(A1:E1,"B") & "B")
HTH
Posted by Ronald on November 01, 2000 8:02 AM
Thanks Thomas but there still problem...
when there is C grade..i'm also want it to be appear
in cell F1 but the formula you gave me can't do this..
could you help me fix it.coz i'm a newby to excel.thanks
Posted by Tim Francis-Wright on November 01, 2000 8:24 AM
Re: Thanks Thomas but there still problem...
Let'as assume A1 to E1 are A B C B D
If what you want is for only the A and B grades
to show (1A 2B), use
=COUNTIF(A1:E1,"A") & "A " & COUNTIF(A1:E1,"B") & "B"
If you need the C and D grades also to show (1A 2B 1C 1D), use
=COUNTIF(A1:E1,"A") & "A " & COUNTIF(A1:E1,"B") & "B ") & COUNTIF(A1:E1,"C") & "C " & COUNTIF(A1:E1,"D") & "D"
Posted by Ronald on November 01, 2000 8:37 AM
You are brilliant Tim..regards.it works like a spell..hehhe
Posted by Ronald on November 01, 2000 8:45 AM
Your formula has solve my problem..but i'm wondering 1 more thing..please......Tim
this is more difficult i guess
what i like to do now is
when there is no grade etc. A,B,C,D,E
the value is not shown..example
the student get 2A,1B and 1D..
the data in F1 will show : 2A 2B 1D
instead of 2A 2B 0C 1D 0E..
could you help me one more time
Tim..please..and thanks
Posted by Ronald on November 01, 2000 8:47 AM
Sorry Tim..this is the final question. i get mixed up..here it is..
this is more difficult i guess
what i like to do now is
when there is no grade etc. A,B,C,D,E
the value is not shown..example
the student get 2A,1B and 1D..
the data in F1 will show : 2A 1B 1D
instead of 2A 1B 0C 1D 0E..
could you help me one more time
Tim..please..and thanks
Posted by Tim Francis-Wright on November 01, 2000 9:04 AM
Re: Sorry Tim..this is the final question. i get mixed up..here it is..
This should work for Cell F1. It's long and
inelegant, but so are a lot of things...
=IF(COUNTIF(A1:E1,"A")>0,COUNTIF(A1:E1,"A") & "A ","") & IF(COUNTIF(A1:E1,"B")>0,COUNTIF(A1:E1,"B") & "B ","") & IF(COUNTIF(A1:E1,"C")>0,COUNTIF(A1:E1,"C") & "C ","") & IF(COUNTIF(A1:E1,"D")>0,COUNTIF(A1:E1,"D") & "D ","") &IF(COUNTIF(A1:E1,"E")>0,COUNTIF(A1:E1,"E") & "E ","")
I've tried this and found it to work. HTH!
Posted by Ronald on November 01, 2000 9:11 AM
what can i say..u r really not a magician don't u..it workkkkkkkkss...heheh..thank Tim..see you around
