W.E.B

asked on

# Excel Find Replace Macro

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

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

ASKER CERTIFIED SOLUTION

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

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

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

Thanks

Rob H

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.

="Driver "&TEXT(MATCH($B3,DriverMat

This will convert the resulting driver number to 3 digits.

ASKER

Thank you guys.

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

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