We help IT Professionals succeed at work.

Excel Find Replace Macro

W.E.B
W.E.B asked
on
777 Views
Last Modified: 2014-05-22
Hello,
Can you please help,
Column "B", is there a macro that will find & replace same Driver numbers. (starting Row 3)
With Driver1 (+1)

Example,
Driver  50       replace with Driver1
Driver 347      replace with Driver2
Driver  1086  replace with Driver3
Driver  2286  replace with Driver4
Driver  3020  replace with Driver5
and so on ......

I have about 100 Drivers on the sheet.

Rows are not always the same.

Sample attached..

Any help is appreciated.
Thanks,
sample.xlsx
Comment
Watch Question

Peter HarrisFileMaker Developer at Cognitive

Commented:
You can achieve this with a formula using the MATCH and INDEX functions.

I have attached a new copy of your example workbook with a new sheet called 'DriverMatch'. This has the find and replace or 'matching' table on it.

The first column cells are: A2 to A6 (use absolute reference so $A$2:$A$6)
The (replacement) column cells are: B2 to B6

=INDEX(DriverMatch!$B$2:$B$6,MATCH(Sheet1!B3,DriverMatch!$A$2:$A$6,0),1)

The MATCH function find the matching driver number ROW.
Then the INDEX function find the correct replacement based on the ROW.

You will need to populate the table in DriverMatch, then change the 6 in the formula to whatever is the last row of the lookup table.

MATCH can be used where VLOOKUP can't because a precise match is required and the list doesn't need to be sorted.
sample-v2.xlsx
Finance Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Will your Driver numbers be in numerical order for the equivalent number as they are in the example?

If so you could use a slightly simpler formula using the MATCH function; as Peter mentions the MATCH function returns the row number of the table in which it finds the matching value.

Therefore if your table is sorted in numerical order by the original driver number, the resulting replacement can be calculated, using Peter's suggestion of a MATCHing table, as follows, :

="Driver "&MATCH($B3,DriverMatch!$A$2:$A$6,0)

The Matching table for this would then be a single list sorted in order.

Thanks
Rob H
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Also if you wanted to put some sort of numbering convention on this you could add a TEXT function:

="Driver "&TEXT(MATCH($B3,DriverMatch!$A$2:$A$6,0),"000")

This will convert the resulting driver number to 3 digits.

Author

Commented:
Thank you guys.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.