+ Reply to Thread
Results 1 to 5 of 5

Combining and rearranging data records from multiple worksheets to a single worksheet

  1. #1
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Combining and rearranging data records from multiple worksheets to a single worksheet

    Hello,

    Iím really stumped by an issue Iím having in Excel 2003, and Iím sure itís just because thereís a step in VBA I canít get my head around. Itís sort of a double look-up, but is hard to explain until you see it in action. Iíve attached an example spreadsheet with a simplified layout of my problem (the Ďrealí version has hundreds upon hundreds of records). The two input sheets provide all the data I need to create an output sheet, but I canít get my head round automating the process of creating this output sheet (using VBA or otherwise).

    Basically, I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.

    The variables are:
    Position Number (Sheet 1)
    Position Title (Sheets 1 and 2)
    Position Requirement (Sheet 2)
    Requirement Importance (Sheet 2)

    The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).

    Normally, Iíd be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I canít figure out how to say to Excel ďa plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbersĒ.

    Any help here would be massively appreciated, as Iím thoroughly stumped and getting quite frustrated! Iím still a relatively new user of VBA, but have a good understanding of the logic used (just not the vocabulary of terms, so itís quite possible Excel or VBA has a function somewhere that will do exactly what I want!).

    Thanks a bunch for your time.
    Attached Files Attached Files
    Last edited by danb1985; 09-14-2009 at 09:56 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining and rearranging data records from multiple worksheets to a single works

    Here's a macro that should do it for you. All the sheets need to be named exactly as per your sample, or edit the macro accordingly.
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ďNone of us is as good as all of usĒ - Ray Kroc
    ďActually, I *am* a rocket scientist - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Combining and rearranging data records from multiple worksheets to a single works

    That worked beautifully - thank you so much for taking the time to do this.

    It's hugely appreciated and just cut our workload by several thousand per cent!

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining and rearranging data records from multiple worksheets to a single works

    Well shucks.. (*blush*) Glad to help.

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

  5. #5
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Combining and rearranging data records from multiple worksheets to a single works

    Consider them both done!

    Many thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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