Partial Match and replace if found..

P

pasternik

New Member
Joined
Aug 16, 2010
Messages
18
Statements

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 91px"><COL style="WIDTH: 267px"><COL style="WIDTH: 59px"><COL style="WIDTH: 71px"><COL style="WIDTH: 77px"><COL style="WIDTH: 83px"><COL style="WIDTH: 145px"><COL style="WIDTH: 211px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">874</TD><TD style="TEXT-ALIGN: left">8/9/2010</TD><TD>WAL MART</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 1.59 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 100.49 </TD><TD> </TD><TD>Sundry Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">875</TD><TD style="TEXT-ALIGN: left">8/9/2010</TD><TD style="TEXT-ALIGN: left">QUICKSAVE TRANSFER OUT TO SAVINGS</TD><TD>DEBIT</TD><TD style="TEXT-ALIGN: right">$ 5.50 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 94.99 </TD><TD>Quicksave to Savings</TD><TD>Non Income - Savings Transfers</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">876</TD><TD style="TEXT-ALIGN: left">8/10/2010</TD><TD>INTERNET XFER FROM SVGS</TD><TD>XFER</TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 7.00 </TD><TD style="TEXT-ALIGN: right">$ 101.99 </TD><TD>From Savings</TD><TD>Non Income - Savings Transfers</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">877</TD><TD style="TEXT-ALIGN: left">8/10/2010</TD><TD>OK NATURAL GAS UTIL</TD><TD>DEBIT</TD><TD style="TEXT-ALIGN: right">$ 44.92 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 57.07 </TD><TD>Natural Gas</TD><TD>Utilities</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">878</TD><TD style="TEXT-ALIGN: left">8/10/2010</TD><TD>CIRCLE K STORE</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 40.11 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 16.96 </TD><TD> </TD><TD>Auto Gas</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">880</TD><TD style="TEXT-ALIGN: left">8/10/2010</TD><TD>CASH W/D</TD><TD>ATM</TD><TD style="TEXT-ALIGN: right">$ 20.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (31.99)</TD><TD> </TD><TD>Sundry Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">882</TD><TD style="TEXT-ALIGN: left">8/10/2010</TD><TD>LOVE S COUNTRY</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 15.21 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (76.15)</TD><TD> </TD><TD>Auto Gas</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">884</TD><TD style="TEXT-ALIGN: left">8/11/2010</TD><TD>CASH W/D</TD><TD>ATM</TD><TD style="TEXT-ALIGN: right">$ 100.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (205.10)</TD><TD> </TD><TD>Sundry Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">886</TD><TD style="TEXT-ALIGN: left">8/11/2010</TD><TD>CREST FOODS OF EDM</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 38.67 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (272.72)</TD><TD>Groceries</TD><TD>Personal Groceries</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">888</TD><TD style="TEXT-ALIGN: left">8/11/2010</TD><TD>MCDONALD'S</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 12.98 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (314.65)</TD><TD> </TD><TD>Personal Meals</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">889</TD><TD style="TEXT-ALIGN: left">8/12/2010</TD><TD style="TEXT-ALIGN: left">ATM FOREIGN W</TD><TD>FEE</TD><TD style="TEXT-ALIGN: right">$ 1.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (315.65)</TD><TD>Bank Fees</TD><TD>Bank Fees</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">891</TD><TD style="TEXT-ALIGN: left">8/12/2010</TD><TD>CASH W/D</TD><TD>ATM</TD><TD style="TEXT-ALIGN: right">$ 42.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ (386.60)</TD><TD> </TD><TD>Sundry Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">892</TD><TD style="TEXT-ALIGN: left">8/13/2010</TD><TD>STATE PAYROLL CHECKING </TD><TD>DEP</TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,915.88 </TD><TD style="TEXT-ALIGN: right">$ 1,529.28 </TD><TD>State Payroll</TD><TD>Income W2 - Jenni</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">893</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="COLOR: #ff0000">CREST FOODS OF EDM CHK CARD PUR</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 160.46 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,368.82 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">894</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">AT&T PAYMENT - 487160774OKC</TD><TD>DEBIT</TD><TD style="TEXT-ALIGN: right">$ 145.36 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,223.46 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">895</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="COLOR: #ff0000">LOGANS CHK CARD PUR MIDWEST CITY</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 51.06 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,172.40 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">896</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="COLOR: #ff0000">SHELL OIL 57442704 CHK CARD PUR</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 38.69 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,133.71 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">897</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">921 SE 66TH ATM CASH W/D OKLAHOM</TD><TD>ATM</TD><TD style="TEXT-ALIGN: right">$ 20.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,113.71 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">898</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">CREST FOODS OF EDM CHK CARD PUR</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 18.55 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,095.16 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">899</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">CORK AND BOTTLE WI CHK CARD PUR</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 16.79 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,078.37 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">900</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="COLOR: #ff0000">SHELL OIL 57440899 CHK CARD PUR</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 6.12 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,072.25 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">901</TD><TD style="TEXT-ALIGN: left">8/16/2010</TD><TD style="COLOR: #ff0000">ONCUE EXPRESS 101 CHK CARD PUR O</TD><TD>POS</TD><TD style="TEXT-ALIGN: right">$ 3.77 </TD><TD> </TD><TD style="TEXT-ALIGN: right">$ 1,068.48 </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Here is my table, the data in Black in column C has already been modified manually to show the data that I would like to show for a pivot table, the data in red in column C is the raw data imported from my bank that I would like to change to match above entries if they are there. Then I would also like to input the tax category from column I.

