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

C

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

Hans Terkelsen

circuit_breaker said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top