#### its_subu

##### New Member
Hello
The first screen shot is what Im having. The desired result is also below. Please advice. Also I dont know why nbsp is coming. They are supposed to be blank fields.
Book1.xls
ABCDEFGH
210001JohnBuildingA1WestAveNYSITE00001(000)999-2222
310001JohnBuildingA1WestAveNYSITE00001(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333
510002peterBuildingB13KarelAvenueNYSITE00720(111)384-2000
Sheet3

Result needed is
Book1.xls
ABCDEFGH
310001JohnBuildingA1WestAveNYSITE00001(000)999-2222(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333(111)384-2000
Sheet2

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Is the data on Sheet3 sorted on column A in ascending order?

Also I dont know why nbsp is coming. They are supposed to be blank fields.
You can avoid those by clicking the 'View Source' button in the HTML Maker (rather than the 'Please click this button to send the source to the clipbord' button) and then selecting all the resulting code to copy to your post.

Yes
The data is sorted in sheet 3. Please let me know how I can get the desired result as in screenshot two from screen shot one.

This is a VBA approach to your solution....However, I do think that if you take a look at the ASAP Utility pack, you would be able to address your situation (and other similar scenarios) pretty fast in the future.

Code:
``````Sub yy()

Dim lastrow As Long
lastrow = Range("H65536").End(xlUp).Row

Dim lastrow2 As Long
lastrow2 = Range("G65536").End(xlUp).Row

For i = 2 To lastrow Step 2

If Cells(i, 8) = "" Then
Cells(i, 8).Value = Cells(i, 8).Offset(1, 0)
End If
Next

For k = 3 To lastrow2 Step 1

If Cells(k, 7) = "" Then
Cells(k, 7).EntireRow.Delete
End If
Next

End Sub``````

its_subu said:
Yes
The data is sorted in sheet 3. Please let me know how I can get the desired result as in screenshot two from screen shot one.

Given:
Book8
ABCDEFGH
210001JohnBuildingA1WestAveNYSITE00001(000)999-2222
310001JohnBuildingA1WestAveNYSITE00001(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333
510002peterBuildingB13KarelAvenueNYSITE00720(111)384-2000
Sheet3

which is sorted on column A in ascending order...

A2, copied down:

=IF(LOOKUP(B2,Sheet3!\$A\$2:\$A\$5)=B2,MATCH(B2,Sheet3!\$A\$2:\$A\$5,1),0)

C2, copied across then down:

=IF(N(\$A2),INDEX(Sheet3!B\$2:B\$5,\$A2-(C\$1="Phone")),"")

normally i would not like to give a solution less elegant fhan what the mvp has given. however I did this exercise and my sub is

Dim myrange As range
Dim cell As range
Sub test()

range("G2").Select
Selection.Insert Shift:=xlDown
Set myrange = range(range("a1"), range("a1").End(xlDown))
For Each cell In myrange
If cell = cell.Offset(1, 0) Then cell.EntireRow.Delete
Next
MsgBox "macro over"
End Sub
modify to suit you.

Replies
11
Views
918
Replies
6
Views
1K
Replies
3
Views
569
Replies
8
Views
415
Replies
2
Views
2K

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

### 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