Need an easy way to seggregate this file accordingly asap

Here are my questions?

1. I want to know an easy way of how many permissions does each Name does not have?

2. An easy way for how many user names do have the same kind of permissions?

3. An easy way of segregating such type of list which could save ample of time in my office work.

One more thing, i am not so much expert in excel just learnt only a few things that's essential to do my daily routine office work only as I get 100s of file like this on weekly basis. So would just like to know an easy way or formulas of doing this in excel only. One more thing in my office every one works on Office 2003 only. This is my file download Link http://www.uploadmb.com/dw.php?id=1375027929

Could you post the file using the forum facility? (Go Advanced and attach)

This sounds very achievable though, pivot tables are probably the way to go....

After i add the files it asks me please drag and drop the existing attachment which i cant do. I dont know y

There should be an option to use the Basic Uploader, this will let you browse to the file and attach - does that help?

Your file of 1.75 Mb exceeds the forum limit of 1000 kb this is the message through basic uploader

So is there anyone who can help me?

I can't download from that external site as i'd braeching our security policy in the office and its tough without a sample...

If the file size is too large then could you possibly do an extract? Just some clean data in the structure you have it?

Usually with excel once the principal is proven on a small subset of data it is very easy to scale up....

JungleJme I have deleted 60% of it and just shown 40% in the attachment which is now attached. So let me know the replies to all my questions in my 1st post as asked on the top plz asap.

Or if any other person who can please let me know asap.

Sorry, i am out of time! But i have had a go:

I can't quite remember 2003 but go to Name Manager and have a look at the formula i have used.

This creates a range called "Table". The range is dynamic so you can paste new data over the source data in sheet and the range will automatically expand to cover the new data.

Then two pivot tables summarise the data in sheet 2. They answer your questions about many users have each permission, and how many permissions each user has.

When the data changes you will need to "Refresh All" to update your pivots.

I don't quite know what you mean by segregate. But if you just want to view or copy out a particular user then apply a filter to row 1 of your source data sheet and you can then filter on certain values. Google how to do this if you are unsure.

If you mean seperate the data into different workbooks, sheets or lists then you will need a VBA macro to loop through and do this and i'm afraid i do not have time to write and test that at the moment. Again, lots of people have done this before though so if you google "Loop through Data and Create New Worksheets Excel VBA" then something should come up!

Good luck

JUngle Me thanks for all your help but I dont know how to make 2 pivot tables at 1 sheet. Never ever knew in my life such thing can also happen. So I will try to learn this soon. Now my basic concern is this

I have seggregated the data as per your pivot table sent and here it is what I want exactly as per all user ids and permissions only in Pivot Table. I dont need names. When you first check it in pivot after entering user ids & permissions this is what you get

UserID Total
NBUC310 27
NBUC312 31
NFLU001 19
NFLU002 20
NFLU004 19
NFlu005 22
NFLU006 20
NFLU009 20
NFLU010 20
nflu012 20
NFLU020 19
NFLU021 19
NFLU023 19
NFLU024 19
NFLU025 22
NFLU026 19
NFLU032 20
nflu034 20
NFLU035 20
NFLU037 21
NFLU038 20
NFLU220 19
NFLU221 19
nflu222 19
nflu223 19
NFLU225 22
NFLU226 20
nflu227 19
NFLU228 19
NFLU230 20
NFLU232 20
NFLU234 20
NFLU235 30
NFLU236 20
NFLU237 22
NFLU238 22
NFLU239 23
nflu242 20
nflu320 19
NFLU321 19
NFLU322 20
NFLU323 20
NFLU324 22
nflu325 20
nflu326 19
nflu330 17
NFLU340 22
NFLU341 19
NFLU342 19
NFLU343 19
NFLU344 20
NFLU345 19
NFLU348 22
NFLU349 21
NFLU360 22
NFLU361 19
NFLU362 19
NFLU364 19
NFLU380 20
NFLU381 20
NFLU386 20
NFLU420 20
NFLU421 19
NFLU422 19
NFLU423 20
NFLU425 19
NFLU426 20
NFLU427 22
NFLU429 19
NFLU430 20
NFLU434 20
nflu472 20
NFLU520 22
NFLU521 20
NFLU522 20
NFLU523 19
NFLU524 19
NFLU526 20
NFLU527 19
NFLU528 20
NFLU531 23
NFLU535 21
NFLU536 21
NFLU539 19
NFLU600 22
NFLU621 19
NFLU622 19
NFLU623 19
NFLU626 20
NFLU628 20
NFLU629 19
NFLU631 20
NFLU632 20
NFLU635 19
NFLU637 19
NFLU706 20
Nflu71 20
NFLU720 22
NFLU722 20
nflu723 17
NFLU724 19
Nflu727 20
NFLU730 19
NFLU731 20
nflu732 17
NFLU750 20
NFLU751 19
NFLU760 22
NFLU760CV 23
NFLU761 14

Then you need to arrange them in asceneding order for permissions accordingly. So the data would look like this

