Can I run a Query or Pivot Table against this dataset?

[circuit_breaker]

Hi,
I have an Excel sheet like this:

User Machine Machine Machine Machine

User1 PC1 PC2 PC3
User2 PC5 PC1
User3 PC2 PC3
User4 PC1 PC4 PC5 PC2
User5 PC3 PC4 PC5

I'd like to know for every single machine, who are the user(s) using
it.
For example, PC1 is used by User1, User2 and User4

Any help is welcome. I was thinking of copying the above into a table
and use SQL.
Thanks.

 

[Hans Terkelsen]

Hi,
I have an Excel sheet like this:

User Machine Machine Machine Machine

User1 PC1 PC2 PC3
User2 PC5 PC1
User3 PC2 PC3
User4 PC1 PC4 PC5 PC2
User5 PC3 PC4 PC5

I'd like to know for every single machine, who are the user(s) using
it.
For example, PC1 is used by User1, User2 and User4

Any help is welcome. I was thinking of copying the above into a table
and use SQL.
Thanks.

Perhaps you could use a table with 0 and 1 like this:

[A9] PC1 PC2 PC3 PC4 PC5
User1 x 1 1 0 0
User2 1 0 0 0 1
User3 0 1 1 0 0
User4 1 1 0 1 1
User5 0 0 1 1 1

The formula x is =COUNTIF($B3:$E3,B$9), just notice the $
That is, if your first table has User 1 in A3

Regards Hans T.

 

[Herbert Seidenberg]

Excel 2007
Three more ways:
http://www.mediafire.com/file/nyngdoxz3ml/08_24_09.xlsx