Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
            ABC123
            CDE456
            CDE456
            CDE456
            EFG789

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
            ABC123
            ABC123
            ABC123
CDE456      CDE456
            CDE456
            CDE456
EFG789      EFG789

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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).

share|improve this answer
    
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 at 0:24

Consider the following macro...............it does not require any initial data in column A

Sub fixA()
    Dim N As Long, i As Long
    Range("A:A").Clear
    [A1] = [b1]
    v = [b1]
    N = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To N
        If Cells(i, "B") = Cells(i - 1, "B") Then
            Cells(i, "A") = ""
        Else
            Cells(i, "A") = Cells(i, "B")
            v = Cells(i, "B")
        End If
    Next i
End Sub
share|improve this answer
    
Not sure if this works for all cases... you clear column A? I think what I meant to say is column B has those values but sometimes not all of em. If there was a value in column A that wasn't in column B, we'd like to know about that –  aug Jul 19 at 16:04
    
Backup the workbook and try it out! –  Gary's Student Jul 19 at 16:08
    
Yeah as I expected if I have values not listed in column 1 that aren't mentioned in column 2, it simply deletes them... that's pretty tragic :/ it does help with my situation though. Anyway we can make it so that if it finds a value that isn't in column 2, it simply appends it to the end of column 1? –  aug Jul 19 at 23:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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