Hi

My source data is like this

Sub Measure yr mo date Value
33 45 4 4 123
33 45 4 5 113
36 45 4 4 122
36 45 4 5 112


My question is I need to copy data in a different worksheet? When sub and
measure are same it needs to be copied in a same row of diff worksheet. As
soon as anyone of them changes then it needs to be copied in a different row
of diff worksheet

Any help would be appreciated
--
Kittie

Sub AAAA()
Dim lastrow As Long, s1 As Variant, s2 As Variant
Dim i As Long, col As Long, rw As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

s1 = Cells(2, 1).Value
s2 = Cells(2, 2).Value
col = 0
rw = 1
For i = 2 To lastrow
If Cells(i, 1) = s1 And Cells(i, 2) = s2 Then
col = col + 1
Else
col = 1
rw = rw + 1
s1 = Cells(i, 1)
s2 = Cells(i, 2)
End If
Worksheets("sheet2").Cells(rw, col) = Cells(i, 5).Value
Next


End Sub

--
Regards,
Tom Ogilvy



"Lolly" wrote in message
...
Hi

My source data is like this

Sub Measure yr mo date Value
33 45 4 4 123
33 45 4 5 113
36 45 4 4 122
36 45 4 5 112


My question is I need to copy data in a different worksheet? When sub and
measure are same it needs to be copied in a same row of diff worksheet.
As
soon as anyone of them changes then it needs to be copied in a different
row
of diff worksheet

Any help would be appreciated
--
Kittie

I saw your value column as column E (5th column). If it is the 4th
column/column D, change
Worksheets("sheet2").Cells(rw, col) = Cells(i, 5).Value

to

Worksheets("sheet2").Cells(rw, col) = Cells(i, 4).Value

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub AAAA()
Dim lastrow As Long, s1 As Variant, s2 As Variant
Dim i As Long, col As Long, rw As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

s1 = Cells(2, 1).Value
s2 = Cells(2, 2).Value
col = 0
rw = 1
For i = 2 To lastrow
If Cells(i, 1) = s1 And Cells(i, 2) = s2 Then
col = col + 1
Else
col = 1
rw = rw + 1
s1 = Cells(i, 1)
s2 = Cells(i, 2)
End If
Worksheets("sheet2").Cells(rw, col) = Cells(i, 5).Value
Next


End Sub

--
Regards,
Tom Ogilvy



"Lolly" wrote in message
...
Hi

My source data is like this

Sub Measure yr mo date Value
33 45 4 4 123
33 45 4 5 113
36 45 4 4 122
36 45 4 5 112


My question is I need to copy data in a different worksheet? When sub
and
measure are same it needs to be copied in a same row of diff worksheet.
As
soon as anyone of them changes then it needs to be copied in a different
row
of diff worksheet

Any help would be appreciated
--
Kittie

Hi
Tom

Thanyou very much it helped me a lot
I had one more query in this


It's as follows

Write now with that code it's copying in continous rows in other worksheet.
Like this

123 124 234
235 345 567
123


I want to do like this


1 Value 123 124 234
2
3 Value1 235 345..
4
5
6 Value 123
and so on

If you could help me on that
it would be great

Thanx a lot for providing me help.

"Tom Ogilvy" wrote:

Sub AAAA()
Dim lastrow As Long, s1 As Variant, s2 As Variant
Dim i As Long, col As Long, rw As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

s1 = Cells(2, 1).Value
s2 = Cells(2, 2).Value
col = 0
rw = 1
For i = 2 To lastrow
If Cells(i, 1) = s1 And Cells(i, 2) = s2 Then
col = col + 1
Else
col = 1
rw = rw + 1
s1 = Cells(i, 1)
s2 = Cells(i, 2)
End If
Worksheets("sheet2").Cells(rw, col) = Cells(i, 5).Value
Next


End Sub

--
Regards,
Tom Ogilvy



"Lolly" wrote in message
...
Hi

My source data is like this

Sub Measure yr mo date Value
33 45 4 4 123
33 45 4 5 113
36 45 4 4 122
36 45 4 5 112


My question is I need to copy data in a different worksheet? When sub and
measure are same it needs to be copied in a same row of diff worksheet.
As
soon as anyone of them changes then it needs to be copied in a different
row
of diff worksheet

Any help would be appreciated
--
Kittie

do you want the literal term "Value" placed in column 1 (if so, why Value
in row 1 and Value1 in row3 then back to Value in row 6).

Also, how do you determine how many rows to skip. You skip one row ( 2),
then you skip two rows (4 and 5).

If there is a pattern there, I don't see it.

--
Regards,
Tom Ogilvy

"Lolly" wrote in message
...
Hi
Tom

Thanyou very much it helped me a lot
I had one more query in this


It's as follows

Write now with that code it's copying in continous rows in other
worksheet.
Like this

123 124 234
235 345 567
123


I want to do like this


1 Value 123 124 234
2
3 Value1 235 345..
4
5
6 Value 123
and so on

If you could help me on that
it would be great

Thanx a lot for providing me help.

"Tom Ogilvy" wrote:

Sub AAAA()
Dim lastrow As Long, s1 As Variant, s2 As Variant
Dim i As Long, col As Long, rw As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

s1 = Cells(2, 1).Value
s2 = Cells(2, 2).Value
col = 0
rw = 1
For i = 2 To lastrow
If Cells(i, 1) = s1 And Cells(i, 2) = s2 Then
col = col + 1
Else
col = 1
rw = rw + 1
s1 = Cells(i, 1)
s2 = Cells(i, 2)
End If
Worksheets("sheet2").Cells(rw, col) = Cells(i, 5).Value
Next


End Sub

--
Regards,
Tom Ogilvy



"Lolly" wrote in message
...
Hi

My source data is like this

Sub Measure yr mo date Value
33 45 4 4 123
33 45 4 5 113
36 45 4 4 122
36 45 4 5 112


My question is I need to copy data in a different worksheet? When sub
and
measure are same it needs to be copied in a same row of diff
worksheet.
As
soon as anyone of them changes then it needs to be copied in a
different
row
of diff worksheet

Any help would be appreciated
--
Kittie