 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Find and replace numbers using a formula
We have 45 branch locations at our company. Each location is assigned a merchant number to use when processing credit cards from customers. Our month end bank statement is around 1000 lines long and lists all of our branches sales paid for by credit card using their merchant number as an identifer. I would like a formula that reads down the bank stmt and replaces each merchant number with our branch number, so I can balance the bank for each branch. For example if the merchant number attached to branch 1 is 500263 (this never changes) I would like the formula &/or function to locate every instance of that merchant number 500263 in the bank statement (it's on excel) and replace it with our branch number 1 and so on. I have used the,edit- find- replace all, but this is tedious. Please help! You would need to use some combination of the FIND and REPLACE functions. I would look at XL's help for a starter and if you have questions post back here. Dave  SignatureBrevity is the soul of wit. > We have 45 branch locations at our company. Each location is assigned a > merchant number to use when processing credit cards from customers. Our month [quoted text clipped - 7 lines] > it with our branch number 1 and so on. I have used the,edit- find- replace > all, but this is tedious. Please help! Thanks for the idea I have played around with those functions for a few weeks now and cannot seem to hit on the formula needed. It seems those functions are for text? I am sort of a novice I just wrote my first simple macro the other day and am learning by trail and error. My whole office has tried to find a solution formula to no avail. Could be more specific? > You would need to use some combination of the FIND and REPLACE functions. I > would look at XL's help for a starter and if you have questions post back [quoted text clipped - 13 lines] > > it with our branch number 1 and so on. I have used the,edit- find- replace > > all, but this is tedious. Please help! Try Vlookup Set up a data set with the two numbers sets add a helper column and use Vlookup() > We have 45 branch locations at our company. Each location is assigned a > merchant number to use when processing credit cards from customers. Our month [quoted text clipped - 7 lines] > it with our branch number 1 and so on. I have used the,edit- find- replace > all, but this is tedious. Please help! I think you are on the right track. I am somewhat of a novice and have been trying various functions for a few weeks now. Vlookup is the function I keep going back to. Do you think the Index function could also play in to this? Right now I add 2 columns to my bank statement listing branches 1-45 in the 1st and their merchant numbers in the 2nd. I then use this for the "find" & "replace all" under edit. So I have the first part, What is the helper column? Can you be more specific with an example or what formula would go in the helper column? > Try Vlookup > Set up a data set with the two numbers sets [quoted text clipped - 11 lines] > > it with our branch number 1 and so on. I have used the,edit- find- replace > > all, but this is tedious. Please help! It seems to me that you've already done most of the work by creating the datalist of branch numbers and merchant numbers. Assume the bank statement is A1 to G1000, with headers in Row1. Say the XL bank statement has the merchant number in Column C. Say the sales amounts are in Column D. And your datalist is in Y1 to Z46, with branch number in Y and merchant number in Z. In H2 enter this formula: =INDEX(Y$2:Y$46,MATCH(C2,Z$2:Z$46,0)) In X2 enter this formula: =SUMIF(H$2:H$1000,Y2,D$2:D$1000) Copy both H2 and X2 down as needed. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I think you are on the right track. I am somewhat of a novice and have been trying various functions for a few weeks now. Vlookup is the function I keep going back to. Do you think the Index function could also play in to this? Right now I add 2 columns to my bank statement listing branches 1-45 in the 1st and their merchant numbers in the 2nd. I then use this for the "find" & "replace all" under edit. So I have the first part, What is the helper column? Can you be more specific with an example or what formula would go in the helper column? "bj" wrote: > Try Vlookup > Set up a data set with the two numbers sets [quoted text clipped - 22 lines] > > replace > > all, but this is tedious. Please help! Thank you so much for giving me the actual formula. I will try it out this week. It also occurred to me over the week-end that I coud run a macro while I do the "find" "replace all" edit for each branch and then run that every month. The possible issue with that is the bank statement parameters can vary from month to month. This may be an issue with the formula also, and I will just have to make the range changes each month. I have one more quick question if you don't mind, I cannot get the TRANSPOSE function to work. The first time I tried it, it worked great and I cannot remember what I did. My Discover statement lists the merchant number and then directly below it in the same col. (A) lists the charge amount. So I need to move every other row to it's own col (B ). It should read A1=merchant number, B1= charge amount, not A1= merchant number, A2= charge amount. Thank you. > It seems to me that you've already done most of the work by creating the > datalist of branch numbers and merchant numbers. [quoted text clipped - 50 lines] > > > replace > > > all, but this is tedious. Please help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.