[Solved] If cell contains x, add y to a different cell.

Discuss the spreadsheet application

[Solved] If cell contains x, add y to a different cell.

Postby ev888 » Sun Dec 09, 2018 4:41 am

I need to updated about 2500 products in a CSV. If any cell in a column contains the word 'Boy' (lets just call this Column E), I need to add 'Boy' to all the cells in Column D. Note that it doesn't have to be just the single word 'Boy'; if it appears anywhere in column E (such as Infant Boy, Junior Boy, etc) I need to add 'Boy' to column D. What would be the correct formula for this? I found a similar thread but it was a bit more complex, and I'm not familiar with cell formulas.
Last edited by ev888 on Sun Dec 09, 2018 8:17 pm, edited 1 time in total.
OpenOffice 6.0.1.1 (x64) on Windows 10
ev888
 
Posts: 2
Joined: Sun Dec 09, 2018 4:30 am

Re: If cell contains x, add y to a different cell.

Postby robleyd » Sun Dec 09, 2018 8:19 am

Spreadsheets can either have values or formulae in a cell, not both. So if D already contains data, what you are asking can't be done directly.

You would need to use a helper column containing the formula. Something along the lines of 'IF E contains boy THEN this cell = the value in D with boy appended; ELSE this cell = the value in D'. Depending on exactly what you need, you may have to manipulate the results.

If D is empty, then it can be done directly in D. In either case the exact formula will depend on several things.
1. Is the value you are matching case insensitive?
2. If appending to D, is there any other text needed, e.g. a space separating the appended data?
3. Is the appended data to be exactly the same as what is found in respect of case?
4. Is the search value always going to be Boy?

Possibly there are other things I've not considered!

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
[Tutorial] Absolute, relative and mixed references
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2567
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: If cell contains x, add y to a different cell.

Postby ev888 » Sun Dec 09, 2018 3:42 pm

Here is the spreadsheet itself.

Image

There is nothing in column D, nor is 'boy' case sensitive. No spaces are needed. The search value is not always going to be 'boy', I will need to do one for 'girl' as well.
OpenOffice 6.0.1.1 (x64) on Windows 10
ev888
 
Posts: 2
Joined: Sun Dec 09, 2018 4:30 am

Re: If cell contains x, add y to a different cell.

Postby Zizi64 » Sun Dec 09, 2018 4:29 pm

Here is the spreadsheet itself.

It is a picture only. Please opload your ODF type sample file here.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7814
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: If cell contains x, add y to a different cell.

Postby Lupp » Sun Dec 09, 2018 5:31 pm

ev888 wrote:If any cell in a column contains the word 'Boy' (lets just call this Column E), I need to add 'Boy' to all the cells in Column D.
I can't believe you actually mean this. It surely must be done per row.
Code: Select all   Expand viewCollapse view
=CHOOSE(1+ISNUMBER(FIND("boy";LOWER(E2)))+2*ISNUMBER(FIND("girl";LOWER(E2)));"";"Boy";"Girl";"Boy,Girl")
entered into D2 and filled down as far as needed should do.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2368
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 25 guests