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..

 

[SkipVought]

Hi,

Perhaps you should also display the results you expect, given the table you posted. The FIRST issue I see is that your example has no table headings!

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

 

[Andrzejek]

With the example given (data starts in Column 2 and there are no empty rows), try:

Option Explicit

Sub Macro1()
Dim c As Integer

c = 2
Do While Sheet1.Range("A" & c).Value <> ""
    Sheet2.Cells(1, c - 1).Value = Sheet1.Range("A" & c).Value
    Sheet2.Cells(2, c - 1).Value = Sheet1.Range("C" & c).Value & "/" & Sheet1.Range("D" & c).Value
    c = c + 1
Loop
Sheet2.EntireColumn.AutoFit

End Sub

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]

Hi Skip,

Its a bit difficult to draw the results here in this post. I hope that i can describe that:

The headers are:
Column A: DateTime
Column B: Machine
Column C: Employee
Column D: Status

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). So in our example the dates should be as columns, like:

16/5 | 17/5 | 19/5 (based on the values within the table)

We need to put the machines as rows, the end result should be:

16/5 | 17/5 | 19/5
machine 1 Peter / Not started
machine 1 Mike / Not Started

and so on....

@Andrzejek: I hav etried your solution but i get variables which are not declared (like Sheet1 and Sheet2), and also after fixing that part, a subsription out of range error...Any idea?

 

[SkipVought]

Sheet1 & Sheet2 are not variables, but they are the sheet CodeNames. Look it up in VBA Help.

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

 

[PHV]

variables which are not declared (like Sheet1 and Sheet2
Seems like you're not an US or UK guy.
Use the locale names instead.

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

 

[SkipVought]

You have DATE & TIME for each row of data. Will you have multiple DateTime for any Date?

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

 

[Bilberry]

Hi Skip,
yes you can add multiple date times

 

[SkipVought]

Then this will not work with multiple Times per Date!!!
[pre]
.
16/5 17/5 19/5
Machine Employee Status. Employee Status Employee Status
machine 1 Peter Not started
[/pre]

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

 

[Bilberry]

Ok Skip, can we try without the time part? So only the dates...

 

[SkipVought]

We'll tell me how this is supposed to be reported: same machine, same date???

[pre]
16-05-2014 15:47 Machine1 [highlight #FCE94F]Peter Not started[/highlight]
16-05-2014 15:47 Machine1 Mike Not started
16-05-2014 15:47 Machine1 [highlight #FCE94F]Peter Started[/highlight]
[/pre]

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

 

[Bilberry]

Hi Skip,
Thats demo data, in practice there will only be distinct dates in there

 

[Andrzejek]

My code was set up with the assumptions that:

This is your sample data on Sheet1:
[pre][blue]
A B C D[/blue]
Column A Column B Column C Column D
[blue]1[/blue] 16-05-2014 15:47 Machine1 Peter Not started
[blue]2[/blue] 16-05-2014 15:47 Machine1 Mike Not started
[blue]3[/blue] 16-05-2014 15:47 Machine1 Peter Started
[blue]4[/blue] 16-05-2014 15:47 Machine1 John Started
[blue]5[/blue] 16-05-2014 15:47 Machine1 John Unknown
[/pre]
You also have Sheet2 available in your workbook. Default is to have 3 Sheets in a workbook.

You may try changing Sheet1. to Sheets(1). and Sheet2. to Sheets(2).

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]

Well why would you post a discrepant example?

Why should I believe that your example is at all coherent?

This is not the first problem with your requirements.

What do you really have here? It appears to be a schedule for assigning some job (yet unidentified) to a machine resource and an employee resource.

If it were me, I'd have a ScheduleStartDate and an ActualStartDate (and end dates). But then I've only worked MRPII for some 20 years, the last 10 in a machine shop.

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

 

[Bilberry]

Hi,
It is indeed to schedule for assigning some job to a machine resource, all the other activities and enddates are done with another tool. This is only to show the data in Excel

@Andrzejek, Ok tx, i have tried to fix the errors. The date time (column A and B) is only one column, so Only column A contains the date and time field together

 

[Bilberry]

Skip sorry for the inconvenience.

Did you have a look how to handle this?

 

[Andrzejek]

With your data like this:
[pre][blue]
A B C D[/blue]
Column A Column B Column C Column D
[blue]1[/blue] 16-05-2014 15:47 Machine1 Peter Not started
[blue]2[/blue] 16-05-2014 15:47 Machine1 Mike Not started
[blue]3[/blue] 16-05-2014 15:47 Machine1 Peter Started
[blue]4[/blue] 16-05-2014 15:47 Machine1 John Started
[blue]5[/blue] 16-05-2014 15:47 Machine1 John Unknown
[/pre]

The code I gave you still produces this on Sheet2:

[blue]
       A                  B                C                 D                  E[/BLUE]
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
Peter/Not started  Mike/Not started  Peter/Started     John/Started      John/Unknown

With this line commented out:[tt][green]
'Sheet2.EntireColumn.AutoFit[/green][/tt]

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]

Please tell us what it is that you need to accomplish with this tool, and please do not regurgitate what you have already stated.

I'm looking for the purpose.

What you have already stated is clouded by your perception of HOW you think the purpose ought to be accomplished. I'm interested in WHAT it is that needs to be accomplished.

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

 

[Bilberry]

@Andrzejek: Thanks for that. I could not see the machines (column B) on the first row. It should look like:

   A            B                  C                D                 E                  F
         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  Mike/Not started  Peter/Started     John/Started      John/Unknown

Is that possible?

@Skip: We get a CSV from a system. We want to show this information in another way to show the planning of some activities. I want to use this only for the view/user interface (it is only read only). The content above, is only an example. The production data will be the same, only with different content.

 

[SkipVought]

So you DO want Time to be included in the Across

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