Re: [libreoffice-users] Calc - Matching Up Text Fields

Hi Errol,

Many thanks for the example.  I will play with this over the weekend to see
how it works.  I will drop a note (probably next week) to let you know how
it goes.  I've been blogging about my project here and there, so maybe this
will be another step I can blog about.  My post about connecting LOBase to
PostgreSQL is still the top-ranked story on my site, so hopefully I can add
some more LO action.  :-)


On Fri, Aug 2, 2013 at 6:23 AM, Errol Goetsch <errol@xe4.org> wrote:

> On 2013/08/01 11:31 AM, Don Parris wrote:
>
> > Unfortunately, one bank throws everything except for the date and the
> > amount into a single "description" column.  It would still be simpler if
> > they did not include the actual transaction date in that column, as it
> > means the description changes... well... daily.  Grrrr....
> >
> >
> > On Thu, Aug 1, 2013 at 12:27 AM, Brian Barker <b.m.barker@btinternet.com>
> > **wrote:
> >
> >  At 21:35 31/07/2013 -0400, Don Parris wrote:
> > >  Again, I just need to figure out how to extract that bit from the bank's
> > > > description.  Any thoughts on that?
> > > >
> > > >  As I mentioned, that depends on the precise possibilities in the bank
> > > data.  If the relevant data is always in the same columns of the relevant
> > > records, you can probably extract it using MID(); otherwise you may need
> > > to
> > > do a more complicated search through the text.
> > >
> > >
> > > Brian Barker
> > >
> > >  --
> On 2013/08/01 11:31 AM, Don Parris wrote:
>
> > Unfortunately, one bank throws everything except for the date and the
> > amount into a single "description" column.  It would still be simpler if
> > they did not include the actual transaction date in that column, as it
> > means the description changes... well... daily.  Grrrr....
> >
> >
> > On Thu, Aug 1, 2013 at 12:27 AM, Brian Barker <b.m.barker@btinternet.com>
> > **wrote:
> yes, left() or mid() is quicker if the contents are fixed. In Don's worse
> case scenario, where the bank's records have descriptions that are random,
> unpredictable in length and sequence, and change daily, but at least have
> predictable strings (eg store name) somewhere in them, try
>
> =IF(ISERR(FIND(LOWER(VLOOKUP(**D$3,List1,2)),Data.$D4))=1,"",**D$3)
>
> The key elements in the formula are *find *(to tag the transaction) and
> *vlookup *(to return the entity), with if(), iserr() and lower() acting in
> support.
> where
> a) the formula is repeated for each string in a table alongside the
> transaction, with each column "trying it's luck" and the last column
> identifying the ID (by summing the row of possible ID's where non-responses
> =0)
>
> b) iserr()=1 is used to identify if the transaction contains the key
> entity (if the field lacks the text, the cell blanks out, if it has the
> text, it returns the ID of the entity)
> c) if() is used to tag the transaction with the entity's code
> d) lower() is used because find() is case sensitive, and ties to the
> instruction to lower-casify the descriptions in sheet 2 (data) (equally use
> upper(), though it makes for wider description fields)
> e) find() is used to see if the description contains the text or not (the
> actual position of the text is irrelevant)
> f) vlookup() refers to List1, which is the table of entities and their code
> g) d$3 is the entity ID at the head of the column, where multiple columns
> apply their own string to the description
>
> _Working example__
> _https://docs.google.com/file/**d/**0B6LXy9sguZVkcXRBbGUxQVVvT1k/**
> edit?usp=sharing<https://docs.google.com/file/d/0B6LXy9sguZVkcXRBbGUxQVVvT1k/edit?usp=sharing>gives
>  a working example for 1000 transactions and 31 strings
> 1. Enter the text string in sheet 1 (summary) - the example allocates the
> entity ID and allows for 31 entities, where the first 7 are colour coded
> 2. Drop the bank data in sheet 2 (data)*. Remember to **|format|change
> case => lower case the descriptions* - the example allows for 1000
> transactions
> 3. See the processing in sheet 3 (analysis) - the 1000 transactions are
> auto-tagged for 31 texts, the 1st 7 are also colourised
>
> 4. Back to sheet 1 (summary), where the entitles are totalled for debits
> and credits in the month you choose and their frequency counted.
>
> The summary page sub-totals the transactions using dsum
> =IF($H4=0,"",DSUM(Result,F$3,$**C3:$C4))
> There are some embellishments, such as |validation (to show or hide
> non-key entities and to limit the summary to a month or not)
>
> The sheets are protected against changes but there is no password. To
> edit, just undo the protection.
> I hope this helps
>
>
>
> --
> To unsubscribe e-mail to: 
> users+unsubscribe@global.**libreoffice.org<users%2Bunsubscribe@global.libreoffice.org>
> Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-**
> unsubscribe/<http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/>
> Posting guidelines + more: http://wiki.**documentfoundation.org/**
> Netiquette <http://wiki.documentfoundation.org/Netiquette>
> List archive: 
> http://listarchives.**libreoffice.org/global/users/<http://listarchives.libreoffice.org/global/users/>
> All messages sent to this list will be publicly archived and cannot be
> deleted


-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted