Match, cut and align

[ndendrinos]

Hello,
I've posted earlier re:aligning rows by name and got a solution based on VLOOKUP that works but I think a macro is a better answer for me.
e.g.
........A...............B................C.................D................
....Honda........10.00..................................................
....Buick..........50.00..................................................
....Audi.............3.00
...............................................................................
.......................................Chrysler............11.00.........
.......................................Buick................50.00.........
Data in C:D will always be lower than the ending row in A:B

In theory at least in my mind what the code needs to do is:
Match names in C to A and if match found CUT the matched range C:D and paste moving up to the same row with the matched name in A
At the end in C and D I will have left only the companies that do not exist in A
Hope I explained it well and thank you.

 

[Tazguy37]

Sorting the columns (in pairs) isn't an option?

 

[Yogi Anand]

Hi ndendrinos:

Let us say I start off with data as shown in Sheet2 ...

Book1
	A	B	C	D	E
1
2	Honda
3	Buick
4	Audi
5
6			Chrysler	11.00
7			Buick	50.00
8			Pontiac
9			Audi	72.00
10			Chevy
11
Sheet2

then executing the following code ...

Sub y_3()
    For Each cell In [c6:c10]
        Debug.Print cell.Value
        If Evaluate("=isna(Match(""" & cell & """,a1:a4,0))") = True Then GoTo cont1
        matchrow = Evaluate("=match(""" & cell & """,a1:a4,0)")
        Cells(cell.Row, "D").Copy Cells(matchrow, 2)
cont1:
    Next cell
End Sub

I end up with data as in following Sheet2 (2) ...

Book1
	A	B	C	D	E
1
2	Honda
3	Buick	50.00
4	Audi	72.00
5
6			Chrysler	11.00
7			Buick	50.00
8			Pontiac
9			Audi	72.00
10			Chevy
11
Sheet2 (2)

I hope this helps!

 

[Tazguy37]

I'm just being "nitpicky", I think, but:

Sub y_3()
    For Each cell In [c6:c10]
        Debug.Print cell.Value
        If Evaluate("=isna(Match(""" & cell & """,a1:a4,0))") = False Then
            matchrow = Evaluate("=match(""" & cell & """,a1:a4,0)")
            Cells(cell.Row, "D").Copy Cells(matchrow, 2)
        End If
    Next cell
End Sub

works too. (I really, really, really hate GoTo's)

 

[Yogi Anand]

Hi TazGuy:

Thanks for your comment ... You are absolutely right. In fact I worked the code using both TRUE and FALSE, and somehow ended up posting the one with the TRUE. I generally use GoTos during debugging or making a piece of code work and then don't always clean up the code after the checking and debugging has been done.

 

[Tazguy37]

I generally use GoTos during debugging or making a piece of code work and then don't always clean up the code after the checking and debugging has been done.

I figured that's what happened. It works just fine, I just get the shakes when I see goto's. Comes from the "structured programming" mindset being pounded into my brain for years in school.

 

[jindon]

I think this is what he is asking

Sub test()
Dim r As Range, c As Range
For Each r In Range("a1", Cells(Rows.Count, "a").End(xlUp))
    If Not IsEmpty(r) And r.Offset(, 2) <> r Then
        Set c = Columns("c").Find(r, , , xlWhole)
        If Not c Is Nothing Then
            With c.Resize(, 2)
                .Copy Destination:=r.Offset(, 2)
                .Delete shift:=xlUp
            End With
        End If
        Set c = Nothing
    End If
Next
End Sub

 

[ndendrinos]

Yogi Anand, TazGuy, Jindon
My apologies to all of you for not responding to your kind assistance sooner but I had an emergency to take care of yesterday.

I have tested all your suggestions and Jindon you are right, this is exactly what I need

I am also sure that the solutions from Yogi and TazGuy will help me down the road.

Again thank you all and best regards,
Nick