[Solved] Clear Cells if equal to 1st Cell (text)

[turbo_mapper]

Hello

i have a large sheet for game language localization where the columns are:
A keyword
B English
C to .. other languages

in the past i used the English text if there was no other translation but now since it is one combined sheet
i want to remove/clear all cells in each row where it equals the english content to minimize file size.

for example:
keyword;English;German;Spanish;Russian;French
item.standard.bow.04;Hunter´s Bow;Jagdbogen;Hunter´s Bow;Охотничий лук;L''arc du Chasseur'
should become:
item.standard.bow.04;Hunter´s Bow;Jagdbogen;;Охотничий лук;L''arc du Chasseur'

thanks in advance
turbo

[Hagar Delest]

Hi and welcome to the forum!

Can you upload a small sample file (see How to attach a document here)?
It will be much better to see how your data are handled.

[turbo_mapper]

Thank you for the Welcome.

[Hagar Delest]

An actual excerpt of the file would help (just few rows).
I guess that the cells have been filled in manually and then it is a one shot operation.
In such case, I would apply a conditional formatting testing if the cell equals the same row cell in column 8 and applying a colored background for example. For all those highlighted cells, just select them and delete their content.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the ✎ button if your issue has been fixed.

[turbo_mapper]

... For all those highlighted cells, just select them and delete their content.

the file has 2500+ rows and 18 languages,
so is it possible to get all highlighted cells automatically "selected"?
otherwise it would take hours to select them by hand

[Hagar Delest]

Sadly there is no way to select cells based on their conditional formatting status.
For so many data, you should write a macro that checks for each row the content of the cells from C to Q columns. If the content is equal to the cell in column B (of the same row), then delete the content of this cell.
Any AI should be able to give you such code I guess. Snippets may help also in the macro section of this forum.

[cwolan]

Just an ugly idea:

1. in the copy of sheet select the range of the cells to be checked
2. in the input line enter the ugly formula:

   Code: Select all

   =IF(INDIRECT("Sheet1."&CELL("ADDRESS"))=INDIRECT("Sheet1.B"&ROW());"";INDIRECT("Sheet1."&CELL("ADDRESS")))
   

3. accept it with Alt+Enter

See the attached file (I used the data seen on the screenshot provided by turbo_mapper).
⠀

[Hagar Delest]

Ah, indeed, another method is to use a helper sheet: do the comparison in that sheet and then copy and paste the values back to the original sheet.
Only formulas will be used, no macro needed and then no formula remaining either (after deletion of the helper sheet).

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the ✎ button if your issue has been fixed.

[MrProgrammer]

localization_cleanup.jpg (127.08 KiB)

I ignore pictures of data. I'm not going to retype it all so I can test a solution. For any topic you create, you are more likely to get a quick solution when you attach a spreadsheet file to your initial post. When you don't attach, it's common that the first reply will be a request for your file.

Localization_short.csv

Thank you for the CSV file. I imported it into Calc. It's only one row of data, but I will show you a solution for it. My method does not need a copy of the sheet. All the work is done in place. You will want to ensure you have a backup of your spreadsheet before making major changes to it. Read section 6. Saving your data and making backups in Ten concepts that every Calc user should know.

The following steps create formulas to remove the duplicate entries.
• Select cells C2 to Qn, where n is the last row of your data
• Edit → Find & Replace → More options
• Select Search in → Formulas and check both Current selection only and Regular Expressions
• Search for → .+        Period and Plus Sign are regular expression characters
• Replace with → =IF("&"=INDIRECT("RC2";0);"";"&")      Ampersand is a regular expression character too
• Replace All
• I recommend unchecking Current selection only and Regular expressions, before clicking Close.

The following steps convert those formulas back into static data.
• Click cell Qn, where n is the last row of your data
• Hold Shift and click cell C2. Release Shift. C2 must be the active cell.
• Edit → Copy
• Edit → Paste Special → Selection → Text (all other selections unchecked)
    → Options → None → Operations → None → Shift cells → Don't → OK

=IF(INDIRECT("Sheet1."&CELL("ADDRESS"))=INDIRECT("Sheet1.B"&ROW());"";INDIRECT("Sheet1."&CELL("ADDRESS")))

I find the R1C1 syntax of INDIRECT helpful in situations like this. RC is current cell address. RC2 is current row, column B.
=IF(INDIRECT("Sheet1!RC";0)=INDIRECT("RC2";0);"";INDIRECT("Sheet1!RC";0))

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Alex1]

Code: Select all

=IF(INDIRECT("Sheet1."&CELL("ADDRESS"))=INDIRECT("Sheet1.B"&ROW());"";INDIRECT("Sheet1."&CELL("ADDRESS")))

A simpler formula: Enter the following in Sheet_Result.C2 and copy it to C2:F7.

Code: Select all

=IF(Sheet1.C2=Sheet1.$B2;"";Sheet1.C2)

[turbo_mapper]

Just an ugly idea:...

You call it ugly, I call it brilliant!
Your solution works 100% and took me about 10 seconds.

Thanks to all of you for your help and additional solutions, i was almost going to code some C# tool to manage this

Edit:
btw.. clearing the doubles shrunk the file size from 963kb down to 531kb.
Even more before i used separate files for each language which summed up to 1.6mb.
With the now merged files i can dynamically search what languages are available including their completion ratio

[cwolan]

@MrProgrammer
Thank you for the interesting solution and the tip about the R1C1 notation.

@Alex1
Thank you for independently demonstrating the very ugliness of my formula.

[MrProgrammer]

Sadly there is no way to select cells based on their conditional formatting status.

I found a way using the STYLE function. This idea is not really practical for this case, but may be of help to me in the future. A key point is that because turbo_mapper's file is a CSV, all the cells have the Default style, and existing user-specified cell styles don't need to be maintained.

The idea is to use a conditional formatting formula to apply a special style to the cells which need to be cleared. It's applied to one cell first, then that conditional format is copied to the other cells. Once the style is applied to the cells by the IF function, the conditional formatting is no longer needed to maintain the style, so the conditional formatting is deleted. Now the Find and Replace dialog can select all the cells with the special style. The backspace key clears them, as turbo_mapper wants. Then the cells are reset to the Default style. The special style could be deleted too.

• Format → Styles and Formatting → Default → Right-click → New → Organizer → Name → Foo → Background → Yellow → OK
• Select cell C2
• Format → Conditional formatting → Condition 1 → Formula is → IF(C2=$B2;STYLE("Foo")) → Cell style → Foo → OK
• Edit → Copy
• Select C2:Qn, where n is the last row of data
• Edit → Paste Special → Selection → Formats (all other selections unchecked)
    → Options → None → Operations → None → Shift cells → Don't → OK
• Format → Conditional formatting → Condition 1 → Uncheck → OK
• Edit → Find & Replace → More options → Search for styles → Search for → Foo → Find All → Close
• Press backspace key
• Format → Styles and Formatting → Default → Double-click → OK