Create a pivot look a like without calculation, only showing some values 3

[Bilberry]

Hi All,
I want to create a macro to create a pivot-like table. I dont want to do calculation, but only want to show the values. Here is the content of the Excel file:

Column A Column B Column C Column D
16-05-2014 15:47 Machine1 Peter Not started
16-05-2014 15:47 Machine1 Mike Not started
16-05-2014 15:47 Machine1 Peter Started
16-05-2014 15:47 Machine1 John Started
16-05-2014 15:47 Machine1 John Unknown


I want to put Column A as Column and want to have Column B as the row. The values should be Column C / Column D (with a slash). I have tried that with a pivot table, but could not show Column C and Column D (the values). I can do only calculations, like sum, avg. I dont want to do calculations. Is it possible to create a simple macro which can create the output which i have described?

A star for the golden solution..

 

[Andrzejek]

Well, the machine names could be a problem.
What if you would have 'Machine2' in rows 4 and 5? How would you display it?

I would really concentrate on answering Skip’s questions. In plain English: what do you really need to have. That will get you on the right track a lot faster than trying to work with this little sample of your data and us guessing.


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.

 

[Bilberry]

I have change the code, and it looks like that i want to have such an export:

Sub Macro1()
Dim c As Integer

c = 2
Do While Worksheets(1).Range("A" & c).Value <> ""
    Worksheets(2).Cells(c, 1).Value = Worksheets(1).Range("B" & c).Value
    Worksheets(2).Cells(1, c).Value = Worksheets(1).Range("A" & c).Value
    Worksheets(2).Cells(c, c).Value = Worksheets(1).Range("C" & c).Value & "/" & Worksheets(1).Range("D" & c).Value
    c = c + 1
Loop

End Sub

With the following data:

DateTime	Machine	Employee	Status
16-05-2014 15:47    Machine1 Peter     Not started
16-05-2014 15:47    Machine1 Mike      Not started
16-05-2014 15:47    Machine1 Peter     Started
16-05-2014 15:47    Machine4 John      Started
16-05-2014 15:47    Machine5 John      Unknown

I get:

	16/05/2014 15:47	16/05/2014 15:47	16/05/2014 15:47	16/05/2014 15:47	16/05/2014 15:47
Machine1	Peter/Not started				
Machine1		        Mike /Not started			
Machine1			                         Peter/Started		
Machine4				                                         John /Started	
Machine5					                                                         John /Unknown

@Skip: Yes, i want to add also the times. Skip, it is my fault, i couldnt explain it clearly, sorry for that...please dont be frustrated, because you know that i love you so much. You are one of the best here...

@Andrzejek: You will get the star, thanks for your support.

It would be great if i can group also the information, based on machine or something.

 

[PHV]

It would be great if i can group also the information, based on machine
Like this ?

Sub Macro1()
Dim c As Long, r As Long
c = 2
r = 1
Do While Worksheets(1).Range("A" & c).Value <> ""
    If Worksheets(2).Cells(r, 1).Value <> Worksheets(1).Range("B" & c).Value Then
        r = r + 1
        Worksheets(2).Cells(r, 1).Value = Worksheets(1).Range("B" & c).Value
    End If
    Worksheets(2).Cells(1, c).Value = Worksheets(1).Range("A" & c).Value
    Worksheets(2).Cells(r, c).Value = Worksheets(1).Range("C" & c).Value & "/" & Worksheets(1).Range("D" & c).Value
    c = c + 1
Loop
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bilberry]

Indeed! Thanks!

 

[SkipVought]

How abut this...
[pre]
.
5/16/2014 15:47 5/16/2014 15:47 5/16/2014 15:47
Machine1 Peter/Not started Mike/Not started Peter/Started
Machine4 John/Started
Machine5 John/Unknown
[/pre]

Using Named Ranges

Sub MAIN()
    Dim r As Range, sThisMach As String, sPrevMach As String, lRow As Long, iCol As Integer
    
    lRow = 1
    For Each r In [Machine]
        sThisMach = r.Value
        If sPrevMach <> sThisMach Then
            iCol = 1
            lRow = lRow + 1
            Sheets("REPORT").Cells(lRow, iCol).Value = Intersect(r.EntireRow, Range("Machine")).Value
        End If
        With Sheets("REPORT")
            iCol = iCol + 1
            .Cells(1, iCol).Value = Intersect(r.EntireRow, Range("DateTime")).Value
            .Cells(lRow, iCol).Value = _
                Intersect(r.EntireRow, Range("Employee")).Value & "/" & Intersect(r.EntireRow, Range("Status")).Value
        End With
        
        sPrevMach = sThisMach
    Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Oops! Watching Judge Judy, I just realized my code is incorrect. Please stand by.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mscallisto]

So Skip, when you retire are we going to lose you to Judge Judy?

Sam

 

[SkipVought]

I already retired on May 5.

Gonna share TT with JJ. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mscallisto]

Sounds like a plan...enjoy retirement

 

[SkipVought]

this might do it...

Sub MAIN()
    Dim r As Range, sThisMach As String, sPrevMach As String, lRow As Long, iCol As Integer
    Dim dDatTim As Date
    
    lRow = 1
    For Each r In [Machine]
        sThisMach = r.Value
        If sPrevMach <> sThisMach Then
            iCol = 1
            lRow = lRow + 1
            Sheets("REPORT").Cells(lRow, iCol).Value = Intersect(r.EntireRow, Range("Machine")).Value
        End If
        With Sheets("REPORT")
            iCol = iCol + 1
            
            dDatTim = Intersect(r.EntireRow, Range("DateTime")).Value
            
            Do While dDatTim <> Sheets("REPORT").Cells(1, iCol).Value
                If Sheets("REPORT").Cells(1, iCol).Value = "" Then Exit Do
                iCol = iCol + 1
            Loop
            
            .Cells(1, iCol).Value = Intersect(r.EntireRow, Range("DateTime")).Value
            .Cells(lRow, iCol).Value = _
                Intersect(r.EntireRow, Range("Employee")).Value & "/" & Intersect(r.EntireRow, Range("Status")).Value
        End With
        
        sPrevMach = sThisMach
    Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]