Find sting in another row on another sheet, then replace with matching cell data

**shauncat** · 08-20-2012, 02:27 AM

Hi

I am recieving sets of data from an extract that does not contain the full code. All the full codes are Unique. For instance 'BBA4636' will always have the full code of 'BBA4636 - S'
What I want to do is the following:

For eg:
Sheet 1 Column A
BBA4636
BBA4689
BBA4638
BBA4639
BBA4625
BBA4641
BBA4690
BBA4643

Sheet 2 Column A
BBA4689 - A
BBA4625 - L
BBA4638 - F
BBA4641 - T
BBA4639 - Z
BBA4690 - P
BBA4643 - N
BBA4636 - S
BBA4612 - D
BBA4601 - Q
BBA4699 - H

I want a macro to check the first cell in Sheet 1 Column A.
Find that string in Sheet 2 Column A.
Copy the cell in Sheet2 Columna A and replace the one in Sheet1 Column A
Then proceed to the next cell in Sheet 1 Column A.

The end result being that all the cells in Sheet 1 Column A would have the full extension.

TestReplace.xlsx

Thanks for your help

**bebo021999** · 08-20-2012, 02:37 AM

We can create a new list in other sheet (area)
=IFERROR(INDEX(Sheet2!$A$1:$A$11,MATCH(Sheet1!A1&"*",Sheet2!$A$1:$A$11,0)),A1)
Copy down

**jindon** · 08-20-2012, 02:40 AM

Try the attached

**shauncat** · 08-20-2012, 02:52 AM

Thanks for your input.

Unfortunatly this page will stretch out to around 25000 rows so I will need a macro.
It is fine if it populates an adjacent blank cell though.

**shauncat** · 08-20-2012, 03:30 AM

Thanks Jindon, I will test it tomorrow afternoon on my main sheet

**shauncat** · 08-21-2012, 02:36 AM

Worked a treat.
Your a star.
Thanks for your help

**Cutter** · 08-21-2012, 11:56 AM

@ shauncat

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**shauncat** · 08-23-2012, 05:25 AM

Thanks Cutter

I will be well aware of using the solved feature in the future.

Find sting in another row on another sheet, then replace with matching cell data

LinkBack

Thread Tools

Rate This Thread

Display

Find sting in another row on another sheet, then replace with matching cell data

Re: Find sring in another row on another sheet, then replace with matching cell data

Re: Find sting in another row on another sheet, then replace with matching cell data

Re: Find sring in another row on another sheet, then replace with matching cell data

Re: Find sting in another row on another sheet, then replace with matching cell data

Re: Find sting in another row on another sheet, then replace with matching cell data

Re: Find sting in another row on another sheet, then replace with matching cell data

Re: Find sting in another row on another sheet, then replace with matching cell data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions