How to copy a cell depending on adjacent cell content?

English
, ,
#1

Hi,
I need your help to do the following : I have a spreadsheet (extracted from my bank account) with mainly two columns : one is the “Paid to” (say column B), and the other is the amount of the expense (say column C).
My goal is to extract all expenses related to food. So I would like to test column B contents (searching for specific words inside the cell) and if one keyword is found, then copy adjacent C cell to another cell, say the adjacent cell in F column. Logic would be like the following :

IF cell Bx CONTAINS one of the keywords, THEN COPY Cx to Fx

I am not sure if this is possible with functions like FIND and IF ? or should I need VBA macro to achieve this ?

Thanks you for your help.

Show a value if a text cell includes a specific string
Range 3 columns with the common cells in the 3 columns
#2

Do you mean something like entering in cell F2 the formula =IF(ISERROR(SEARCH(F$1,$B2));"";$C2) where F1 contains the word “food” you are searching for? Your next word might be “shoes” in cell G1. Search function is case insensitive so probably better than Find. Sample spreadsheet. Cheers, Al

#3

Thank you Al, looks very good, but what if I need different keywords to match for the same column ? For my case, I have to rely on shop’s names, this is why I need the test to succeed with several keywords like “Market”, “Bio”, “Farm”, etc (all is about food, and this is what I want to control)… I can manage several columns with your solution, it is already good, but ideally testing several words would better fit my need…
Cheers, Pled

#4

This question on OpenOffice might help. Cheers, Al

#5

Ok, it seems to be possible according to your link, but much more complicated ! At least too much form me, I will keep with you solution, as I can easily get the sum of several columns. KISS principle ! :wink:
Thank you again. Pled

#6

EDIT: No valid answer, because date in column B is more complex than expected.

Given data in B2, C2 and below, and keywords in column A of Sheet2.

You can use the following formula in F2 and below =IF(ISNA(VLOOKUP(B2;$Sheet2.A:A;1;0))=0;C2).

See sample file.

A:A is for all column A.

See LibreOffice Help on VLOOKUP, ISNA, and IF.

Tested with LibreOffice 6.4.7.2 (x64); OS: Windows 10.0.

Add Answer is reserved for solutions. Click edit (below your question) to add more information. Thanks.

#7

Hi,
This looks good (I like keyword list), but keywords from list must match cell content, which is not my use case: my cells are for example “CARTE 28/11 KONKARBIO CV CONCARNEAU” or "“CARTE 16/12 SCARABEE BIOCOOP CESSON SEVIGNE” from which I need to identify “BIO” inside cell (what I call keyword actually). And same for other keywords…
Thank you for your help.

#8

Modifying the @Leroy’s sample I think it’s what you want according to your last comment.

=IFERROR(SEARCH(".*"&TEXTJOIN(".*|.*";1;$Sheet2.A$1:A$15)&".*";B2)*C2;0)

I think achieves what you want, creating a regular expression with the words to search.

#9

Take care that Enable regular expressions in formulas must be checked (menu Tools - Options… - LibreOffice Calc - Calculate.

#10

That’s perfect ! Exactly what I was looking for. Thank you so very much.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.