Turning Data into a Table

[ciaran.hudson]

Hi,

I want to create a table.
It will look like a pivot table.
But it will not do any calcultaion.
What would normally be 'Values' in a pivot table simply needs to return content - more like a lookup.

Each record in my data set is unique and I am not trying to do calculation.
I am just trying to represent the same data differently.

Here is an example of the data.
Data File
User Computer Status
Paul PC1 Active
Paul PC2 Inactive
Peter PC1 Locked
Peter PC2 Active

Note, each record when all 3 fields combined is unique.

And here is the desired table
Desired Table
User PC1 PC2
Paul Active Inactive
Peter Locked Active

In the desired table.
Each User will appear only once as a row
Each Computer will only appear once as a column.
And the status will appear where the User and Computer meet.

If it cannot be done in Excel, I am open to Access solutions.

Regards,
Ciarán

 

[Claus Busch]

Hi,

Am Thu, 4 Dec 2014 02:33:20 -0800 (PST) schrieb (e-mail address removed):

User Computer Status
Paul PC1 Active
Paul PC2 Inactive
Peter PC1 Locked
Peter PC2 Active

Note, each record when all 3 fields combined is unique.

And here is the desired table
Desired Table
User PC1 PC2
Paul Active Inactive
Peter Locked Active

your data in Sheet1 starting in A1
Write the headers in Sheet2. Then in A2:
=Sheet1!A2
In A3:
=IFERROR(INDEX(Sheet1!$A$2:$A$100,MATCH(1,(COUNTIF(A$2:A2,Sheet1!A$2:A$100)=0)*(Sheet1!A$2:A$100<>""),0)),"")
Insert the array formula with CTRL+Shift+Enter and copy down till cells
remain empty
In B2:
=INDEX(Sheet1!$C:$C,MATCH($A2&B$1,Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100,0))
and also insert with CTRL+Shift+Enter then copy to the right and down.


Regards
Claus B.