If text exists

[Kevin J Prince]

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
--
"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

 

[jlepack]

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

 

[Kevin J Prince]

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.

=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

--
"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

 

[jlepack]

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

 

[Kevin J Prince]

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?

=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

--
"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

 

[jlepack]

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

 

[jlepack]

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.

 

[Kevin J Prince]

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.

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.

--
"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