Automating a bulk find/replace across 2 lists

[2010-04-21]

OS: Win XP Pro
Office: Windows 2007

I have a sheet (e.g. Sheet1) with some data on, e.g.

NAME          | PROFILE                              | VALUE
----------------------------------------------------------------------------------------
TEST ITEM 1   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 1   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 1   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 1   | HR: Security Profile                 | This and That

I have another sheet (e.g. Sheet2) with a lit of different names on:

TEST ITEM 2
TEST ITEM 3
TEST ITEM 4
TEST ITEM 5
...
TEST ITEM 125

For each of the 125 items on Sheet2, I need to copy 4 lines defined in Sheet1, but replace the "NAME" value with the value of the item in Sheet2

So the end result will be like this:

NAME          | PROFILE                              | VALUE
----------------------------------------------------------------------------------------
TEST ITEM 1   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 1   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 1   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 1   | HR: Security Profile                 | This and That
TEST ITEM 2   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 2   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 2   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 2   | HR: Security Profile                 | This and That
TEST ITEM 3   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 3   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 3   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 3   | HR: Security Profile                 | This and That
TEST ITEM 4   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 4   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 4   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 4   | HR: Security Profile                 | This and That
TEST ITEM 5   | AP: Use Invoice Batch Controls       | YES
TEST ITEM 5   | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 5   | Concurrent:Report Access Level       | Responsibility
TEST ITEM 5   | HR: Security Profile                 | This and That
...
TEST ITEM 125 | AP: Use Invoice Batch Controls       | YES
TEST ITEM 125 | CCC_ITR_CUSTOMER_ADDRESS_ID          | 423422-XYZ
TEST ITEM 125 | Concurrent:Report Access Level       | Responsibility
TEST ITEM 125 | HR: Security Profile                 | This and That

I could do that by hand, but there would be a big margin for error, plus it'll take ages.

I wonder if there is any way to automate the process in Excel?

Any advice much appreciated.

Thanks!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Sep10
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] DataRng [COLOR="Navy"]As[/COLOR] Range
c = 1
With Sheets("Sheet2") '[COLOR="Green"][B]sht2[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]sht1[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] DataRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] Cells(c, 1)
        .Resize(DataRng.Rows.Count, DataRng.Columns.Count) = DataRng.Value
        .Resize(DataRng.Rows.Count, 1) = Dn
        c = c + DataRng.Rows.Count
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[2010-04-21]

Thanks Mick!

That is such a massive help. It's saved hours and hours of work, and probably lots of mistakes which would have been made had we created the list by hand.

Thanks again, your help is much appreciated.