Now I have a Tax Category Value list of fixed values from here in Column K

Statements

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 276px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>K</TD></TR><TR style="HEIGHT: 37px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ffffcc">Tax Categories - Choose</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Advertising</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Auto Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Auto Gas</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Auto Insurance</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Auto Loan</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Auto Registration Fees</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Auto Repairs</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Bank Fees</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Business - Cell Phone</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Business - Clothing</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Business - Direct Expense</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>Business - Groceries</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>Business - Meals</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>Business - Misc</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Business - Office Suppiles</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Business - Travel</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>Charitable Donations - Cash</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>Charitable Donations - Non Cash</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Child Care Expenses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Entertainment</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Gifts</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Home Decoration</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Home Maintenance & Repair</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


But I do not have a unique value list set up for the Descriptions in column C, I'm sure I need to do this, but I'm not sure how other than using data validation, which does not produce unique entries.

So I guess my first problem is to create the unique entries from Column C to do a search in the Raw data for a partial match to give the data in column c the new description name, and if there is no match maybe to highlight the data in red or something....

I hope I'm explaining this all right...

Jenni
 
Sort by date Sort by votes
Ok so I got the unique descriptions from advanced filter, and they now reside in column L, there are 158 unique entries. Now I need to compare the raw data in red to the values in Column L, and rewrite the data (I guess now into a new column) if part of the data matches something in Column L...

Any help out there??

Thanks bunches!!!
Jenni
 
Upvote 0
Are you kidding me is there anyone out there moderating these posts, this is a very busy forum, maybe i should go elsewhere...
 
Upvote 0
I'm not trying to be a jerk I just really need help with this, I have been trying to figure this out for weeks and just today, I'm staring to get somewhere by just fouling around, now I need some real help if anyone can help me...
Here are my updated values and formulas:

