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

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 
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
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