UserID Permission
NFLU761 14
nflu330 17
nflu723 17
nflu732 17
NFLU001 19
NFLU004 19
NFLU020 19
NFLU021 19
NFLU023 19
NFLU024 19
NFLU026 19
NFLU220 19
NFLU221 19
nflu222 19
nflu223 19
nflu227 19
NFLU228 19
nflu320 19
NFLU321 19
nflu326 19
NFLU341 19
NFLU342 19
NFLU343 19
NFLU345 19
NFLU361 19
NFLU362 19
NFLU364 19
NFLU421 19
NFLU422 19
NFLU425 19
NFLU429 19
NFLU523 19
NFLU524 19
NFLU527 19
NFLU539 19
NFLU621 19
NFLU622 19
NFLU623 19
NFLU629 19
NFLU635 19
NFLU637 19
NFLU724 19
NFLU730 19
NFLU751 19
NFLU002 20
NFLU006 20
NFLU009 20
NFLU010 20
nflu012 20
NFLU032 20
nflu034 20
NFLU035 20
NFLU038 20
NFLU226 20
NFLU230 20
NFLU232 20
NFLU234 20
NFLU236 20
nflu242 20
NFLU322 20
NFLU323 20
nflu325 20
NFLU344 20
NFLU380 20
NFLU381 20
NFLU386 20
NFLU420 20
NFLU423 20
NFLU426 20
NFLU430 20
NFLU434 20
nflu472 20
NFLU521 20
NFLU522 20
NFLU526 20
NFLU528 20
NFLU626 20
NFLU628 20
NFLU631 20
NFLU632 20
NFLU706 20
Nflu71 20
NFLU722 20
Nflu727 20
NFLU731 20
NFLU750 20
NFLU037 21
NFLU349 21
NFLU535 21
NFLU536 21
NFlu005 22
NFLU025 22
NFLU225 22
NFLU237 22
NFLU238 22
NFLU324 22
NFLU340 22
NFLU348 22
NFLU360 22
NFLU427 22
NFLU520 22
NFLU600 22
NFLU720 22
NFLU760 22
NFLU239 23
NFLU531 23
NFLU760CV 23
NBUC310 27
NFLU235 30
NBUC312 31


Now what I want exactly is I wanted the all the user ids with 17 permissions all on 1 page. Then all user ids with 19 permissions on 1 page. Then all users with 20 permissions on 1 page.

The permissions for each user varies say for e.g this user id NFLU004 has

Add Order
Create ARRESTDIRECT
Create Credit Request
Create Criminal Request
Create D M V Request
Create Sexual Offender Database Search
Create Socsec Trace Request
Enable E D E
Hide Ssn
Hide Ssn For Web Results
Show Standard Order
Use Sterling Direct
View Adverse Action Letters
View ARRESTDIRECT
View Credit Request
View Criminal Request
View D M V Request
View Sexual Offender Database Search
View Socsec Trace Request

But the other users with 19 permissions may not have such. They may have something different. So when this thing varies in certain user id it should reflect for that particular user individually. So I need an easy way for the same so that it could save some time as I have to do this manually every now and then

OK - have a look at the attached.

You should paste your data into Columns A:C of the "Data" tab.

Then you will need to Refresh All Pivots in your workbook - you can do this on each pivot table by right clicking and click "Update Data" or follow these instructions for how to do them all at once.

The source data now has a column D which is a helper column with a formula in it. If you paste new data into columns A:C you will need to copy this formula to the bottom of the new range. To do this, select Cell D2, when selected a tiny little box appears in the bottom right hand corner of the selected cell, double clicking that box will copy the contents of the cell to the bottom of the adjacent range (saves a lot of time dragging the contents down!)

This formula is used by the "Check Missing Permissions" tab - here you can enter a USER ID at the top and it will show you which permissions the ID does and doesn't have by matching the combination of the USER ID and the permission name to the values in column D of the source data.

Also, i have added sheets for 17, 19 and 20 Permissions. You can right click and copy these sheets to create more, simply change the filter once copied.

Another GREAT tip. If you select any summarised item on a Pivot Table and Double Click it, it will create a new sheet with the constituent records for that item. This may help you quickly work through the User IDs. Give it a try.

Hope all that helps,

J

The problem for me I can reply to you easily but cannot attached the file in the file upload manager because the circle thing keeps on rotating in the attachment sheet before the ?. 2 hours have passed and still its rotating only. So when it would stop then only I can attached the sheet.

I really liked the formula thing and it was too good but I will explain you sir what I have to do exactly. I get atleast 50000 user records in a day and have to complete them and since I am slow it takes a week for me to complete which my seniors know it. So at last this is the way i have to present my sheet at the end which i have attached below. In that you would see what each user is missing is mentioned clearly which is my daily work. So have a look at it and if you could suggest an easy an simple way for the same so that it could save ample of time. For 100 users it takes for me 10 hrs to do the job completely. So i need an easy way to do the task and save the time for other work.

This is the way I have to submit my work in the end.

Names/Userids of users in 1 colomun. 2nd coloumn Names of permissions for each of them

So like as you could see in my previous sheet some users have 19 permissions, some have 20 some have even 2 or 3 permissions but which ever are the same becomes 1 policy. So every user who has something different or an extra permission becomes a new policy for that specific user id/ids.

So in this I wanna learn how to save my time. I am really thankful to you for helping me so much out. Also i want your help for this along with my other thread also http://www.excelforum.com/excel-gene...nner-asap.html

Sorry, you'll need to write yourself a macro in VBA to do that and my VBA isn't really up to the task i'm afraid.

Sorry!

Please check this, I hope this works

Originally Posted by shyamhappy

Please check this, I hope this works

I know this method already but is there a way macro can be made through which the work can be done automatically if such type of large bulk lists are there