[Solved] Check cell against array content - display if TRUE

Discuss the spreadsheet application

[Solved] Check cell against array content - display if TRUE

Postby morphingstar » Tue Apr 30, 2019 1:25 am

I have some strings which I want to handle according to some part of the string.
Example of a string table:
stringaaaa.mp4 morechars
stringbbbbb.mp3 morechars123
stringccccccc.txt morechars123xy

I want a table which only displays strings which contains one of .mp3 .mp4 .txt .
Layout
ColC contains the raw strings starting at row 101 (>100 entries), in the example below the row numbers are different.
Array sits on range d91:d93, when created.
ColAC will be used for the resulting string. The original and any assistive columns will be on the left of ColZ.
Goal
Strings which do not include one of the array's contents should not be displayed, or will be filtered out with AUTOFILTER.

So far I developed this formula:
=MID($C36;$I36;SEARCH($H36;$C36)) copied from row 36, ColG
Table
ColC ColD Descr. ColE ColF ColG func.result ColH Col_i
abcdefghijkl.mp3 12345qwertz Len - Search abcdefghijkl. .mp3 1
abcdefghijkl.mp4 12345qwertz Len - Search abcdefghijkl. .mp4 1
abcdefghijkl.txt 12345qwertz Len - Search abcdefghijkl. .txt 1
string ColC control cells for develop. result reference Start point of MID
=MID($C36;$I36;SEARCH($H36;$C36)) copied from row 36
As you see, I need a reference for every row: .mp_ or .txt .
If the reference was inside an array (range) and the check would use _OR_ _-LOOKUP_ _IF_ then the 2 reference columns could be eliminated. An array could be easily modified, no need to run through every entry row.

Comments:
The MID start point reference is optional. In this application it would be(include) the first character of the string. I included SP for completion and portability.
abcdefghijkl. ignore the dot at the end of the result. Ii is not important, just an uncorrected accuracy error in the MID-string definition.

I checked program HELP for _OR_ _-LOOKUP_ _IF_ , but failed to build what I need.

The result might be helpful to some people who design tables for their files.
The web page eliminates spaces, the columns are not displayed as I wrote them.


aligntable: eliminated
Table= eliminated

Code:
Code: Select all   Expand viewCollapse view
So far I developed this formula:
[b]=MID($C36;$I36;SEARCH($H36;$C36)) [/b]     copied from row 36, ColG
[b]Table[/b]
ColC                                          ColD Descr.            ColE   ColF     ColG func.result  ColH          Col_i
abcdefghijkl.mp3 12345qwertz   Len - Search                  abcdefghijkl.     .mp3       1
abcdefghijkl.mp4 12345qwertz   Len - Search                  abcdefghijkl.     .mp4       1
abcdefghijkl.txt 12345qwertz   Len - Search                  abcdefghijkl.     .txt       1
string ColC                                control cells for develop.             result                 reference  Start point of MID
                                                                                                 =MID($C36;$I36;SEARCH($H36;$C36))      copied from row 36


SEE ATTACHED PICTURE, as CODE is messing up too, though less than _table_.
Thanks for solutions.
Attachments
picforooforum_table-with-array-ref.jpg
Last edited by Hagar Delest on Thu May 02, 2019 7:54 am, edited 1 time in total.
Reason: tagged solved
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 67
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: check 1 cell content against array content - display if

Postby robleyd » Tue Apr 30, 2019 1:54 am

I want a table which only displays strings which contains one of .mp3 .mp4 .txt .


As SEARCH is capable of using regular expressions, can you not use a regex as your search term? Something like

Code: Select all   Expand viewCollapse view
SEARCH("[mp.][txt]"; $C36)


Note; my regex-fu is weak, so that may not be syntactically correct; I'm sure someone will enlighten us :D
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - 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: 2886
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Check 1 cell content against array content - display if

Postby morphingstar » Wed May 01, 2019 9:03 pm

I found a solution which serves - even simpler than originally thought.
See attached screen copy which shows results and formulas.
For me the problem is solved, without the need for an array.
The result is sortable
Tks for looking.

Click the picture to view full size. Copy returns useless version, I don't know why.
Here is a text copy of the various cells, if it helps.
Col C, Row 52 apply top left

File_List fixed pos $: . 3 ex: .wav =4 File Name file type added info
abcdefghijklx.mp3 asdfgqwer Len & Find + MID 27 14 13 abcdefghijklx mp3 asdfgqwer
abcdefghijklx.mp4 asdfgqwertz 15 abcdefghijklx mp4 asdfgqwertz
abcdefghijkl.txt yxcvbnm abcdefghijkl txt yxcvbnm abcdefghijkl.txt yxcvbnm
fixed pos. $ name / type split dotfree type control above combine
LEN($C53) FIND($E$52;C53) LEFT(C55;FIND($E$52;C55)-1)
LEN($C54)-FIND($E$52;C54)
MID(C55;FIND($E$52;C55)+1;$F$52)
RIGHT($C55;LEN($C55)-FIND($E$52;C55)-$F$52)
CONCATENATE($H55;$E$52;$I55;$J55)
Col stat C, Row start 52
Attachments
stringextraction20190430a_150dpi16col.jpg
oo calc table - variable length string extraction
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 67
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests