Screen shot attached Please advise

[its_subu]

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
	A	B	C	D	E	F	G	H
1	Id	Name	Location	address	state	Siteno	phone	Fax
2	10001	John	BuildingA	1WestAve	NY	SITE00001	(000)999-2222
3	10001	John	BuildingA	1WestAve	NY	SITE00001		(111)222-4444
4	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720	(111)222-3333
5	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720		(111)384-2000
Sheet3

Result needed is

Book1.xls
	A	B	C	D	E	F	G	H
2	Id	Name	Location	address	state	Siteno	phone	Fax
3	10001	John	BuildingA	1WestAve	NY	SITE00001	(000)999-2222	(111)222-4444
4	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720	(111)222-3333	(111)384-2000
Sheet2

 

[Aladin Akyurek]

Is the data on Sheet3 sorted on column A in ascending order?

 

[Peter_SSs]

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.

 

[its_subu]

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.

 

[poorwallace]

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.

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

 

[Aladin Akyurek]

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
	A	B	C	D	E	F	G	H
1	Id	Name	Location	address	state	Site no	phone	Fax
2	10001	John	BuildingA	1WestAve	NY	SITE00001	(000)999-2222
3	10001	John	BuildingA	1WestAve	NY	SITE00001		(111)222-4444
4	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720	(111)222-3333
5	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720		(111)384-2000
Sheet3

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

Book8
	A	B	C	D	E	F	G	H	I
1	Pos	Id	Name	Location	address	state	Siteno	phone	Fax
2	2	10001	John	BuildingA	1WestAve	NY	SITE00001	(000)999-2222	(111)222-4444
3	4	10002	peter	BuildingB	13KarelAvenue	NY	SITE00720	(111)222-3333	(111)384-2000
4	0	99999
5
Sheet2

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")),"")

 

[venkat1926]

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.