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

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
=VLOOKUP(F1;B$1:C$10;2;0)
and fill that down column D.

I trust this helps.

Brian Barker


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