MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Help


Posted by Sean on October 12, 2000 2:30 AM

Hi,
I hope someone here can help me. I am new to Excel, and have very little experience in it, also due to time constraints I am unable to find a decent book.

My situation is this.
In column A I have lots of text values
In column B I have numbers (a time) that are related to each individual text.

I need to go through column A and for every different value I need to total their times. Say "Text£" appears in column A 4 times I need to total all 4 times, and then do the same with "Text$" every time it appears.

I then need to compare the values of all the times and return the text which has the highest associated time value.

I realise this is possible if you know what "Text" values can appear, but what if this is not known? Is there and VB code that can be used ?

Any suggestions on here or to my email would be GREATLY appreciated!!

Thanks,
Sean.


Posted by Celia on October 12, 2000 7:10 AM


Sean
Based on the assumption your data is in rows 1 to 10 :-

Put the following formula in C1 and fill down to C10 :-
=SUMIF(A$1:A$12,A1,B$1:B$12)

The following should then return the text in column A with the highest column B total :-
=LOOKUP(LARGE(C$1:C$12,1),C$1:C$12,A$1:A$12)

Celia

Posted by Celia on October 12, 2000 7:14 AM

Correction

Correction :- Should read rows 1:12 and fill down to C12

Posted by Sean on October 13, 2000 8:12 AM

Re: Correction

Thanks very much!
I'll give this a try, I hope it works.....
Sean