+ Reply to Thread
Results 1 to 15 of 15

Need an easy way to seggregate this file accordingly asap

  1. #1
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    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

  2. #2
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly asap

    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....

  3. #3
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

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

  4. #4
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly asap

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

  5. #5
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

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

  6. #6
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

    So is there anyone who can help me?

  7. #7
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly asap

    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....

  8. #8
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

    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.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly 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
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

    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

  11. #11
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly asap

    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
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

    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

  13. #13
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Need an easy way to seggregate this file accordingly asap

    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!

  14. #14
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: Need an easy way to seggregate this file accordingly asap

    Please check this, I hope this works

  15. #15
    Forum Contributor
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Need an easy way to seggregate this file accordingly asap

    Quote Originally Posted by shyamhappy View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need an easy way to present this in a good manner asap
    By chandannasta in forum Excel General
    Replies: 4
    Last Post: 08-02-2013, 02:09 PM
  2. Easy Question - Macro to add csv file below existing data
    By foxy_420 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2012, 01:52 PM
  3. Excel File Convert or Change To Easy Application
    By st3vi3 in forum Excel General
    Replies: 3
    Last Post: 06-26-2012, 05:30 AM
  4. ASAP Utilities - inserting file path
    By vine_figtree in forum Excel General
    Replies: 3
    Last Post: 03-31-2007, 01:28 AM
  5. new user with easy question? not easy for me
    By speakeztruth in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-03-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1