Find and replace numbers using a formula

[Guest]

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!

 

[Guest]

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

 

[Guest]

Try Vlookup
Set up a data set with the two numbers sets
add a helper column and use Vlookup()

 

[Guest]

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?

 

[Guest]

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?

 

[RagDyeR]

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$1000)

Copy both H2 and X2 down as needed.

--

HTH,

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?

 

[Guest]

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.