Special Fill Rows and Columns Based on Dynamic Named Ranges

Instigator

New member
Joined
Aug 9, 2016
Messages
41
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
Col1Col2Col3Col4Col5Col6Col7Col8...Col(nth)
Row1itemAitemEitemDitemCitemBitemAitemEitemDitemC
Row2itemBitemAitemEitemDitemCitemBitemAitemEitemD
Row3itemCitemBitemAitemEitemDitemCitemBitemAitemE
Row4itemDitemCitemBitemAitemEitemDitemCitemBitemA
Row5textRtextVtextTtextRtextVtextTtextRtextVtextT
Row6textStextWtextUtextStextWtextUtextStextWtextU
Row7textTtextRtextVtextTtextRtextVtextTtextRtextV
Row8textUtextStextWtextUtextStextWtextUtextStextW

Dyanmic Named Range 1Dynamic Named Range 2
itemAtextR
itemBtextS
itemCtextT
itemDtextU
itemEtextV
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.
 
Last edited:
Unique data from Excel range with IFERROR-COUNTIF-INDEX-MATCH functions

For first range, put this ARRAY or CSE formula below
Code:
=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)));"")
 

Attachments

  • instigator-navic1.xlsx
    10 KB · Views: 10
Last edited:
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?
 

Attachments

  • instigator-navic1-rev1.xlsx
    11 KB · Views: 8
I think it's easier VBA
 
Do you have an idea of how I might go about accomplishing it in VBA?
 
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
 
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.
 

Attachments

  • Instigator-navic321.xlsx
    13.5 KB · Views: 6
Back
Top