Page 1 of 1

[Solved] If B1 contains 'peter', then....

Posted: Sat Nov 13, 2010 7:07 am
by fpi2010
Hello experts,

Can someone help me generating a macro for me.

For column B & column C check each cell value. for example,
if B1 cell contains the word "peter" AND C1 cell contains the word "valid" then put the value "10" in A1
if B2 cell contains the word "tom" AND C2 cell contains the word "invalid" then put the value "20" in A2

basically, I am testing two columns B & C's values, and if they match my condition, then put a value in column A

a macro in libreoffice basic would be great.

Thanks a lot.

Andrew

Re: Help me with this script - if B1 contains 'peter', then.

Posted: Sat Nov 13, 2010 8:14 am
by Zizi64
I think, you not needed MACRO for this task.

You have those TWO names only?
If the answer is YES, just type that FORMULA in A1 :
=IF(AND(B5="peter";C5="valid");10;IF(AND(B5="tom";C5="invalid");20;"do not match"))
and then copy-paste the formula into another row of column A.

Re: Help me with this script - if B1 contains 'peter', then.

Posted: Sat Nov 13, 2010 9:10 am
by fpi2010
Hello zizi64 again. :mrgreen:

well, the issue is that I want to check if the cell contains the word, for example,

"peter is at home" in the cell, then "peter" is contained in the cell "peter is at home"

you know what I mean?

Re: [Calc] If B1 contains 'peter', then....

Posted: Sat Nov 13, 2010 6:15 pm
by Zizi64
Yes, that is possible to solve with BASIC MACRO "INSTR()" function.

from OOo HELP:
InStr Function [Runtime]
Returns the position of a string within another string.
The Instr function returns the position at which the match was found. If the string was not found, the function returns 0.
Syntax:
InStr ([Start As Long,] Text1 As String, Text2 As String[, Compare])

Code: Select all

Example:
Sub ExamplePosition
Dim sInput As String
Dim iPos as Integer
sInput = "Office"
iPos = Instr(sInput,"c")
print iPos
end sub
But: you need use (for example )"true" and "false" words instead "valid" and "invalid" because the word "invalid" contains the word "valid".

And:
These words: "peter "; "peter,"; "Peter."; Petersen"; etc. occur in your text?
Maybe you need search the word "Peter[space]" for exact matching.

Re: [Calc] If B1 contains 'peter', then....

Posted: Sat Nov 13, 2010 7:30 pm
by Zizi64
There is an idea:

Code: Select all

Function MyTestCondition(A_string, A1word, A2word, B_string, B1word, B2word, CaseParStr as string) as integer

	If CaseParStr = "OFF" then CasePar = 1 else CasePar = 0

	if ((Instr(1, A_string, A1word, CasePar)<>0) and (Instr(1, B_string, B1word, CasePar)<>0)) then
		MyTestCondition = 20
	elseif 	((Instr(1, A_string, A2word, CasePar)<>0) and (Instr(1, B_string, B2word, CasePar)<>0)) then
		MyTestCondition = 10
	else 
		MyTestCondition = 0
	endif
		 
End function
Mytestcondition.ods
(11.19 KiB) Downloaded 785 times

Re: [Calc] If B1 contains 'peter', then....

Posted: Sun Nov 14, 2010 4:23 pm
by Villeroy
case sensitively:
=IF(ISNUMBER(FIND("Peter";A1));"Hi Peter";"Missing Peter")
case insensitively:
=IF(ISNUMBER(SEARCH("Peter";A1));"Hi Peter";"Missing Peter")

Re: [Calc] If B1 contains 'peter', then....

Posted: Fri Oct 02, 2015 8:38 pm
by buck1942
Thanks to the one posting a solution to this Subject (5 YEARS ago ;) ), regarding how to do an "instr/contains"...

Buck

Re: [Calc] If B1 contains 'peter', then....

Posted: Sun Oct 04, 2015 11:19 am
by Villeroy
buck1942 wrote:Thanks to the one posting a solution to this Subject (5 YEARS ago ;) ), regarding how to do an "instr/contains"...

Buck
Same solution with all commonly used spreadsheets since 30 years.

Re: [Calc] If B1 contains 'peter', then....

Posted: Mon Nov 30, 2020 8:01 am
by cul
I have a follow u question to how I can expand on this...

I need:

if cell A1 DOES contain EITHER "LP", "12"", "10"", or "7"" return "Record" (yes those are record sizes so the extra " denotes inches)
if cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "CD" return "CD" (should also allow CDr)
If cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "Cass" return "Cassette"
If cell A1 DOES NOT contain "LP", "12"", "10"", "7"", or "CD" but DOES contain "DVD" return "DVD"

It all needs to fit in one cell...all help appreciated as always!

I've tried working with
=IF(ISNUMBER(SEARCH("LP";A1));"Vinyl";"Not Found") and
=IF(ISERR(SEARCH("LP",A1)),"Not found","Vinyl")
but I'm not getting very far when I try to add in other options.

Thank you!

Re: [Calc] If B1 contains 'peter', then....

Posted: Mon Nov 30, 2020 8:12 am
by Zizi64
Please upload a real, ODF type sample file here.

Re: [Calc] If B1 contains 'peter', then....

Posted: Mon Nov 30, 2020 8:23 am
by cul
Sorry, I thought I was in calc help. I didn't realise this was Macros. Uploading ODS anyway!

I'm trying to populate sheet 1 column K using data from sheet 2 column F.