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.

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.

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)