Text Data Challenge - Looking for Options

[stringrazor]

I am using a WSH script to extract group membership information from Active Directory to a CSV file which I import into Excel (MSO Pro Plus 2010 32-bit) in the following format:

userName,accountName,groupName

for example


_____A_____B________ C
1 | joe | joe1234 | groupB
2 | joe | joe1234 | groupC
3 | leo | leo1234 | groupA
4 | leo | leo1234 | groupC
5 | joe | joe1234 | groupA


etc.

Column values are duplicated. If a user is in 3 groups, they show up in 3 rows. The goal is to quickly determine if a user is NOT in a specific group without having to do it by eye. I've found a number of ways to get close to that goal but all involve a lot of manipulation. Since the data is static once extracted, I'd like to find a process that's fairly quick so I can work with newly extract data each time I need to check and not have to go through a long string of complex data massaging steps each time.

Suggestions?

[Sam Capricci]

There are probably a lot of options. Based on what you pasted, what constitutes a duplicate? Joe1234 is in B three times but attributed to three different groups. If he is supposed to be in once then sort column B ascending, then put =IF(B1=B2,"",B2) and copy down and anywhere there is a value in that row is a new person. For more than that you might need to post a sample spreadsheet and list what restrictions you have.

[TMS]

Add a header row; say, name, ID and group. Then insert a Pivot Table with name in the Row Labels and group as the Column Labels with a Count of the IDs.


Regards, TMS

	A	B	C	D	E	F	G	H	I	J
1	name	ID	group
2	joe	joe1234	groupB
3	joe	joe1234	groupC
4	leo	leo1234	groupA
5	leo	leo1234	groupC
6	joe	joe1234	groupA
7
8					Count of ID	Column Labels
9					Row Labels	groupA	groupB	groupC	Grand Total
10					joe	1	1	1	3
11					leo	1		1	2
12					Grand Total	2	1	2	5
13

[stringrazor]

Add a header row; say, name, ID and group. Then insert a Pivot Table with name in the Row Labels and group as the Column Labels with a Count of the IDs.


Regards, TMS

	A	B	C	D	E	F	G	H	I	J
1	name	ID	group
2	joe	joe1234	groupB
3	joe	joe1234	groupC
4	leo	leo1234	groupA
5	leo	leo1234	groupC
6	joe	joe1234	groupA
7
8					Count of ID	Column Labels
9					Row Labels	groupA	groupB	groupC	Grand Total
10					joe	1	1	1	3
11					leo	1		1	2
12					Grand Total	2	1	2	5
13

I will need check that certain people are all members of certain groups, eg "make sure these 10 people are all members of these 3 groups...". I know I can use the list I'll get, likely by email, for an advanced filter on the pivot table source data, regenerate the pivot table, and then manually choose the groups to show from column label filter but is there a quicker way?

[stringrazor]

That Pivot Table works nicely, thank you!

---- Gary ----

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[newdoverman]

Here is one way of doing this. As names are added to Sheet1, if the name is unique, it is added to the list of names on the Summary worksheet. The Summary worksheet also identifies the groups from column C that the name belongs to.

[stringrazor]

New groups could also be added at any time.

[newdoverman]

With the solution that I gave you, just enter the group name in the next column and copy the formula from the previous column to the right as far as necessary and down as far as you want. The new group will be counted for all names new and old.

If you want to add the capability in ahead of time so that all you have to do is to add the group name in Row 1 use this formula in B2 and copy across as far as you want and copy down. If the number of rows in the original sheet exceeds 200, increase 200 to whatever you need.

=IF(OR($A2="",B$1=""),"",COUNTIFS(Sheet1!$A$2:$A$200,$A2,Sheet1!$C$2:$C$200,B$1))

[TMS]

I have no idea what your process is, or what checklists exist for you to use.

"make sure these 10 people are all members of these 3 groups...".

doesn't really give much of a clue as to how you would do that.

If you know that "these 10 people all need to be members of these 3 groups...", surely it would be better to ensure your processes take positive action to ensure that is the case.

[stringrazor]

I'm not explaining correctly. Let me restate.

The pivot table lets me see group membership fairly quickly based on the source data area. What I want to do is filter the data that is available to the pivot table using a list.

Lets say the full source data area contains hundreds of rows made up of perhaps 100 unique users belonging to up to 10 unique groups. That would be 1000 rows if all users belonged to all groups (but they don't). Suppose I'm only interested in the group membership of 10 specific users.

I know I can manually filter at the pivot table both the users (rows) and groups (columns) shown but I would like to use a list as in advanced filtering.

All of this is reference only. No data will be changed. If I determine group membership isn't as needed, I have to submit a request to add/remove users in AD. I have read-only access to Active Directory.

[newdoverman]

This version of the file that I uploaded is edited to allow 2000 rows of data with Data Validation to prevent duplicate entries of the name and group combination.

On the Summary worksheet, the Filter has been activated. This allows you to filter by group or groups....select 1 if you want that group included. In the name column, select the names that you are interested in. You can combine names and groups to form a filter of the records.