At 18:32 31/07/2013 -0400, Don Parris wrote:
I download two files from my banks that have transaction
descriptions that look like so (the one with a * to the left is from
Bank #2 - the rest are from Bank #1):
PURCHASE AT MY GROCER #NNN, CHARLOTTE, NC ON 050913 FROM CARD#:
XXXXXXXXXXXXNNNN * MY GROCER #NNN CHARLOTTE NC
PURCHASE AT BIG BOX NNNN, CHARLOTTE, NC ON 050413 FROM CARD#:
XXXXXXXXXXXX1113 MERCHANT PAYMENT BIG BOX ANNNN AT LOC NNNNNN BIG BOX
ANNNN PINEVILLE Charlotte NC
Notice the way two transactions at BIG BOX are referred to in two
different ways - one includes a date, while the other does
not. Also, all series of "N"s & "X"s above are numbers.
All this doesn't help much until we know exactly what you need to
extract from all this.
My database has payee names like so:
entity_id | entity_name
01 | My Grocer
02 | Big Box
I want to prep the bank data and create a fresh CSV file, so as to
cull out some of the unnecessary data and create a fresh CSV file
for importing into my back-end DB. Ideally, I can automagically
match the entity name from the description field to the entity name
in my DB. My question is (or maybe questions are):
If I have the ID in ColA and the Name in ColB (from my DB), I want
to put the description list (from the banks) in ColE and be able to
match payee names and have the ID copied to ColD. Even if I have to
create a separate column for each bank's description style, that's
still gotta be better than trying to manually match up transaction
data from my bank to what I have in my DB.
I thought about VLOOKUP, but that seems to not work at all when I
try it using text fields.
So exactly what did you try - and what happened when you did?
What do you have in column E - the entire record from the bank, not
just the "entity name" you want to search for? In that case, the
first thing you probably have to do is to strip out just the entity
name into a separate column: let's say column F. How you do that
depends on the precise possibilities in the bank data. The next
problem is that VLOOKUP() can retrieve values only from the right of
matched cells, whereas your "entity ID" is currently to the left of
the entity name. Since you haven't mentioned column C, I'm going to
take that over for this purpose. In C1 enter =A1 and fill this down
the list of entities: you now have a copy of the ID list to the right
of the names - where they need to be.
Now in D1 enter
and fill that down column D.
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy