I am newbie to excel, Please help!! I have one excel sheet, on which I
have some 2000 values (going larger) on column C, these values start
with V and A, what i want is to copy all the values starting from V
from this coumn C to Column B. These values should be next to the
values in column C which starts with A.

Pls help

Is it one V followed by one A, otherwise which A do you place it by? Which
comes first V or A?

Show some sample data and required results.


--
HTH

Bob Phillips

"kishore" wrote in message
oups.com...
I am newbie to excel, Please help!! I have one excel sheet, on which I
have some 2000 values (going larger) on column C, these values start
with V and A, what i want is to copy all the values starting from V
from this coumn C to Column B. These values should be next to the
values in column C which starts with A.

Pls help

Based solely upon what I think you are saying:

A B C
1 MyData
2 V123
3 V234
4 V345
5 V123 A123
6 V234 A234
7 V345 A345

My Cell B2 contains
=IF(LEFT(C2,1)="V","",INDEX($C$2:$C$7,MATCH(RIGHT( C2,(LEN(C2)-1)),RIGHT($C$2
:$C$7,(LEN($C$2:$C$7)-1)),0),1))

This formula is a Control Array Type (CSE) requiring that when entered
you must use the keyboard holding down the Control Key and The Shift Key
then also pressing the Enter Key (All Simultaneously),, the entry will then
show up in the formula bar as

{=IF(LEFT(C2,1)="V","",INDEX($C$2:$C$7,MATCH(RIGHT (C2,(LEN(C2)-1)),RIGHT($C$
2:$C$7,(LEN($C$2:$C$7)-1)),0),1))} --- the { } on the front and the
back - yo can not manualy enter these's {}'s..

Someone will probably offer a better suggestion, but this demo worked for
me..
Hope this helps,
Jim May



"kishore" wrote in message
oups.com...
I am newbie to excel, Please help!! I have one excel sheet, on which I
have some 2000 values (going larger) on column C, these values start
with V and A, what i want is to copy all the values starting from V
from this coumn C to Column B. These values should be next to the
values in column C which starts with A.

Pls help

I forgot to put the sample of the file, I wanted to do it using macro,
so that i can call this in my existing macro.

Col A Col B Col C Col D
Col E
1
2
3
4 A12345
5 A23456
6 A13567
7 V12345
8 V23456
9 V13567

What I want is a macro which do the following:

Col A Col B Col C Col D
Col E
1
2
3
4 V12345 A12345
5 V23456 A23456
6 V13567 A13567
7
8
9

Thanks guys in advance!!

Sub MoveData()
Dim iLastRow As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
'ignore initial blank rows
iRow1 = 1
Do Until Cells(iRow1, "C").Value < ""
iRow1 = iRow1 + 1
Loop
iRow2 = iRow1
For i = iRow1 To iLastRow
If Left(Cells(i, "C").Value, 1) = "A" Then
If i < iRow1 Then
Cells(iRow1, "C").Value = Cells(i, "C").Value
Cells(i, "C").Value = ""
End If
iRow1 = iRow1 + 1
Else
Cells(iRow2, "B").Value = Cells(i, "C").Value
Cells(i, "C").Value = ""
iRow2 = iRow2 + 1
End If
Next i

End Sub



--
HTH

Bob Phillips

"kishore" wrote in message
oups.com...
I forgot to put the sample of the file, I wanted to do it using macro,
so that i can call this in my existing macro.

Col A Col B Col C Col D
Col E
1
2
3
4 A12345
5 A23456
6 A13567
7 V12345
8 V23456
9 V13567

What I want is a macro which do the following:

Col A Col B Col C Col D
Col E
1
2
3
4 V12345 A12345
5 V23456 A23456
6 V13567 A13567
7
8
9

Thanks guys in advance!!

Excellent, Perfect, Many thanks Bob, another easy question, is it
possible to get some code to delete the all the rows for which there is
no value in Col C, asking this because once the values from column C
moved to Column B, I want remove all those values for which there is no
value in Col C.

Many thanks,
Regards

Here you are, just run it after the first.

Sub DeleteData()
Dim iLastRow As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "C").Value = "" Then
Rows(i).delete
End If
Next i

End Sub




--
HTH

Bob Phillips

"kishore" wrote in message
ups.com...
Excellent, Perfect, Many thanks Bob, another easy question, is it
possible to get some code to delete the all the rows for which there is
no value in Col C, asking this because once the values from column C
moved to Column B, I want remove all those values for which there is no
value in Col C.

Many thanks,
Regards

Hi Bob,
Thanks for quick reply, but somehow I am unable to make it work, here
are my enteries, Is it possible to remove all the rows for which col C
dont have the values. This is how my sheets has the values.
Col A Col B Col C Col D Col E
ColF.......

05ID2862P1050 V31910 A08910 3211 3385 222 396 09/05/2005
05ID2862P1050 V31911 A08911 3386 3560 397 571 09/05/2005
05ID2862P1050 V31912 A08912 3561 3735 572 746 09/05/2005
05ID2862P1050 V31913 A08913 3736 3850 747 854 09/05/2005
05ID2214P1001 V31914 3607 3545 118 180 30/04/2005
05ID2214P1001 V31915 3544 3370 181 355 30/04/2005
05ID2214P1001 V31916 3369 3195 356 530 30/04/2005

And What I want to remove aal the rows for which there is no entry in
Col C, I can do it manually but.....
Many Thanks,
P.S - How to learn more abt Macro....any advice...Pls

Regds,
Kishore

Kishore,

Try this mod

Sub DeleteData()
Dim iLastRow As Long
Dim iRow1 As Long
Dim iRow2 As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Trim(Cells(i, "C").Value) = "" Then
Rows(i).Delete
End If
Next i

End Sub



--
HTH

Bob Phillips

"kishore" wrote in message
oups.com...
Hi Bob,
Thanks for quick reply, but somehow I am unable to make it work, here
are my enteries, Is it possible to remove all the rows for which col C
dont have the values. This is how my sheets has the values.
Col A Col B Col C Col D Col E
ColF.......

05ID2862P1050 V31910 A08910 3211 3385 222 396 09/05/2005
05ID2862P1050 V31911 A08911 3386 3560 397 571 09/05/2005
05ID2862P1050 V31912 A08912 3561 3735 572 746 09/05/2005
05ID2862P1050 V31913 A08913 3736 3850 747 854 09/05/2005
05ID2214P1001 V31914 3607 3545 118 180 30/04/2005
05ID2214P1001 V31915 3544 3370 181 355 30/04/2005
05ID2214P1001 V31916 3369 3195 356 530 30/04/2005

And What I want to remove aal the rows for which there is no entry in
Col C, I can do it manually but.....
Many Thanks,
P.S - How to learn more abt Macro....any advice...Pls

Regds,
Kishore

Hi Bob,

It doesnt work, never mind....thanks a lot for your help!!

Cheers
kishore

Wanna post me the workbook?

--
HTH

Bob Phillips

"kishore" wrote in message
ups.com...
Hi Bob,

It doesnt work, never mind....thanks a lot for your help!!

Cheers
kishore