# Match, cut and align

#### ndendrinos

##### Well-known Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sorting the columns (in pairs) isn't an option?

Hi ndendrinos:

Let us say I start off with data as shown in Sheet2 ...
Book1
ABCDE
1
2Honda
3Buick
4Audi
5
6Chrysler11.00
7Buick50.00
8Pontiac
9Audi72.00
10Chevy
11
Sheet2

then executing the following code ...
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
ABCDE
1
2Honda
3Buick50.00
4Audi72.00
5
6Chrysler11.00
7Buick50.00
8Pontiac
9Audi72.00
10Chevy
11
Sheet2 (2)

I hope this helps!

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

Code:
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)

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.

Yogi Anand said:
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.

I think this is what he is asking
Code:
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

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

Replies
0
Views
119
Replies
10
Views
612
Replies
9
Views
507
Replies
0
Views
305
Replies
7
Views
390

1,196,203
Messages
6,013,991
Members
441,800
Latest member
CDra13

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back