MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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