Screen shot attached Please advise

its_subu

New Member
Joined
Nov 19, 2005
Messages
7
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
1IdNameLocationaddressstateSitenophoneFax
210001JohnBuildingA1WestAveNYSITE00001(000)999-2222
310001JohnBuildingA1WestAveNYSITE00001(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333
510002peterBuildingB13KarelAvenueNYSITE00720(111)384-2000
Sheet3




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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,532
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 19, 2005
Messages
7
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

Active Member
Joined
Mar 8, 2005
Messages
365

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
1IdNameLocationaddressstateSite nophoneFax
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")),"")
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,269
Messages
5,577,108
Members
412,768
Latest member
klig
Top