My friend has two columns where one has unique values and another column has those same values but also duplicated like shown below

Column 1    Column 2
ABC123      ABC123
CDE456      ABC123
EFG789      ABC123

Sometimes column 2 doesn't always have all the values in which case i think it just needs to be clear that they do not exist in columm 2. What he wants to do is he wants the values in column 1 to shift to the row number of the same value in the duplicate values column like shown below.

Column 1    Column 2
ABC123      ABC123
CDE456      CDE456
EFG789      EFG789

Is there a way to do this in excel painlessly with either a macro or an inner excel feature?


This does what your questions requests.

Sub ShowStartingPoints()

Dim rowA As Integer
rowA = 1

Do While (Range("A" & rowA).Value <> "")

Dim row As Integer
row = 1

Dim doesValueExitInB As Boolean
doesValueExitInB = False

Dim valueA As String

valueA = Range("A" & rowA).Value

Do While (Range("B" & row).Value <> "")

If (valueA = Range("B" & row).Value) Then

Range("A" & rowA).Value = ""
Range("A" & row).Value = valueA
doesValueExitInB = True
Exit Do

End If

row = row + 1

Loop 'Loops B col

If Not doesValueExitInB Then

    Range("C" & rowA) = Range("A" & rowA)
    Range("A" & rowA) = ""
    Range("C" & rowA).Interior.ColorIndex = 3
End If

rowA = rowA + 1

Loop ' loops A col

End Sub

Before macro

enter image description here

After macro

enter image description here

There are going to be issues with this, which your example would not pick up on. If you have a long list in column A as I've shown, you will overwrite data because a value replaces another value!

Note, in the first screenshot, A5 has the value No. This is also where CDE456 starts in B5. So, we check where the value of A3 starts in col B. CDE456 in ColB starts on row 5 so it is moved to A5 (as per your request in your question)... Kaboom, we've just written over a value. However, your question doesn't mention this so I will assume it's not an issue (plus, you can always just adapt the code to your needs).

  • This is a terrific answer. Thanks for mentioning that slight flaw because it does create some problems, but we'll see if we can adapt the code to fit our needs. I'll also update if I get a working solution that fixes that slight bug. For now though, I'm accepting this answer. Thanks a lot :) – aug Jul 22 '14 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.