Sort Users with their application from a chart

**point0two** · 10-19-2011, 10:18 AM

Hello,

I am new to programming on Excel and was wondering, if someone could provide me some instruction and advice.

The issue is this, I have a chart with a list of users on the left side and on the right-hand side I have cells that are filled with an 'O' ,corresponding to the application they have on their computer. If you are a little confused here is a rough sketch :

--------------------------------------------------------------------------------------------------------------------------
[Cell A1] Users: -|- [Cell B1] MS Paint -|- [Cell C1] MS Excel -|- [Cell D1] Chrome -|-
--------------------------------------------------------------------------------------------------------------------------
[Cell A2] Bob -|- [Cell B2] O -|- [Cell C2] <BLANK> -|- [Cell D2] O -|-
--------------------------------------------------------------------------------------------------------------------------
[Cell A3] Billy -|- [Cell B3] <BLANK>-|- [Cell C3] O -|- [Cell D3] O -|-
---------------------------------------------------------------------------------------------------------------------------
[Cell A4] Bernard -|- [Cell B4] O -|- [Cell C4]<BLANK>-|- [Cell D4]<BLANK>-|-
---------------------------------------------------------------------------------------------------------------------------

So lets say I wanted to see all the applications Bob has, I run the script/formula and it generates a nice window or a new sheet, that shows me his name and the apps he has.

I have a huge list of users and a very large library of apps. It would make life easier, if I could check who has what very easily.

If you can help me, it will be very greatly appreciated! If you can't, then pointing in me in the right direction, either by suggesting a resource where I can learn to-do something like this would be very much appreciated.

I have tried searching online, but I am still confused as to what I should be looking for.

Thanks!!

**Andy Pope** · 10-19-2011, 10:41 AM

you can use pivot tables.

First sheet is based on you current data layout. You can filter Users if required.

Second sheet is a better data layout

**point0two** · 10-20-2011, 08:44 AM

Hi Andy,

Wow I didn't know I could do that, thanks!

Just out of curiosity, is there still a way to Query a search using some sort of formula? The query should return the persons name and all data associated with it. For example with Bob, his desk location, software applications, system build etc. Some of the data would be in the form of a chart, other just a dumb of information.

Thanks again for your help!

point0two

**Andy Pope** · 10-20-2011, 09:10 AM

You might be able to use Autofilters
or with formula you could try INDEX, MATCH, VLOOKUP. It's hard to say which ones exactly without knowing what the expected result is and how you intend to use it.

And of course you can create charts based on the pivot table.