macro criteria copy

P

puiuluipui

Hi, i have a dabase like this...

A B C D
11/01 7:23:30 in ADRIAN
11/01 16:05:29 out ADRIAN
11/01 17:15:02 out ADRIAN
11/01 7:23:44 IN ALEC
11/01 16:04:34 out ALEC
11/03 12:17:22 IN ALEC
11/03 21:10:30 out ALEC
.....
....and i need a macro to extract datas in another sheet, like this:

G H I J

data pers IN out
11/01 ADRIAN 7:23:30 16:05:29
11/01 ADRIAN 17:15:02
11/01 ALEC 7:23:44 16:04:34
11/03 ALEC 12:17:22 21:10:30

I need everything to be sorted by name and date, ascending.

Can this be done?

Thanks in advance.
 
B

Bob Phillips

Public Sub ProcessData()
Dim sh As Worksheet
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

Set sh = Worksheets("Sheet2")
sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out")
NextRow = 2

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then

If LCase(.Cells(i, "C").Value) = "in" And _
LCase(.Cells(i + 1, "C").Value) = "out" Then

sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value
sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value
sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value
sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value
i = i + 1
Else

sh.Cells(i, "G").Value = .Cells(i, "A").Value
sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value
If LCase(.Cells(i, "C").Value) = "in" Then

sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value
Else

sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value
End If
End If
Else

sh.Cells(i, "G").Value = .Cells(i, "A").Value
sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value
If LCase(.Cells(i, "C").Value) = "in" Then

sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value
Else

sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value
End If
End If

NextRow = NextRow + 1
Next i
End With

End Sub
 
P

puiuluipui

Hi, on sheet 2 tab>right click>view code>than paste your code.
I did the same in sheet 1 tab, but nothing. What am i doing wrong?
Maybe the country region? I mean "comma" and other signs? "," with ";" ?
I dont know. Maybe it's something even more simple.
Hope you can figure this out.
Thaks in advance.
 
P

puiuluipui

Hi, i put it in sheet 2 tab>right click>view code>than paste the code.
it's not right?
 
P

puiuluipui

Hi, on sheet 2 tab>right click>view code>than paste your code.
I did the same in sheet 1 tab, but nothing. What am i doing wrong?
Maybe the country region? I mean "comma" and other signs? "," with ";" ?
I dont know. Maybe it's something even more simple.
Hope you can figure this out.
Thaks in advance.
 
P

puiuluipui

Hi, it's working. i had problems because i exported the data from a program
and some cells had space befor the text. But i have one problem.
I have :

A B C D
11/01 7:23:30 IN ADRIAN
11/01 16:05:29 out ADRIAN
11/01 17:15:02 out ADRIAN

In the same date " 11/01" i have two rows with "out" and one row with "IN"
After i run the macro, it doesn't shows the date. Something like that:
sheet 2 (macro)
11/01 ADRIAN 7:23:30 16:05:29
ADRIAN 17:15:02


It doesn't matter if i have one "IN" and two "out" or one "out" and two
"IN", in the date column, it doesn't show the date. I have with the same
date, a lot of "IN" and a lot of "out". Not necessary the same number of
"IN" and "out". If there is an empty cell in the "macro result sheet", it
doesn't show the date. Is there a way to show the date even the cell it's
blank? Even it has only one "IN" or one "out"?


Thanks in advance.



"Bob Phillips" a scris:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top