VBA to extract data to a single row

[garyperegrino]

Hi Everyone,

I got a problem on our timekeeping logs. Logs been printed as below:

PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In
PD OPERATOR Cruz, Maria 4/1/2019 8:07:57 PM 040119 C/Out
PD OPERATOR Cruz, Maria 4/2/2019 6:56:09 AM 040219 C/In
PD OPERATOR Cruz, Maria 4/2/2019 7:02:25 PM 040219 C/Out
PD OPERATOR Pablo, Juan 4/1/2019 9:40:10 AM 040119 C/In
PD OPERATOR Pablo, Juan 4/1/2019 8:01:02 PM 040119 C/Out
PD OPERATOR Pablo, Juan 4/2/2019 6:16:44 AM 040219 C/In
PD OPERATOR Pablo, Juan 4/2/2019 7:31:32 PM 040219 C/Out

I want the C/Out of Maria on row 2 on April 1 2019 should be in row of Maria on C/In. Below is the output:

PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In 4/1/2019 8:07:57 PM 040119 C/Out
PD OPERATOR Cruz, Maria 4/2/2019 6:56:09 AM 040219 C/In 4/2/2019 7:02:25 PM 040219 C/Out
PD OPERATOR Pablo, Juan 4/1/2019 9:40:10 AM 040119 C/In 4/1/2019 8:01:02 PM 040119 C/Out

Is this possible on VBA? Any advice and help really appreciated. Thank you very much

[godlev]

@garyperegrino - do you really need VBA ?
check if the attached file which is combination of table with formulas and pivot table that gives you flexibility

[garyperegrino]

Hi Godlev - This is the format of my excel.

Column A Column B Column C Column D Column E
PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In
PD OPERATOR Cruz, Maria 4/1/2019 8:07:57 PM 040119 C/Out
PD OPERATOR Cruz, Maria 4/2/2019 6:56:09 AM 040219 C/In
PD OPERATOR Cruz, Maria 4/2/2019 7:02:25 PM 040219 C/Out
PD OPERATOR Pablo, Juan 4/1/2019 9:40:10 AM 040119 C/In
PD OPERATOR Pablo, Juan 4/1/2019 8:01:02 PM 040119 C/Out
PD OPERATOR Pablo, Juan 4/2/2019 6:16:44 AM 040219 C/In
PD OPERATOR Pablo, Juan 4/2/2019 7:31:32 PM 040219 C/Out

Then the output should be like this.

Column A Column B Column C Column D Col E Column F Column G Column H
PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In 4/1/2019 8:07:57 PM 040119 C/Out
PD OPERATOR Cruz, Maria 4/2/2019 6:56:09 AM 040219 C/In 4/2/2019 7:02:25 PM 040219 C/Out
PD OPERATOR Pablo, Juan 4/1/2019 9:40:10 AM 040119 C/In 4/1/2019 8:01:02 PM 040119 C/Out

Just need to move the C/out of Maria aligned to C/In on per date basis.

Thank you very much for your support.

[jolivanes]

If the data as shown is (I did not download your attachment) in Column A and starts at the 2nd Row, put this formula in B2.

Please Login or Register  to view this content.

Copy and paste the formula to every 2nd Row after this (B4, B6, B8 etc)
Copy all of Column B and paste again as values.
Delete the odd Rows (3, 5, 7 etc)
If you want this done with code, let us know.

[garyperegrino]

Hi Jolivanes, Thank you for your response. Yes I want it done with a code. But these data is separated on every column:

Column A Column B Column C Column D Column E
PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In
PD OPERATOR Cruz, Maria 4/1/2019 8:07:57 PM 040119 C/Out
PD OPERATOR Cruz, Maria 4/2/2019 6:56:09 AM 040219 C/In
PD OPERATOR Cruz, Maria 4/2/2019 7:02:25 PM 040219 C/Out
PD OPERATOR Pablo, Juan 4/1/2019 9:40:10 AM 040119 C/In
PD OPERATOR Pablo, Juan 4/1/2019 8:01:02 PM 040119 C/Out
PD OPERATOR Pablo, Juan 4/2/2019 6:16:44 AM 040219 C/In
PD OPERATOR Pablo, Juan 4/2/2019 7:31:32 PM 040219 C/Out

If Maria has C/Out on The same date, it will move this row on C/IN row like this one:

Column A Column B Column C Column D Col E Column F Column G Column H
PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In 4/1/2019 8:07:57 PM 040119 C/Out

THank you very much for your support.

[godlev]

did you check the attachment ?

[garyperegrino]

Hi godlev, Yes I already checked it. On the attachment, on this data - "PD OPERATOR Cruz, Maria 4/1/2019 6:56:28 AM 040119 C/In" it is on one column only.

My data is separated by columns. Please checked attached timekeeping file. Thank you for your utmost support.

[JeteMc]

This proposal employs three helper columns (F2:H15) which may be moved and/or hidden for aesthetic purposes.
The helper columns are populated using: =IF(OR($A2="",$E3="",$E3="C/In"),"",C3)
The output (K2:R15) is populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.