Bug 109003 - Feature request: Auto filling cells by example / context of neighbouring cells, aka "Flash Fill"
Summary: Feature request: Auto filling cells by example / context of neighbouring cell...
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.4.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: AutoFill
  Show dependency treegraph
 
Reported: 2017-07-07 14:42 UTC by Gerry
Modified: 2018-08-24 21:27 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Gerry 2017-07-07 14:42:22 UTC
Please add a feature to Calc that auto-fills cells based on context of neighbouring cells and examples of correct entries by the user. Spreadsheet cell content usually follows a certain pattern and it is a frequent use case that part of this pattern is needed for further analysis in other cells. 

LibreOffice Calc should be able to automatically extract/autofill the relevant piece of the neigbouring cell content based on examples entered by the user. 

Example 1: I want to fill the last name in column B based on the full name in column A.

COLUMN A				COLUMN B
Bjoern Michaelsen			Bjoern
Armin le Grand				Armin
Katarina Behrens			Katarina
András Timár				?
Michael Meeks				?
Tomaž Vajngerl				?
Caolan McNamara				?
I give three examples and expect Calc to fill the remaining cells in column B correctly based on my example.


Example 2: I want to fill the first character of the last name in column B based on the full name in column A.

COLUMN A				COLUMN B
Bjoern Michaelsen			M.
Armin le Grand				G.
Katarina Behrens			B.
András Timár				?
Michael Meeks				?
Tomaž Vajngerl				?
Caolan McNamara				?
I give three examples and expect Calc to fill the remaining cells in column B correctly based on my example.


Example 3: I have variable names which follow a certain pattern in column A and I want to extract the middle part for column B:

COLUMN A				COLUMN B
aw16_treatA_total			treatA
ch28_ebsoZ_total			ebsoZ
adi43_treatZ_other			treatZ
ch45_treatAA_beso			?
zh123_midA_total			?
ph45_midZZ_other			?
sdi345_treatT_total			?
I give three examples and expect Calc to fill the remaining cells in column B correctly based on my example.


The requested enhancement corresponds to the "Flash Fill" feature introduced with MS Excel 2013. For more information, see: https://support.office.com/en-us/article/Use-AutoFill-and-Flash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464
Comment 1 Heiko Tietze 2017-07-08 06:32:28 UTC
Example 1
=LEFT(A1,FIND(" ",A1)-1)

Example 2
=MID(A2,SEARCH(" [^ ]*$",A2,1)+1,1) & "." (RegEx has to be enabled in Tools > Options; splitting names is always a tricky task and you likely fail like in this example)

Example 3
Either use Find() repeatedly parametrized with the first occurrence of the underscore or Search(). It's not completely clear if the string may contain more than two underscores.

What I want to demonstrate is that Calc has powerful methods to do what you want without automatic processing. The request is not defined precise enough to get implemented. My take: WORKSFORME. May Q decide.
Comment 2 Gerry 2017-07-08 08:31:02 UTC
Thanks Heiko for looking into this enhancement request. 

I am aware of the use of complicated functions that lead to "almost" the same results. However, what I point to is the automatic filling based on neighbouring cell content without using any function. An enhancement with substantially better user experience.

I suggest you to look at the video "Flash Fill" which you find in the web link of the bug report. This illustrates quite nicely the type of enhancement which I request. 

In my job, I use LibreOffice Calc and Excel 2013 everyday, for many different use cases. Working with data (numeric or text) the Flash Fill feature of Excel extraordinarily increases productivity. I request a similar enhancement to LibreOffice.
Comment 3 Buovjaga 2017-08-07 12:44:11 UTC
I think this might be something near: https://github.com/dennisfrancis/AutoFillMissingData
Comment 4 Gerry 2017-08-07 18:18:50 UTC
Hi Francis, I added you to this bug entry with regard to Auto filling cells by example. Could you please have a look at comment 3 where Buovjaga mentions your extension as potential solution to this bug? Thanks in advance.