Get the first instance of a value from a range

[swaggerbox]

I have two columns "A" and "B" . Column A contains names of people while Column B contains values associated to these people. However, due to some reasons, several entries in column B have been incorrectly captured. Is there a way that we can replace the "incorrect values" in column B. The first instance or the first value that correspond to each name should be referred to as the correct one. For example (shown below), John should always have a value of 12 all through out column B, and Gale should have a 10, and so on. Any ideas?

http://i.imgur.com/nuRy8oI.jpg"

[Aflatoon]

Copy B1 to C1 and then in C2:
=IF(A2=A1,C1,B2)
and fill down.

[swaggerbox]

Hi,
What if, I'm not allowed to input in other cells except column B

[mikerickson]

If the first value is the correct one, VLOOKUP("John", A:B, 2, False) will return the correct value for John.

[swaggerbox]

Nope. The FIRST VALUE for each name is the CORRECT one. For example, the first value for John is 12, based on the example. For Gale, it is 10, for Kriz, it is 1. And for Mike it is 4. So going by that logic, if the value of John is other than 12, then it is incorrect, the same thing with Gale (should always be 10), Kriz (always 1) and Mike (always 4), based on the example.

[Aflatoon]

Are you not allowed to use other cells at all, even temporarily?

[Paul_Hossler]

If you can't use any other columns, this will replace any name's value with the first value for that name

It doesn't assume anything is sorted




Option Explicit
Sub PickFirst()
Dim i As Long
Dim r As Range

Set r = ActiveSheet.Cells(1, 1).CurrentRegion

Application.ScreenUpdating = False

For i = 1 To r.Rows.Count
r.Cells(i, 2).Value = Application.WorksheetFunction.VLookup(r.Cells(i, 1).Value, r, 2, False)
Next i

Application.ScreenUpdating = True

End Sub

[mikerickson]

Your screen shot is too small for me to see clearly.
What does the VLOOKUP formula I posted return? When you change "John" to "Gale", etc. what values does it return then?

[swaggerbox]

Thanks for this macro Paul!

[snb]

15829