Consolidating Multiple Rows of Data Into Single Row

[Axiomics]

Hi there. Can anyone shed some light please ... would be very grateful!

I have following data in Excel file :

Name No Date Time
--------- --------- --------- ----------
John 1 1-1-2014 08:00
John 1 1-1-2014 13:00
John 1 1-1-2014 14:00
John 1 1-1-2014 17:00
Kate 2 1-1-2014 09:00
Kate 2 1-1-2014 12:00
Kate 2 1-1-2014 13:00
Kate 2 1-1-2014 18:00
...

What I am looking to do is run a macro to consolidate the data into a new worksheet in the same workbook to look like the following:

Name No Date IN OUT IN OUT >>>
----------- --- ---------- ----- ----- ----- -----
John 1 1-1-2014 08:00 13:00 14:00 17:00 >>>
Kate 2 1-1-2014 09:00 12:00 13:00 18:00 >>>

In other words:

1. I need the row data for each person for each DAY in one line
2. There could be up to 8 records per day per person - eg. In and OUT 4 times
3. All John's entries for the month will be first, then all Kate's etc


Nothing I have tried works ... suspect I need some code to do this!

I am at a loss. Any takers?

 

[Andrzejek]

It would be a lot easier to read if you would format your data.

Is that what you have?

[pre]
A B C D
Name No Date Time
------ --- --------- ----------
John 1 1-1-2014 08:00
John 1 1-1-2014 13:00
John 1 1-1-2014 14:00
John 1 1-1-2014 17:00
Kate 2 1-1-2014 09:00
Kate 2 1-1-2014 12:00
Kate 2 1-1-2014 13:00
Kate 2 1-1-2014 18:00
[/pre]

So if the Name (A) and No (B) changes, you want to have another row in Sheet2?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Axiomics]

Hi Andy,

Thanks for reply.

Sorry about formatting! It was fine until I submitted the post???

Change in Name (A), No (B) and Date (C) should result in new line.

Each row should contain the times for each person PER DAY with possibility of up to 8 times on record.

Hope this makes sense?

 

[Andrzejek]

Formatting - use PRE tag, it PREserves the spacing. Very halpful.

"Nothing I have tried works " - did you try PIVOT TABLES? Looks it should do what you want, no code required.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Just a few comments.

"Nothing I have tried works"

You never even hinted at what it was you tried. You statement implies that you tried at least two different solutions, yet we know nothing of what you attempted. How then could we help in that respect?

Your data has no IN/OUT. In & Out can only be assumed: for any Name/No/Date each In must be followed by an Out. However it seems possible that a scenario like this could exist. Joe works late for whatever reason and does not exit the gate until 12:01 AM. Or he enters at 11:59 PM. Do those instances present a problem?

Then you state that there is a maximum of 8 time values (4 In/Out pairs). I could imagine more than 4 In/Out occurring.

Anyhow, Ands's PivotTable suggestion is a good possibility!

 

[Andrzejek]

Since it is a last day of the year, slow day here, and this is your first time at TT (welcome to Tek-Tips ) you may try this piece of code (assuming your data starts at row 2 on Sheet1, there are no empty rows, and there is a Sheet2)

Option Explicit

Private Sub Test()

Dim i As Integer
Dim strName As String
Dim intNo As Integer
Dim datDate As Date
Dim strTime As String

Dim intS2Row As Integer
Dim intS2Col As Integer

i = 2
intS2Row = 2
intS2Col = 4

With Sheet1
    Do While .Range("A" & i).Value <> ""
        If strName = .Range("A" & i).Value And _
            intNo = .Range("B" & i).Value And _
            datDate = CDate(.Range("C" & i).Value) Then
            
            intS2Col = intS2Col + 1
            Sheet2.Cells(intS2Row, intS2Col) = .Range("D" & i).Value
        Else
            intS2Row = intS2Row + 1
            intS2Col = 4
            
            strName = .Range("A" & i).Value
            intNo = .Range("B" & i).Value
            datDate = CDate(.Range("C" & i).Value)
            strTime = .Range("D" & i).Value
            
            Sheet2.Range("A" & intS2Row & ":D" & intS2Row).Value = _
                Array(strName, intNo, datDate, strTime)
        End If
        i = i + 1
    Loop
End With

End Sub

For future 'VBA code' questions please use

http://www.tek-tips.com/threadminder.cfm?pid=707

happy new year

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

There is a decent discussion of a range concatenate function at ExcelTips.net. Make sure you read the thread since the original code had some errors.

Use the Preview button prior to posting. I typically find lots of updates I needed to make prior to submitting.

Duane
Hook'D on Access
MS Access MVP

 

[Axiomics]

Hi Andy & Skip,

Happy New Year to you both too! Thank you for the replies.

The background is this - we have bio-metric time & attendance units at our stores and the software supplied with it generates at least a 6 page report per staff member per month (which is not the Green way to go at all!) The company that supplied is not interested in changing the reports at all! So my idea is to pull in the raw CSV files into Crystal Reports to generate a single page report.

What I have tried to date - bearing in mind I can't code worth a damn:
[ul]
[li]My first round was in Crystal Reports with very helpful guys (like yourselves) on a support forum using a cross-tab report. I could just not get the report to generate the 8 IN & OUT columns ...[/li]
[li]Then we tried formulas (with arrays and hectic coding and stuff) and hit a wall with time calculations in Crystal and I started to feel bad about giving everyone the round-around! Eventually, the guys recommended manipulating the data in excel before pulling it into Crystal ...[/li]
[li]I tried recording a macro in Excel, but couldn't get it to automatically start a new line for the new date...[/li]
[li]The data is too much to manipulate by hand and would take too long, then I could just as well print the hundreds of pages from the software anyway![/li]
[li]My last version of the Crystal report looks like it will work for me if I can get the output to look like my request in this forum ... hence my post. Didn't think it necessary to post all the above from the start ...[/li]
[/ul]

Skip, your assumption regarding the IN/OUT is correct. I have worked through a few years' data and nobody has ever clocked In/Out more than 4 times a day.

Andy, I will try your code! Thank you for your time and assistance! Appreciate it ...

Regards

 

[dhookom]

An Access report with a multi-column subreport would work with no code.

Duane
Hook'D on Access
MS Access MVP

 

[Axiomics]

Thanks dhookom ... Will give it a try!