 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006If text exists
Hi All, Not been around for a while so not even sure how to ask this... I have a spreadsheet consisting of 3 columns, column 1 is text, col 2 is a pseudo date, Like (aug 23) no year shown. colum 3 is a numeric value which can be either + or - shown as 12.35+ or as a negative 345.82- What I need to do is scan column 1 for a certain word, if it is there I need to copy column 3 into column 5 so that I can calculate the total values associated with the string. There is a possibility that there can be anything from 400 - 5000 rows in the sheet. I know it can be done, I just don't know how. Can anyone throw me a quick bit of information on this please. Regards Kevin  Signature"I live in my own little world. But it's OK. They know me here." = = = = Kevin J Prince http://www.1and1.co.uk/?k_id=5257507 Put this little baby into E1 (or wherever, and change the numbers to suit) and fill down. =IF(A1="Joe",C1,"") I assumed that your text that you were looking for is static. If it comes from somewhere else then change "Joe" with the cell address that you need it to refer to. Cheers, Jason Lepack > Hi All, > [quoted text clipped - 23 lines] > Kevin J Prince > http://www.1and1.co.uk/?k_id=5257507 Thanks for the reply The text is generally a word amongst many so it's some form of string manipulation I think I need to scan somehow for the text. E.g. Col 1 | Col 2 | Col 3 | Col4 | Col 5 ------------------------------------------------------------------------- ------------------- Banjo ERT 267 abcdef | Aug-23 | 24.56- Rupert angle 322 romeo | Jul-22 | 175.56+ Bloomer BG74 angle | May-23 | 14.23- etc My search or formulae might be that I want to catch 'angle' and put the value into the col 5. So the above case would have only two values transferred to col 5. Does that make sense. ? Regards Kevin >Put this little baby into E1 (or wherever, and change the numbers to >suit) and fill down. [quoted text clipped - 35 lines] >> Kevin J Prince >> http://www.1and1.co.uk/?k_id=5257507 Signature"I live in my own little world. But it's OK. They know me here." = = = = Kevin J Prince http://www.1and1.co.uk/?k_id=5257507 How about this? =IF(ISERROR(FIND($E$1,A2)),"",C2) With this I've set it so that your value to find is in E1 and all your data goes from A2 to whereever. Put this formula in E2 and fill down. Type the value that you want to find into E1 and watch as your numbers jump into E column. Cheers, Jason Lepack > Thanks for the reply > [quoted text clipped - 65 lines] > Kevin J Prince > http://www.1and1.co.uk/?k_id=5257507 Jason, Thanks your a pal with that. I'm not sure how it all works but it does. I now only have on thing I need to sort out. The fact that the column 3 has either a negative or a positive sign after the value. This means that I can't =SUM(E2:Exxx). Might as well ask as I'm on a roll GRIN... Example would be I want to sum all the values transferred from the column c, but have that in say G3. Is it possible.? Cheeky or what... Many many thanks for your help on this, Regards Kevin >How about this? > [quoted text clipped - 78 lines] >> Kevin J Prince >> http://www.1and1.co.uk/?k_id=5257507 Signature"I live in my own little world. But it's OK. They know me here." = = = = Kevin J Prince http://www.1and1.co.uk/?k_id=5257507 Is your data in columns three formatted as numbers? If not that is probably your problem. I'm on my way home, when I get there (about an hour) I'll help with the soplution. Cheers, Jason Lepack > Jason, > Thanks your a pal with that. I'm not sure how it all works but it does. [quoted text clipped - 104 lines] > Kevin J Prince > http://www.1and1.co.uk/?k_id=5257507 This code will change all of your numbers into proper numbers. Eg. 24.56- will be changed to -24.56 Select the cells that you would liek to change and run this code. If there is a "+" on the right side then the number will be positive. If there is a "-" on the right side then the number will be negative. If there is neither on the right side then the number will not be touched. After this your =sum(E:E) will work excellently. Sub changeToNumbers() Dim r As Range, sign As String For Each r In Selection If Not r.Value = "" Then sign = Right(r.Value, 1) If sign = "+" Then r.Formula = "=" & Left(r.Value, Len(r.Value) - 1) ElseIf sign = "-" Then r.Formula = "=-" & Left(r.Value, Len(r.Value) - 1) End If End If Set r = r.Offset(1, 0) Next End Sub Cheers, Jason Lepack PS. you cheeky bugger. ;) > Is your data in columns three formatted as numbers? If not that is > probably your problem. [quoted text clipped - 113 lines] > > Kevin J Prince > > http://www.1and1.co.uk/?k_id=5257507 Jason, excellent, that's done the job very well thank you very much for you help. THANK YOU All the best for the season to you an your family. Regards Kevin >This code will change all of your numbers into proper numbers. > [quoted text clipped - 150 lines] >> > Kevin J Prince >> > http://www.1and1.co.uk/?k_id=5257507 Signature"I live in my own little world. But it's OK. They know me here." = = = = Kevin J Prince http://www.1and1.co.uk/?k_id=5257507
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.