Script to check for duplicates in one column then alter adjacent column values

**SebN** · 04-29-2010, 11:29 AM

Hi,

I hope you can help as I'm sure this shouldn't be too complicated but I'm going round in circles and tearing my hair out!

I have an excel spreadsheet that has lots of data in it. Columns B and C both hold 11 digit reference numbers. Column C has data in every cell but column B only has data in some of the fields. Column C also has a lot of cells with the same value. Any cells that have the same value in column C will have the corresponding column B value either as an 11 digit number or in some cases i will be blank. If it has a value other than blank, it will be the same for all versions of this duplicate.

I am trying to write a script that will look through column C and for any duplicate values it will populate the corresponding cell in column B with the correct 11 digit number.

So for example

A    B    C
1    1    123
2         123
3         456
4    2    789
5    3    456

After the script would return

A    B    C
1    1    123
2    1    123
3    3    456
4    2    789
5    3    456

If anyone can help I would be so so grateful!

**StephenR** · 04-29-2010, 12:03 PM

Select column B.

Edit > Goto > Special > Blanks > OK.

Enter in the formula bar =B1 (where B1 is the first entry above a blank)

Ctrl+Enter.

Copy/paste special values if you want to remove the formulae.

Or by code:

Sub Macro2()

With Columns("B:B")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With

End Sub

Dave - I think I got this from shg. What comes around...

**SebN** · 04-29-2010, 12:08 PM

Hi,

Thanks for the help but the spreadsheet I'm going to do this on is about 5000 rows long and will have to be done for multiple workbooks so I'm hoping to have a VB script that I can just run as a macro to completely automate the procedure!

Edit - that was in response to Steve! Checking out your solution now Dave!

**davegugg** · 04-29-2010, 12:05 PM

Paste this in a standard module:

Public Sub SebN()

Dim CVal As Double
Dim BVal As Double

With ActiveSheet

For i = 2 To .Cells(Rows.Count, 3).End(xlUp).Row
    CVal = .Cells(i, 3).Value
    BVal = .Cells(i, 2).Value
    If BVal = 0 Then GoTo skip
    For j = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
        If .Cells(j, 3).Value = CVal Then
            .Cells(j, 2).Value = BVal
        End If
    Next j
skip:
Next i

End With

End Sub

This assumes the data to process is the only thing in these columns, and starts in row 2.

Oh shoot, Steve's is much easier. Good lesson here, look for simplest solution first instead of relying too much on VBA.

**elprup** · 04-29-2010, 12:15 PM

One of them could inject some irony into this thread by writing code to carry out Stephen's solution.

Edit -

HA! He did it on his own before I completed my post.

**SebN** · 04-29-2010, 12:20 PM

Hi Dave,

Thanks very much! Your solution works (almost) perfectly!

It does fall down however if the last cell of Row B is blank (which it sometimes is), so if I have

A    B    C
1    1    123
2         123
3    3    456
4    2    789
5         456

It returns

A    B    C
1    1    123
2    1    123
3    3    456
4    2    789
5         456

So, in this one the last cell in column B should be a 3

**davegugg** · 04-29-2010, 12:23 PM

Well, you could change For j... line of code to have a 3 instead of a 2 (like the For i... line of code), but use StephenR's code, its much more efficient.

Script to check for duplicates in one column then alter adjacent column values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Re: Script to check for duplicates in one column then alter adjacent column values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions