How to change the following data

[Guest]

A B C A B
C
1 David 123 456 to David 789 000
2 789 000 CK 135
246
3 CK 135 246 CK 348 256
4 CK 348 256 Steve hij
kem
5 Steve abc efg
6 hij kem

Because i hv large amount of data,i don't want to copy and paste 1 by 1

 

[Bob Phillips]

You need to explain the rules as to why the source should transform to the
target coz it ain't obvious to me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

oh no,iz like this

A B C

1 David 123 456
2 789 000
3 CK 135 246
4 CK 348 256
5 Steve abc efg
6 hij kem

to
A B C
1 David 789 000
2 CK 135 246
3 CK 348 256
4 Steve hij kem
"Bob Phillips" 來函：

 

[Bryan Hessey]

Unique identifier in A receives the last two data items?

also, define Unique identifier

--

 

[Guest]

Yes,that is what I want to put the last two data items to the upper part if
that 2items the cell before is blank

"Bryan Hessey" 來函：

 

[Bob Phillips]

That is hardly an explanation of the rules, just a re-statement of the data.
Maybe this will do it, but maybe not, so if not, give us more help, don't
leave us to second-guess what you are thinking

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = "" Then
Cells(i, "B").Resize(, 2).Copy Cells(i - 1, "B")
Rows(i).Delete
End If
Next i

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bryan Hessey]

Hi Bob, as you say, a very poor explanation of what is required, but I
think the OP was after something like:


Code:
--------------------
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i + 1, "C").Value = "" Then
'Cells(i, "B").Resize(, 2).Copy Cells(i - 1, "B")
Cells(i, "B").Value = Cells(i + 1, "A").Value
Cells(i, "C").Value = Cells(i + 1, "B").Value
Rows(i + 1).Delete
End If
Next i