Complicated IF statement?

[iH8usrnames]

I am new here and not even sure how ask the question let alone search the forums to see if this has been asked before.

I am trying to get a better handle on finances by tracking everything in Excel but the bank is really no help, I cannot get a quicken or TXT file from them. The only option I have is a CSV which is poorly formatted. My solution is to simply copy and paste the text from the website into excel however I still have on hurdle to jump - the source of the transaction is extremely vague in some cases. A few examples:

1. POS 1227 1649 312713 505 W ARMY TRAIL BLOOMINGDALE IN
2. POS 1224 1710 307616 1553 EAST HILL RD. GRAND BLANC MI
3. POS 1223 1346 304648 160 N ROBERT T PALM ELMHURST IL

I know from address lookup that:
1 = Costco
2 = Oliver T's
3 = Walgreens

What I would like is some test or statement that checks if a cell contains a certain string of text, if it exists, it populates and adjacent cell with the required info.

Looks for 160 N ROBERT T PALM ELMHURST IL then writes Walgreens

The goal is to keep expanding this criteria as the spreadsheet grows.

[mcmahobt]

Take a look at the attached workbook for a possible solution. You can expand the Lookup Table sheet data as you know what more store addresses are. Column B of the Data Import will then pull the name of the establishment.

Hope this helps!

[iH8usrnames]

YES! That is exactly what I needed. Thanks a ton.

[iH8usrnames]

That is exactly what I want but unfortunately I cannot manage to integrate it into my existing spreadsheet. For whatever reason I cannot make it work; I guess this is why I am an admin and not a developer (write code).

I have attached a section of the Bank spreadsheet I am working on.

Any help would be greatly appreciated.

[FDibbins]

Try this, instead...
=INDEX('Lookup Table'!B:B,MATCH(B3,'Lookup Table'!A:A,0))

Your sample was significantly different to your actual

[iH8usrnames]

@ FDibbiins
Brilliant. I apologize if it was misleading. My intent was to remove what I perceived as extraneous information but I guess that did not help things.
I honestly would never have figured this out on my own.

@ alansidman
That works too. I am thoroughly impressed.

[alansidman]

In F2, =INDEX('Lookup Table'!B:B,MATCH("*"&MID(B3,LEN(LEFT(B3,22)),LEN(B3)),'Lookup Table'!A:A,0)) and copy to appropriate other cells.

[FDibbins]

@ Alan, it looks like the text is on its own row, below the "Withdraw" etc text?

[alansidman]

@Ford. Agree.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[iH8usrnames]

I have returned. Try as I might I cannot seem to get any one formula to return the information I need. This time I have attached the actual full spreadsheet.

Some of the transaction information supplied by the bank has business names on the left.

BANGKOK PEPPERS GRAND BLANC MI*01-22-15*254538
BARE ESCENTUALS #04 CHICAGO IL*01-01-15*824498
BINNYS BEVERAGE DEP GLEN ELLYN IL*11-28-14*973297
BRICKS WOOD FIRED WHEATON IL*12-03-14*147324
BUZZ CAFE OAK PARK IL*01-02-15*505295

Some of the transaction information supplied by the bank has business names on the right:

POS 1228 1958 314064 AMAZON.COM SEATTLE WA
POS 1230 1632 317823 MENARDS- GLENDALE H GLENDALE HEIGIL
POS 1231 1340 319625 Marianos FRESH WHEATON IL

Some of the transaction information supplied by the bank has only address information:

POS 1213 2202 283981 142 OAKBROOK CENTER OAKBROOK IL
POS 1217 1903 291775 505 W ARMY TRAIL RD BLOOMINGDALE IL
POS 1218 1923 293986 2031 MAIN ST. WHEATON IL
POS 1219 2010 296746 360 W ARMY TRAIL RD BLOOMINGDALE IL

Given the nature of the data supplied it I cannot only look to the left for data, or the right, or the middle for a match. It seems I need one that finds a matching pattern of characters of a certain length regardless where they fall in the cell. In my imagination the above supplied data would find matched segments:

142 OAKBROOK CE
505 W ARMY TRAI
2031 MAIN ST. WH
360 W ARMY TRAI
BANGKOK PEPPERS
BARE ESCENTUALS
BINNYS BEVERAGE
BRICKS WOOD FIR
BUZZ CAFE OAK P
AMAZON.COM SEAT
MENARDS- GLENDA
Marianos FRESH


Mind you, I really have no idea how to do this and look forward to trying to understand the responses given, reverse engineering.

Thanks in advance.BankStatementRealSample.xlsx

[FDibbins]

OK your upload is not really the same as your example.

THis 1 is tricky because some rows need to discard the left data, some discard the right data and others need to derop both.

This is what I have so far, it works on a lot of them...
I added a helper column on Lookup sheet in C...
=IF(LEFT(A63,3)="Pos",A63,LEFT(A63,30)) copied down

Then used this...
=INDEX(LookupTable!B:B,MATCH(IF(LEFT(F2,3)="Pos","*"&MID(F2,22,30)&"*",LEFT(F2,30)),LookupTable!C:C,0))
copied down.

It drops the errors from 184 to 49

Play around with the LEFT(A63,30)) and MID(F2,22,30) parts and see if you can narrow it down further

Of course, someone may could up with a proper sollution for this
Play around with the

[iH8usrnames]

I was able to move that setting down to 5 and get much better population of the spreadsheet.

I really appreciate the help.

[FDibbins]

Glad it worked for you

Note that the fewer letters you use (5), the less accuracy you may have

[iH8usrnames]

For sure it does.

I just made a change.

=IF(LEFT(A2,3)="POS",MID(A2,22,11),LEFT(A2,5))

MID(A2,22,11)
The first 21 characters in the "POS" entries can be ignored for the most part, it seems everything from Character 22 and beyond should be unique to a given vendor. I just need to figure out how many characters I require.

I know this will not be perfect but it will be much closer than what I had before, which is nothing.

As a result of this exercise my wife is now open to changing to a new banks so we can better manage this stuff.

[FDibbins]

The first 21 characters in the "POS" entries can be ignored for the most part,

Yup, thats what I figured as well, I used 22 instead of 21, but you know your data