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
pasternik said:
What does these things do for my current situation???
Click to expand...

Suppose we have:

A,8
A,7
B,9
C,5
A,2

in A2:B6 on Sheet1. Let's suppose that this area grows (shrinks) over time. In order to take into account such changes, we can create a dynamic named range (as I've done for your data). If we want totals for A, B, and C, we can't do this in column A and B. This must be done somewhere else, say, in E:F, otherwise the definitions will not work.
 
Upvote 0
Ok, I get where you are going I think, but I never want totals for A,B & C, I just at this point want to compare data in C to the value list in L, and then match those results from Column c, to column I, to get my final results.. These are then uploaded to a Filemaker DB for more manipulation..... Currently I am more versed in Filemaker Calcs than Excel Calcs, which I wish I knew more of....
 
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