Hi all, this is my first post although, I've been lurking for a long long time and have gotten invaluable tips from this site. So greatly appreciated!!
I'm unsure whether this is better suited for VBA, or just a good formula in excel(?). I'm leaning towards the former as the research I've done thus far is all leaning that way.
My query is this:
I have two columns of numbers, A, and B. I need to merge both columns of numbers, but in a defined order based on the value in a cell, in Column C.
Eg. If the cell value reads 0, I need 1 line from Column A, and 1 from Column B, stacked and repeated in Column C;
If the cell value reads 1, I need 2 lines from column A, and 1 line from Column B, stacked, and repeated with the next available, unused numbers in Column C;
If the cell value reads 2, I need 3 lines from Column A, and 1 line from Column B, stacked and repeated with the next available, unused lines in Column C; etc...
This needs to repeat in the 3rd Column until all the lines are exhausted from Column B. In each scenario, from Column A, I need the first available set of lines, then the second available set of lines, etc... (I cannot skip any lines from either Column A or B). There will never be a time that I ask for more than 1 line at a time from Column B. I may need up to 10 lines at a time from Column A.
I could have up to 1000 lines of numbers in Column A, and up to 250 lines of numbers in Column B.
Thus far, I've tried an offset formula that is going the right direction however, it only utilized the number on the line it was on in Column C. I'm thinking a Select Case statement in VBA may be able to automate this whole process however, I'm extremely novice at best and am unsure how to put it all together.
Any suggestions on where to start or how to accomplish this task would be greatly appreciated.
Thanks and regards,
CdnTechMngr