+ Reply to Thread
Results 1 to 23 of 23

Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Question Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Dear All,

    Firstly, I must thank Simarui for resolving the original question. http://www.excelforum.com/excel-prog...on-matrix.html

    Unfortunately, the criteria has changed, (doesn't it always?) and I need some more help.

    Attached is a file with 5 tabs
    Tab marked "User to System Matrix" is the end result I want to achieve.

    I need to to identify in the matrix what systems a user has access to (left to right), or what users are listed in a system (top to bottom) I no longer need to concatenate all the systems to the bottom. However, I need to pull in the User names or descriptions from column E in each of the sheets (which can be added to) into Column B. If the 'User name' has already appeared (e.g. User 1) then it only need be entered once. However, on some systems the User name may be in as "User One" as opposed to "User 1" therefore I would like both to appear.

    Finally, I need to pull the entry from Column H in each column into Column C. This should be the same throughout (or blank), however, it would be useful if all entries were also concatenated into this column (so if a user has their parent company entered in one system as Company A and another System as Company B, this would be identified.

    I'm not worried about additional control sheets or columns which may or may not be hidden as long as I can come up with something resembling the "User the System Matrix" Tab at the end.

    I'm fairly comfortable with Macros, formula etc. However, and I appreciate this is a big ask, if you could comment in the macro what bit each does I would be very grateful as it would help me learn.

    My last extract had c 450 unique users from 25 systems, however, this will grow and shrink as the infrastructure develops.

    I've left Simarui's original macro in as it might help to be a jump off point.

    Kindest regards

    Terry
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Try the attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Here's another one to try..

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Jindon, your solution worked great thank you. I cannot thank you, and others like you, enough. You all make me look good

    I may have come across a bit of a foible which might be more to do with excel than coding. If I enter a User Name as something like Oracle Ad (as opposed to Oracle Admin or Oracle Administrator) in one of the later sheets, it doesn't pull it in. I'm guessing this is because "Oracle Ad" already exists as it were. Similarly, if "Oracle Administrator" is in the first sheet, it doesn't pull in "Oracle Admin". I'm guessing this would be for the same reason. Not sure what or if there'd be a work-around.

    Apo, your solution worked fine the first time, however, when I tried to operate it a second time it failed on me :-(

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Yeah,,, I noticed that...
    Try this one
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Apo, your solution worked fine the first time, however, when I tried to operate it a second time it failed on me :-(
    Can you expand on that.. did it give you an error or incorrect results?

    When i run it twice here.. it continues to concatenate the values in column H..

    If that is the issue.. just clear the range (B2:H21) in your sample , at the start of the code..

  7. #7
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Apo,

    let me run it again and I'll see what if I can expand - maybe it was just me

    Jindon,

    Again, spot on, thanks again.

    I've been told I don't need the concatenated bit in the end column (the systems) as it's represented in the matrix. (another criteria change ) which bit of the code can I delete to prevent that?

    Terry

  8. #8
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Cancel that Jindon, cracked it!! :-)

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    I guess you knew how to...
    Easiest way is just change to
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Jindon,

    Having shown my bosses what it can do, they've come up with a series of "but can you make it do .........?" questions and "I like that, but don't think we need that" kind of statements.

    I REALLY appreciate your help and don't want to impose on your better nature, but are you willing to help me further please.

    They're wanting to add things like user defined headers at the end and some vlookups and Conditional formatting.

    Many thanks

    Terry

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    I don't understand what you are asking...

  12. #12
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Hey Jindon,

    Basically, I'm asking if I can come back to you to ask how to implement the changes the 'management' have asked for. I just don't want you to feel that I'm abusing your good will.

    Terry

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    If it is related to your original question, you can ask and I will see if I can help you or not.

  14. #14
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    It is and many thanks. I'll piece it all together and send it in a little while.

  15. #15
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Question Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Hello again,

    Having demonstrated Jindon's fantastic work (which made me look good), a few more questions have been asked.

    Verification Details tab - Can the headers be put in the Row A of the "User to System Matrix" after the last system listing (remember the quantity of systems may vary)

    User's Parent Company - Can some kind of lookup be inserted in the User's Parent Company Column (inserted from the Verification Details tab)so that if the user is already listed in the list in column A of the "Known Users" tab it inserts the value of Column B. If the user DOES NOT exist in column A CF the cell to RED

    Of course, having these two extra tabs (which can be hidden) means they will need to be ignored when the original Macro is run.

    I'm more than happy if this is several macro's that are run one after the other.

    The 'Welcome' tab will be used to give the user instructions on how to operate the workbook.

    Ultimately, I will be adding one final "saveas" macro to save the file (potentially without macros) and start the filename as yyyymmdd - <filename to be determined>. (but I think I should be able to manage that bit!

    I appreciate this is probably a lot of work and cannot express how grateful I am for your help and assistance.

    Kindest Regards,

    Terry
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    I need to see your exact desired result with the clear instructions.

  17. #17
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Hi Jindon,

    I've "mocked up" an end result and included text boxes with 'instructions'. I cannot thank you enough for your time and effort. If there was a way of taking you out for a pint or even a slap up meal, I'd be doing it. :)

    Terry
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    See if this is how you wanted.
    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Jindon,

    You Sir are amazing!! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

    Just one problem, on the User's Parent Company, if the user isn't found it inserts a 0 therefore the colour doesn't kick in. What part of the code do I need to change so if it = 0 then the background (and text colour) is red?

    Thank you again (just in case I missed saying it)

    Terry

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Why don't you just delete "User3" from "Known Users"?

    Doesn't make sense to leave such data in the look up table has no corresponding value.

  21. #21
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    It'll be because it will identify that User 3 has been identified as a User ID but that User ID does not have a parent company assigned to it. The colouring flags this up to the user and prompts them to identify the User's Parent Company. This will then be entered in the Known Users tab for future reference.

    Terry

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Then add User3 or whatever when parent company is given....
    Don't understand why you want to make it so complex.

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: Extracting data from multiple columns on multiple sheets / tabs etc to a common matrix

    Forgive me, I think I'm not explaining myself very clearly for which I apologise.

    The idea is that when the macro is run it will identify all the unique users from the 'systems' tabs, it will then compare those users with the 'known users' in the system tab. If it finds it, it inserts the company name under the 'User's Parent Company' column. If it doesn't find it in the known users it colours the cell red. This then vividly informs the person operating the workbook that a user has been identified that is not in the 'Known Users' tab and, as a consequence, does not have a company associated with that User ID. The operator will be able to quickly identify them. Then, independently, find out which company that USER ID belongs to and insert them in the 'Known Users' list. This will prevent it coming up again on the last review.

    This enables the operator to quickly identify if a new user has been added to a system since the last review.

    I'm sorry if I've been frustrating you and really do understand that I am pushing the boundaries of good will.

    However, you've solved it, for which I am extremely grateful.

+ 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. [SOLVED] Extracting data from different sheets / tabs etc to a common matrix
    By MagicMan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-19-2015, 03:16 PM
  2. Extracting multiple webpages (1 website, multiple tabs) into excel using a macro
    By Rjk214 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2015, 05:27 PM
  3. Replies: 1
    Last Post: 11-23-2013, 12:05 PM
  4. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  5. Replies: 2
    Last Post: 04-04-2013, 12:26 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