Need help transposing groups of data within a column that have spaces between them

[shukero]

Hi all,

I have a VERY LARGE dataset in excel (about 2000 cells long) where I need to transpose some data from a column to a individual cells in the same row.

Backstory: I have extracted user data from a 3rd party tool which shows the user in one column, and then the "groups" they belong to in the next column. (See screenshot #1 below) Due to the fact that the number of groups an individual user belongs to may be completely different from one to the next, and all users / groups are separated via a space makes it hard for me to think of a way to "transpose" the user groups.

I'm trying to get these user groups within the same row as the username but in separate cells to the right of the username, and preferably in alphabetical order if possible. (See screenshot #2)

Is there a simple way to do this?

Thanks,
Mike

 

[awoohaw]

What version of excel do you have? And can you post a mini sample of the worksheet(s) using the xl2bb add in? (or aleast a table version, we can't do much with images.)

 

[shukero]

Hi Awoohaw,

I'm using the latest version of excel within the "office365" package. Unfortunately I can't install the plugin as my excel is on a work laptop which doesn't allow additional plugins or programs to be installed on it.

 

[Fluff]

I'm using the latest version of excel within the "office365"

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[shukero]

Thank you for the Tip @Fluff - I have made the updates, and I appreciate the help!

 

[awoohaw]

Hi Awoohaw,

I'm using the latest version of excel within the "office365" package. Unfortunately I can't install the plugin as my excel is on a work laptop which doesn't allow additional plugins or programs to be installed on it.

can you copy and paste the worksheet as values in a table then?

 

[shukero]

Sure; here you go:

The way it is:

Users:	User groups
user #1	Group #1
	Group #5
	Group #2
	Group #10
	Group #6
user #2	Group #3
	Group #1
	Group #2
user #3	Group #5
	Group #11
	Group #7
	Group#4
user #4	Group #2

The way I want it:

Users:	User groups
user #1	Group #1	Group #2	Group #5	Group #6	Group #10
user #2	Group #1	Group #2	Group #3
user #3	Group #4	Group #5	Group #7	Group #11
user #4	Group #2

Just as an FYI - it looks like the table "minimizes" the spaces between the datasets.