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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
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,123,436
Messages
5,601,671
Members
414,466
Latest member
Jools23

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top