Temp for Formatting


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 266px"><COL style="WIDTH: 282px"><COL style="WIDTH: 300px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>J</TD><TD>L</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffcc">Description</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffcc">Matched value </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffcc">Description Value List </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="COLOR: #ff0000">CREST FOODS OF EDM CHK CARD PUR</TD><TD>CREST FOODS OF EDM</TD><TD>7 ELEVEN</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">ATT PAYMENT - 487160774OKC</TD><TD>ATT PAYMENT</TD><TD>ACADEMY SPORTS</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="COLOR: #ff0000">LOGANS</TD><TD>LITTLE GROCERY</TD><TD>ADVANCE AUTO PARTS</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="COLOR: #ff0000">SHELL OIL 57442704 CHK CARD PUR</TD><TD>SHELL OIL</TD><TD>ALL AMERICAN CHRIS</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">921 SE 66TH ATM CASH W/D OKLAHOM</TD><TD>7 ELEVEN</TD><TD>ALLTON'S</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">CREST FOODS OF EDM CHK CARD PUR</TD><TD>CREST FOODS OF EDM</TD><TD>AMAZON MKTPLACE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; COLOR: #ff0000">CORK AND BOTTLE WI CHK CARD PUR</TD><TD>CORK & BOTTLE WINE</TD><TD>AMERICAN FLORAL</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="COLOR: #ff0000">SHELL OIL 57440899 CHK CARD PUR</TD><TD>SHELL OIL</TD><TD>ANDERSON FOODMART</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="COLOR: #ff0000">ONCUE EXPRESS 101 CHK CARD PUR O</TD><TD>ONCUE EXPRESS</TD><TD>ANDERSONS TRAV</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="COLOR: #ff0000">QUICKSAVE TRANSFER OUT TO 080738</TD><TD>PROMART</TD><TD>APL*ITUNES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="COLOR: #ff0000">DEPOSIT</TD><TD>DEPOSIT</TD><TD>ARBY'S</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="COLOR: #ff0000">CHECK-IMAGE</TD><TD>CHECK</TD><TD>ATM CASH W/D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="COLOR: #ff0000">MERCY HEALTH SIGNA CHK CARD PUR</TD><TD>MERCY HEALTH SIGNA</TD><TD>ATM FOREIGN W</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="COLOR: #ff0000">921 SE 66TH ATM CASH W/D OKLAHOM</TD><TD>7 ELEVEN</TD><TD>ATT PAYMENT</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>FLASH MART LLC0083 CHK CARD PUR</TD><TD>FLASH MART LLC</TD><TD>AUTOZONE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>QUICKSAVE TRANSFER OUT TO 080738</TD><TD>PROMART</TD><TD>Bartolo's Jewelry</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>CHECK-IMAGE</TD><TD>CHECK</TD><TD>BEAUTY CO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>CITY OF EDMOND PAYMENT - 6422900</TD><TD>CITY OF EDMOND PAYMENT</TD><TD>BED BATH & BEYOND</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>PROG NORTHERN INS PREM - 1869300</TD><TD>PROG NORTHERN INS PREM</TD><TD>BEST BRIDAL PRICES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>ACADEMY SPORTS #88 CHK CARD PUR</TD><TD>ACADEMY SPORTS</TD><TD>BOB MOORE INFIN</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>OFFICE DEPOT #2085 CHK CARD PUR</TD><TD>OFFICE DEPOT</TD><TD>BRAUMS</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>1 NW 23RD ATM CASH W/D OKLAHOMA</TD><TD>#N/A</TD><TD>BRISCOES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>ACADEMY SPORTS #85 CHK CARD PUR</TD><TD>ACADEMY SPORTS</TD><TD>BROADWAY WINE & SP</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>7 ELEVEN 97 0000 CHK CARD PUR OK</TD><TD>7 ELEVEN</TD><TD>BYRON'S LIQUOR WAR</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>921 SE 66TH ATM CASH W/D OKLAHOM</TD><TD>7 ELEVEN</TD><TD>C & R LIQUOR STORE</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J2</TD><TD>=INDEX($L$2:$L$158,MATCH(C2,$L$2:$L$158,1))</TD></TR><TR><TD>J3</TD><TD>=INDEX($L$2:$L$158,MATCH(C3,$L$2:$L$158,1))</TD></TR><TR><TD>J4</TD><TD>=INDEX($L$2:$L$158,MATCH(C4,$L$2:$L$158,1))</TD></TR><TR><TD>J5</TD><TD>=INDEX($L$2:$L$158,MATCH(C5,$L$2:$L$158,1))</TD></TR><TR><TD>J6</TD><TD>=INDEX($L$2:$L$158,MATCH(C6,$L$2:$L$158,1))</TD></TR><TR><TD>J7</TD><TD>=INDEX($L$2:$L$158,MATCH(C7,$L$2:$L$158,1))</TD></TR><TR><TD>J8</TD><TD>=INDEX($L$2:$L$158,MATCH(C8,$L$2:$L$158,1))</TD></TR><TR><TD>J9</TD><TD>=INDEX($L$2:$L$158,MATCH(C9,$L$2:$L$158,1))</TD></TR><TR><TD>J10</TD><TD>=INDEX($L$2:$L$158,MATCH(C10,$L$2:$L$158,1))</TD></TR><TR><TD>J11</TD><TD>=INDEX($L$2:$L$158,MATCH(C11,$L$2:$L$158,1))</TD></TR><TR><TD>J12</TD><TD>=INDEX($L$2:$L$158,MATCH(C12,$L$2:$L$158,1))</TD></TR><TR><TD>J13</TD><TD>=INDEX($L$2:$L$158,MATCH(C13,$L$2:$L$158,1))</TD></TR><TR><TD>J14</TD><TD>=INDEX($L$2:$L$158,MATCH(C14,$L$2:$L$158,1))</TD></TR><TR><TD>J15</TD><TD>=INDEX($L$2:$L$158,MATCH(C15,$L$2:$L$158,1))</TD></TR><TR><TD>J16</TD><TD>=INDEX($L$2:$L$158,MATCH(C16,$L$2:$L$158,1))</TD></TR><TR><TD>J17</TD><TD>=INDEX($L$2:$L$158,MATCH(C17,$L$2:$L$158,1))</TD></TR><TR><TD>J18</TD><TD>=INDEX($L$2:$L$158,MATCH(C18,$L$2:$L$158,1))</TD></TR><TR><TD>J19</TD><TD>=INDEX($L$2:$L$158,MATCH(C19,$L$2:$L$158,1))</TD></TR><TR><TD>J20</TD><TD>=INDEX($L$2:$L$158,MATCH(C20,$L$2:$L$158,1))</TD></TR><TR><TD>J21</TD><TD>=INDEX($L$2:$L$158,MATCH(C21,$L$2:$L$158,1))</TD></TR><TR><TD>J22</TD><TD>=INDEX($L$2:$L$158,MATCH(C22,$L$2:$L$158,1))</TD></TR><TR><TD>J23</TD><TD>=INDEX($L$2:$L$158,MATCH(C23,$L$2:$L$158,1))</TD></TR><TR><TD>J24</TD><TD>=INDEX($L$2:$L$158,MATCH(C24,$L$2:$L$158,1))</TD></TR><TR><TD>J25</TD><TD>=INDEX($L$2:$L$158,MATCH(C25,$L$2:$L$158,1))</TD></TR><TR><TD>J26</TD><TD>=INDEX($L$2:$L$158,MATCH(C26,$L$2:$L$158,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


