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 <er...@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.bar...@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.bar...@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%2bunsubscr...@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+unsubscr...@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
