CODEWITHSUNDEEP
excelvba
Batman
Batman

Reputation: 6373

Best way to filter both Vertically and Horizontally?

I have a large data set which contains users and the groups they belong too (they can be in many groups). I currently have 265 users and 94 groups. I'd like to find the best way to display this information. Right now I have a very large spreedsheet that shows me this information, but I can only filter the groups (column) to find out which users are in that group. Ideally, It would be great to also be able to filter the users to find all the groups on user belongs too.

Here's a demo of what my sheet looks like: enter image description here

Transposing won't work with 265 users and I'd rather not have two spreadsheets with the same information. Any ideas? Thanks.

Upvotes: 2

Views: 41355

Answers (1)

Brian Camire
Brian Camire

Reputation: 4835

Store your data in one table with two columns, "User Name" and "Group Name", and one record for each group that each user belongs to. For example, the first few rows might be:

User Name      Group Name
User 1         Group 1
User 1         Group 5
User 2         Group 5
User 2         Group 6

You can filter this table on "User Name" to see what groups a specific user belongs to, or on "Group Name" to see what users belong to a specific group.

If you want to see your table in a "matrix" form, create a pivot table based on the table.

As a one-time exercise to convert your data from its current format to the new format, you might:

  1. Create a new worksheet.
  2. In the new worksheet, enter the following column headings in the first row of columns A through F, respectively: Cell Number, Row Number, Column Number, Cell Value, User Name, and Group Name.
  3. In the new worksheet, enter the following formulas: =ROW()-1 in cell A2, =INT((A2-1)/94)+1 in cell B2, =MOD(A2-1,94)+1 in cell C2, =INDEX('Your Existing Data'!$E$2:$CT$266,B2,C2) in cell D2, =INDEX('Your Existing Data'!$A$2:$A$266,B2,1) in cell E2, and =INDEX('Your Existing Data'!$E$1:$CT$1,1,C2) in cell F2. This assumes that your existing data is in a worksheet named "Your Existing Data" and laid out as shown in the image referenced in your comment.
  4. Copy the formulas down to row 24911 (265 * 94 + 1).
  5. Copy the formulas and paste over them with their values.
  6. Use a filter to delete the rows that don't have an X in the Cell Value column.
  7. Delete columns A through D.

If you want, you can add also formulas to the new worksheet to look up the first name, last name, and e-mail from your existing data based on the full name.

Upvotes: 4

Related Questions