I understand by putting 1 for the Match it does not return the exact match, but I would like to actually search column c for words that match in column L, as you can see in Column L I have "ATM Cash W/D" this is not found in column C because there are street addresses before the "ATM CASH W/D" in the string, is there a better way to just serach the string of L:L in C:C and if the string is not found return N/A???


Help Please!!!!
 
Upvote 0
Thank You Aladin, for answering my question, it does work and gives me the desired results now, but can you explain to me what 9.99999999999999E+307 does? Does that make it recursive or something??

Jenni
 
Upvote 0
Here is my final calc for that field and it handles the errors too..

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH($M$2:$M$158,C2),$M$2:$M$158)),C2,(LOOKUP(9.99999999999999E+307,SEARCH($M$2:$M$158,C2),$M$2:$M$158)))

Thanks Crook I'll go check that out!
 
Upvote 0
Ok now I need to move on to my second problem, now I have the data corrected, now I need to search the above entries in column C to find the corresponding Tax Category for previous records...

I tried to apply the calc above to this senario but it isn't going to work for this, because I need to find a match for the "current record" in column C in "previous records" in column C and then get the value in Tax Category in column I from that matching record.

<b>Statements Testing</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:91px;" /><col style="width:267px;" /><col style="width:211px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >880</td><td style="text-align:left; ">8/10/2010</td><td >CASH W/D</td><td >Sundry Expenses</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >882</td><td style="text-align:left; ">8/10/2010</td><td >LOVE S COUNTRY</td><td >Auto Gas</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >884</td><td style="text-align:left; ">8/11/2010</td><td >CASH W/D</td><td >Sundry Expenses</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >886</td><td style="text-align:left; ">8/11/2010</td><td >CREST FOODS OF EDM</td><td >Personal Groceries</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >888</td><td style="text-align:left; ">8/11/2010</td><td >MCDONALD'S</td><td >Personal Meals</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >889</td><td style="text-align:left; ">8/12/2010</td><td style="text-align:left; ">ATM FOREIGN W</td><td >Bank Fees</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >891</td><td style="text-align:left; ">8/12/2010</td><td >CASH W/D</td><td >Sundry Expenses</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >892</td><td style="text-align:left; ">8/13/2010</td><td >STATE PAYROLL CHECKING </td><td >Income W2 - Jenni</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >893</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">CREST FOODS OF EDM</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >894</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; text-align:left; ">ATT PAYMENT</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >895</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">LOGANS</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >896</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">SHELL OIL</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >897</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; text-align:left; ">ATM CASH W/D</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >898</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; text-align:left; ">CREST FOODS OF EDM</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >899</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; text-align:left; ">CORK AND BOTTLE WI</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >900</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">SHELL OIL</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >901</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">ONCUE EXPRESS</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >902</td><td style="text-align:left; ">8/16/2010</td><td style="color:#ff0000; ">QUICKSAVE TRANSFER OUT</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >903</td><td style="text-align:left; ">8/17/2010</td><td style="color:#ff0000; ">DEPOSIT</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >904</td><td style="text-align:left; ">8/17/2010</td><td style="color:#ff0000; ">CHECK</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >905</td><td style="text-align:left; ">8/17/2010</td><td style="color:#ff0000; ">MERCY HEALTH SIGNA</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >906</td><td style="text-align:left; ">8/17/2010</td><td style="color:#ff0000; ">ATM CASH W/D</td><td > </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" Excel Jeanie HTML 4 </a>

Any help on this monster??
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
925
smozgur
smozgur
C
  • Solved
I need a formula to a) find the first numeral in a text string b) remove that numeral and all the characters after that numeral
Replies
3
Views
123
Peter_SSs
Peter_SSs
E
  • Question Question
VBA code clean up
Replies
9
Views
703
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
104
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
710
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top