PDA

View Full Version : Special Fill Rows and Columns Based on Dynamic Named Ranges



Instigator
2016-08-22, 02:44 PM
Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
...Col(nth)


Row1
itemA
itemE
itemD
itemC
itemB
itemA
itemE
itemD
itemC


Row2
itemB
itemA
itemE
itemD
itemC
itemB
itemA
itemE
itemD


Row3
itemC
itemB
itemA
itemE
itemD
itemC
itemB
itemA
itemE


Row4
itemD
itemC
itemB
itemA
itemE
itemD
itemC
itemB
itemA














Row5
textR
textV
textT
textR
textV
textT
textR
textV
textT


Row6
textS
textW
textU
textS
textW
textU
textS
textW
textU


Row7
textT
textR
textV
textT
textR
textV
textT
textR
textV


Row8
textU
textS
textW
textU
textS
textW
textU
textS
textW





Dyanmic Named Range 1
Dynamic Named Range 2


itemA
textR


itemB
textS


itemC
textT


itemD
textU


itemE
textV


item(nth)
textW



text(nth)



Okay, so what I am trying to do is create two dynamic ranges where the quantity (#) of values in each column is arbitrary. Then, on a second sheet, paste these ranges in the format that I laid out in the top table so that the table is filled with a repetition of values in the area specified based on the named ranges. The process would look something like this: Range 1 fills (in a looping fashion) Col1 Row1 through Row4, then fills Col2 Row1 through Row4, then fills Col3 Row1 through Row4...etc. In this way, the total area (Col1 Row1 to Col(nth) Row4) repeats the list values from Range 1, despite the quantity of values in the range. A similar process would be completed for Range 2 on Rows 5 through 8.

If any anything in my explanation in my problem was unclear, please let me know and I will try my best to provide further explanation.

Thank you in advance for any assistance that anyone has to offer as to how the most sensible method to accomplish this task would be.

navic
2016-08-22, 06:05 PM
For first range, put this ARRAY (http://www.tushar-mehta.com/publish_train/xl_vba_cases/array_formulas.htm) or CSE formula below

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$1:$A$4;MATCH(0;COUNTIF($M$1:M1;$A$1:$A$4);0));INDEX($B$1:$B$4;MATCH(0;COUNTIF($M$1:M1;$B$1:$B$4);0)));INDEX($C$1:$C$4;MATCH(0;COUNTIF($M$1:M1;$C$1:$C$4);0)));INDEX($D$1:$D$4;MATCH(0;COUNTIF($M$1:M1;$D$1:$D$4);0)));INDEX($E$1:$E$4;MATCH(0;COUNTIF($M$1:M1;$E$1:$E$4);0)));INDEX($F$1:$F$4;MATCH(0;COUNTIF($M$1:M1;$F$1:$F$4);0)));INDEX($G$1:$G$4;MATCH(0;COUNTIF($M$1:M1;$G$1:$G$4);0)));INDEX($H$1:$H$4;MATCH(0;COUNTIF($M$1:M1;$H$1:$H$4);0)));INDEX($I$1:$I$4;MATCH(0;COUNTIF($M$1:M1;$I$1:$I$4);0)));"")

Instigator
2016-08-22, 06:58 PM
navic,

Thank you for the formula and attached worksheet. I see what you did on the worksheet and, while it does work correctly :thumb:, it is the reverse process that I am attempting to accomplish. I am trying to copy the text from from M2:M6 (based on your worksheet) and paste it in A1:I4 in a repeating manner.

ie.
A1 -- itemA
A2 -- itemB
A3 -- itemC
A4 -- itemD
B1 -- itemE
etc. and then the list repeats again in the cell below...
B2 -- itemA
B3 -- itemB
so forth and so on...

Does this make sense?

navic
2016-08-25, 04:55 PM
I think it's easier VBA

Instigator
2016-09-06, 02:47 PM
Do you have an idea of how I might go about accomplishing it in VBA?

navic
2016-09-06, 06:26 PM
You need to ask on the VBA forum (http://www.excelguru.ca/forums/forumdisplay.php?9-VBA-Programming)

Instigator
2016-09-07, 12:32 PM
Thanks. My apologies.

Mogal
2016-10-09, 11:59 AM
Hello Instigator, where you able to solve this?
I am trying to acomplish the same thing.

I can post links yet, but you can search for this:
Populating Cells from reocurring list

navic
2016-10-10, 09:10 AM
I am trying to acomplish the same thing.
See this thread (http://www.excelguru.ca/forums/showthread.php?6826-Populating-Cells-from-reocurring-list)

Instigator
2016-10-12, 12:30 AM
Hello Instigator, where you able to solve this?
I am trying to acomplish the same thing.

I can post links yet, but you can search for this:
Populating Cells from reocurring list

Hi Mogal,

Sorry, but I have not yet reached a solution. The best that I can come up with is manually typing in the data which is kind of a pain but I don't know of any other way to accomplish the task at the moment. I am reviewing navic's solution on your thread to see what he came up with.

navic
2016-10-12, 07:35 AM
I am reviewing navic's solution on your thread to see what he came up with.See example attached