Need Help. Please

[its_subu]

Hello
Nice to see this site.

I have an long excel sheet of the following format

Number Name Id address phone fax
1 aa 2 abcd 123
1 aa 2 abcd 456
2 bb 3 qwer 789
2 bb 3 qwer 111


456 and 111 are fax nos where as 123 and 789 are phone numbers. How do I merge 1 and 1 which has identical values and fill the fax value in the first field.

Please let me know.

 

[Smitty]

Welcome to the Board!

You can concatenate cells with the ampersand. I.E. =A1&B1

As for fillin the FAX field, I'm not exactly sure what you mean (how do you know the difference between a FAX & non-Fax #?). Can you post a shot of your sheet? Colo's HTML Maker (the link's at the bottom of the page) will let you do that.

Hope that helps,

Smitty

 

[J88L]

Assuming the first row of actual data is in A2, this works:

Sub MoveFax()

Dim RowIncrement As Integer

Range("A2").Select

For RowIncrement = 1 To 20000 ' Enter how many rows you have here

If ActiveCell.Offset(1, 0).Value = "" Then Exit For
        
ActiveCell.Offset(1, 4).Range("A1").Copy
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
    
Next RowIncrement

End Sub

 

[its_subu]

Screen shots pasted

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