Good day,
I have a range which contains the names of staff members in Column B36-B1000 and the names of the staff members in column C36-C1000. I need to copy the same 11 rows containing specific information into column C below the number and name of the staff member.
Is there an easy way to do this without having to do the copy and paste job 1000 times?
1. Is it a VBA solution you want?
2. Can you post a mock-up of your data, as I don't quite follow your explanation....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Good day Glenn,
I am attaching mock-up of the data as requested.
I am not sure if it should be a formula or VBA Code.
Any advice will be appreciated.
Louisa
For two of your people (that should be sufficient), please post what your desired result should look like...
Hi Glen, the xls which I attached pretty much indicates what it should look like after completion, with the correct formatting and all.
It's the reference to "11 rows" that has me confoosed. Which 11 rows?
Hi Glenn, Jip I can see why.
I added all the notes int he correct formatting as well as the concatenated numbers when I did the mock-up and then completely forgot to give the revised information through.
The rows that need to repeat below each name is row 3-23.
Regards
Louisa
OK. Try this out. Take a look at the sheet "Final". Don't worry about all the clutter on "Staff" and "Profile". That can come out once you're happy with the solution....
Good day Glenn,
Yes it works. Would you mind explaining the elements of the formula so that I can understand better how it works?
Regards
Louisa
That might take a while... Let's start with the surname...
=IF(MOD((ROW()-2),22)=0,INDIRECT("'Staff'!B"&INT((ROW()-2)/22+2)),"")
IF(MOD((ROW()-2),22)=0: Take the row number, subtract 2 and divide by 22 (the number of spaces needed between each name). It the remainder is zero:
INDIRECT("'Staff'!B"&INT((ROW()-2)/22+2)) put in what you find in sheet Staff, column B row.... where row... the INTEGER arising from the sum: (row number minus 2) divided by 22 PLUS 2. This will have the effect that every 22nd row, starting from Row 2, it will increment the row number that it copies from the Staff list by ONE.
I the remainder is NOT zero (from a few lines back)... put nothing.
End result, a new name every 22nd row.
The remainder (bar 1) are simply variants on that to suit your needs. The exception is the staff number/subheading. This one was difficult and was (in effect) a comination of a formula to generate the staff number 12 times on alternate rows before moving on to the next one PLUS a formula to put in /1 /2, etc from the 2nd to the 12th occurrence of the number and to put nothing at the first one. Getting that right did talke quite a long time. The agonising workings-out are still to be seen on your sheet.
If you want further explanation let me know.
However, I'm glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
Dear Glen, Apologies for taking so long to respond to the resolution for my problem. I got side tracked on another project and only got back to this one now.
Your solution worked brilliantly. Thank you so much. With your explanation included I also managed to apply it to other sheets with similar issues.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks