• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table (possibly) help?

C

Confused Excel

New Member
Using an example table but the one im looking at is much larger so need to be able to do this with some formula or pivot to save time. I have got a bunch of data from CSV which shows me a user and the AD groups they are apart of. The ask is that I have the groups and underneath in a dropdown, the users in that group (so for example 'Schema Admins' dropdown would show user 1, 2 and 11). Now for this size I could do it manually but I may have to do this for hundreds of lines of data so need something better. I have tried 'text to columns' using Comma as a delimiter to separate out the groups into other columns but that doesnt help my pivot table situation as I need to sort the groups into one column with the user corresponding to that.

Any ideas or questions if the above wasnt clear
 

Attachments

  • Excel 3.xlsx
    9 KB · Views: 1
Is the attached a possible way forward?
 

Attachments

  • Chandoo47298Excel 3.xlsx
    21.4 KB · Views: 4
Hi,

Yes that is a solution thank you, how did you go about doing it? i.e seperating all the groups and linking it to the users and putting it in that format?
 
Power Query:
77277

takes you to:
77278
where you can see the 2 steps to rearrange the data (click on them to see them in action).
Then I Close & Loaded to a Pivot table:
77279

To use it on your data, paste the data over the top of the existing data at cell A1 and ensure the extents of the table fully cover your data (use the grab-handle bottom right):
77280
 
Hi,

Thank you for the above

If I want to do this on other data, so on step 3, I dont have a query available to select, I assume I need to create a table of the data below to use, how do I do that? Once that is done I guess I can just take your formulas for the applied steps?

(Also why is 'Close and Load to' greyed out?

77281
 
Last edited:
Wait think I figured out how to create the data by doing get data from table. I then copied your delimited formula and it seems to be work.

I will test with my data now and feedback

Thank you again for your help
 
To make your own query, select a cell or cells in the source table of data, then, in your screen shot you see From Table/Range? Click that. Allow it to create a table (it does have headers) and you'll be taken to Power Query.
Select the Groups column, click on the Transform tab at the top, choose Split Column, then by Delimiter, select a comma as delimiter, choose Advanced options, split into Rows, click OK. Then in the Home tab, choose Close & Load to (it should not be greyed out the first time), choose Pivot report and where it will be. Click OK, design your pivot table. QED.

There's a tutorial at this forum:
chandoo.org

Power Query Tutorial - What is it, How to use, Full examples, Tips & Tricks

Power Query (Get & Transform data in Excel) is a true game changer ?. It can simplify and automate various data activities. In this in-depth tutorial, learn all about it.
chandoo.org chandoo.org
and plenty of other places, eg.: https://www.wallstreetmojo.com/power-query-tutorial/
 
Yes this works, unfortunately I guess the only thing I cant take from this is how you built that power query for the delimiter but thats a bit of coding above my level

Thanks again for the help
 
Confused Excel said:
the only thing I cant take from this is how you built that power query for the delimiter but thats a bit of coding above my level
Click to expand...
I didn't do any coding, the GUI did it for me, as it will have done for you if you follow instructions in msg#7.
 
You must log in or register to reply here.
Back
Top