Merge lists with second list value every nth row

T

TurpsUK

New Member
Joined
Apr 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi All, can anyone help with an excel formula to incorporate values from a secondary list into a primary list with row intervals every nth value? The first list is 200 compound text names, and I would like to incorporate interspersed 'control compound' names every 6th row if possible. So starting with 'Control compound' in row 1 then Compound 1,2,3,4 & 5, Control compound, Compound 6,7,8,9 & 10 etc..... I have tried to construct a helper column which reads back a row identifier 0,1,2,3,4,5 with =MOD(ROW(A2)-ROW($A$1)-1,N), and this seems to function okay allowing the Control compound intended position to be incorporated in Column E with an IF statement that reads the 0, but I cannot reference the column list (A) into this also without it going out of sequence.
Any help would be much appreciated. Regards
 

Attachments

  • nth row merge lists.PNG
    nth row merge lists.PNG
    46.8 KB · Views: 23

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Welcome to the MrExcel board!

Is it something like this that you are after?

BTW, have a look at XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 04 06.xlsm
ABCDE
1
2Compound 1Control 16Control 1
3Compound 2Compound 1
4Compound 3Compound 2
5Compound 4Compound 3
6Compound 5Compound 4
7Compound 6Compound 5
8Compound 7Control 1
9Compound 8Compound 6
10Compound 9Compound 7
11Compound 10Compound 8
12Compound 11Compound 9
13Compound 12Compound 10
14Compound 13Control 1
15Compound 14Compound 11
16Compound 15Compound 12
17Compound 16Compound 13
18Compound 17Compound 14
19Compound 15
Combine lists
Cell Formulas
RangeFormula
E2:E19E2=IF(MOD(ROWS(E$2:E2),C$2)=1,B$2,INDEX(A$2:A$40,ROWS(E$2:E2)-COUNTIF(E$1:E1,B$2)))
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Is it something like this that you are after?

BTW, have a look at XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 04 06.xlsm
ABCDE
1
2Compound 1Control 16Control 1
3Compound 2Compound 1
4Compound 3Compound 2
5Compound 4Compound 3
6Compound 5Compound 4
7Compound 6Compound 5
8Compound 7Control 1
9Compound 8Compound 6
10Compound 9Compound 7
11Compound 10Compound 8
12Compound 11Compound 9
13Compound 12Compound 10
14Compound 13Control 1
15Compound 14Compound 11
16Compound 15Compound 12
17Compound 16Compound 13
18Compound 17Compound 14
19Compound 15
Combine lists
Cell Formulas
RangeFormula
E2:E19E2=IF(MOD(ROWS(E$2:E2),C$2)=1,B$2,INDEX(A$2:A$40,ROWS(E$2:E2)-COUNTIF(E$1:E1,B$2)))
Click to expand...
Excel Formula:
 
Upvote 0
Hi Peter, thanks very much for responding, the formula worked a treat! I'm a newbie to Mr Excel and have downloaded the XL2BB add in now. A subsequent issue is the value in Column B is dynamic in nature and changes every so often. I tried incorporating all the list values in Column B side by side to Column A and removed the $ sign in the formula, but it does not read back this value every nth (6th row) as the sequence gets corrupted at the point at which the value changes; I'm guessing because it is not indexed in the same way. Do you have any advice on how to resolve this aspect?
Many thanks and best regards

List row Query.xlsx
ABCDEF
1List 1List 2nth rowlist valueHelperSeq ListSeq List with column B List
2Compound 35Control 160Control 1Control 1
3Compound 163Control 11Compound 35Compound 35
4Compound 157Control 12Compound 163Compound 163
5Compound 138Control 13Compound 157Compound 157
6Compound 128Control 14Compound 138Compound 138
7Compound 121Control 15Compound 128Compound 128
8Compound 148Control 10Control 1Control 1
9Compound 171Control 11Compound 121Compound 121
10Compound 38Control 12Compound 148Compound 148
11Compound 81Control 13Compound 171Compound 171
12Compound 87Control 14Compound 38Compound 38
13Compound 170Control 15Compound 81Compound 81
14Compound 144Control 10Control 1Control 1
15Compound 27Control 11Compound 87Compound 87
16Compound 110Control 12Compound 170Compound 170
17Compound 189Control 13Compound 144Compound 144
18Compound 120Control 14Compound 27Compound 27
19Compound 40Control 15Compound 110Compound 110
20Compound 92Control 10Control 1Control 1
21Compound 72Control 11Compound 189Compound 189
22Compound 97Control 12Compound 120Compound 120
23Compound 51Control 13Compound 40Compound 40
24Compound 191Control 14Compound 92Compound 92
25Compound 77Control 15Compound 72Compound 72
26Compound 108Control 10Control 1Control 1
27Compound 131Control 11Compound 97Compound 97
28Compound 96Control 12Compound 51Compound 51
29Compound 20Control 13Compound 191Compound 191
30Compound 12Control 14Compound 77Compound 77
31Compound 140Control 15Compound 108Compound 108
32Compound 60Control 10Control 1Control 1
33Compound 57Control 11Compound 131Compound 131
34Compound 59Control 12Compound 96Compound 96
35Compound 179Control 13Compound 20Compound 20
36Compound 24Control 14Compound 12Compound 12
37Compound 46Control 15Compound 140Compound 140
38Compound 62Control 10Control 1Control 1
39Compound 109Control 11Compound 60Compound 60
40Compound 31Control 12Compound 57Compound 57
41Compound 187Control 13Compound 59Compound 59
42Compound 66Control 14Compound 179Compound 179
43Compound 47Control 15Compound 24Compound 24
44Compound 183Control 10Control 1Control 1
45Compound 44Control 11Compound 46Compound 46
46Compound 11Control 12Compound 62Compound 62
47Compound 145Control 13Compound 109Compound 109
48Compound 117Control 14Compound 31Compound 31
49Compound 94Control 15Compound 187Compound 187
50Compound 84Control 10Control 1Control 1
51Compound 175Control 21Compound 66Compound 175
52Compound 19Control 22Compound 47Compound 19
53Compound 4Control 23Compound 183Compound 4
54Compound 95Control 24Compound 44Compound 95
55Compound 6Control 25Compound 11Compound 6
56Compound 169Control 20Control 1Control 2
57Compound 17Control 21Compound 145Compound 17
58Compound 200Control 22Compound 117Compound 200
59Compound 164Control 23Compound 94Compound 164
60Compound 7Control 24Compound 84Compound 7
61Compound 54Control 25Compound 175Compound 54
62Compound 15Control 20Control 1Control 2
63Compound 13Control 21Compound 19Compound 13
64Compound 33Control 22Compound 4
Sheet1
Cell Formulas
RangeFormula
D2:D64D2=MOD(ROW(A2)-ROW($A$1)-1,$C$2)
E2:E64E2=IF(MOD(ROWS(E$2:E2),C$2)=1,B$2,INDEX(A$2:A$230,ROWS(E$2:E2)-COUNTIF(E$1:E1,B$2)))
F2:F63F2=IF(MOD(ROWS(E$2:E2),C$2)=1,B2,INDEX(A$2:A$230,ROWS(E$2:E2)-COUNTIF(E$1:E1,B2)))
 
Upvote 0
Using your helper col D, how about
+Fluff 1.xlsm
ABCDEF
1List 1List 2nth rowlist valueHelperSeq ListSeq List with column B List
2Compound 35Control 160Control 1Control 1
3Compound 163Control 11Compound 35Compound 35
4Compound 157Control 12Compound 163Compound 163
5Compound 138Control 13Compound 157Compound 157
6Compound 128Control 14Compound 138Compound 138
7Compound 121Control 15Compound 128Compound 128
8Compound 148Control 10Control 1Control 1
9Compound 171Control 11Compound 121Compound 121
10Compound 38Control 12Compound 148Compound 148
11Compound 81Control 13Compound 171Compound 171
12Compound 87Control 14Compound 38Compound 38
13Compound 170Control 15Compound 81Compound 81
14Compound 144Control 10Control 1Control 1
15Compound 27Control 11Compound 87Compound 87
16Compound 110Control 12Compound 170Compound 170
17Compound 189Control 13Compound 144Compound 144
18Compound 120Control 14Compound 27Compound 27
19Compound 40Control 15Compound 110Compound 110
20Compound 92Control 10Control 1Control 1
21Compound 72Control 11Compound 189Compound 189
22Compound 97Control 12Compound 120Compound 120
23Compound 51Control 13Compound 40Compound 40
24Compound 191Control 14Compound 92Compound 92
25Compound 77Control 15Compound 72Compound 72
26Compound 108Control 10Control 1Control 1
27Compound 131Control 11Compound 97Compound 97
28Compound 96Control 12Compound 51Compound 51
29Compound 20Control 13Compound 191Compound 191
30Compound 12Control 14Compound 77Compound 77
31Compound 140Control 15Compound 108Compound 108
32Compound 60Control 10Control 1Control 1
33Compound 57Control 11Compound 131Compound 131
34Compound 59Control 12Compound 96Compound 96
35Compound 179Control 13Compound 20Compound 20
36Compound 24Control 14Compound 12Compound 12
37Compound 46Control 15Compound 140Compound 140
38Compound 62Control 10Control 1Control 1
39Compound 109Control 11Compound 60Compound 60
40Compound 31Control 12Compound 57Compound 57
41Compound 187Control 13Compound 59Compound 59
42Compound 66Control 14Compound 179Compound 179
43Compound 47Control 15Compound 24Compound 24
44Compound 183Control 10Control 1Control 1
45Compound 44Control 11Compound 46Compound 46
46Compound 11Control 12Compound 62Compound 62
47Compound 145Control 13Compound 109Compound 109
48Compound 117Control 14Compound 31Compound 31
49Compound 94Control 15Compound 187Compound 187
50Compound 84Control 10Control 1Control 1
51Compound 175Control 21Compound 66Compound 66
52Compound 19Control 22Compound 47Compound 47
53Compound 4Control 23Compound 183Compound 183
54Compound 95Control 24Compound 44Compound 44
55Compound 6Control 25Compound 11Compound 11
56Compound 169Control 20Control 1Control 2
57Compound 17Control 21Compound 145Compound 145
58Compound 200Control 22Compound 117Compound 117
59Compound 164Control 23Compound 94Compound 94
60Compound 7Control 24Compound 84Compound 84
61Compound 54Control 25Compound 175Compound 175
62Compound 15Control 20Control 1Control 2
63Compound 13Control 21Compound 19Compound 19
64Compound 33Control 22Compound 4Compound 4
Master
Cell Formulas
RangeFormula
D2:D64D2=MOD(ROW(A2)-ROW($A$1)-1,$C$2)
E2:E64E2=IF(MOD(ROWS(E$2:E2),C$2)=1,B$2,INDEX(A$2:A$230,ROWS(E$2:E2)-COUNTIF(E$1:E1,B$2)))
F2:F64F2=IF(MOD(ROWS(E$2:E2),C$2)=1,B2,INDEX(A$2:A$230,ROWS(E$2:E2)-COUNTIF(D$1:D1,0)))
 
Upvote 0
Solution
That has cracked it! Thanks Fluff and Peter for your help, and helping me learn a few things in excel today!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
If you are going to use the helper column then there is no need for MOD in the other formulas. In fact they can be considerably simpler.
Also suggesting a slightly simpler formula for the helper column.

21 04 06.xlsm
ABCDEF
1List 1List 2nth rowlist valueHelperSeq ListSeq List with column B List
2Compound 35Control 160Control 1Control 1
3Compound 163Control 11Compound 35Compound 35
4Compound 157Control 12Compound 163Compound 163
5Compound 138Control 13Compound 157Compound 157
6Compound 128Control 14Compound 138Compound 138
7Compound 121Control 15Compound 128Compound 128
8Compound 148Control 10Control 1Control 1
9Compound 171Control 11Compound 121Compound 121
10Compound 38Control 12Compound 148Compound 148
11Compound 81Control 13Compound 171Compound 171
12Compound 87Control 14Compound 38Compound 38
13Compound 170Control 15Compound 81Compound 81
14Compound 144Control 10Control 1Control 1
15Compound 27Control 11Compound 87Compound 87
16Compound 110Control 12Compound 170Compound 170
17Compound 189Control 13Compound 144Compound 144
18Compound 120Control 14Compound 27Compound 27
19Compound 40Control 15Compound 110Compound 110
20Compound 92Control 10Control 1Control 1
21Compound 72Control 11Compound 189Compound 189
22Compound 97Control 12Compound 120Compound 120
23Compound 51Control 13Compound 40Compound 40
24Compound 191Control 14Compound 92Compound 92
25Compound 77Control 15Compound 72Compound 72
26Compound 108Control 10Control 1Control 1
27Compound 131Control 11Compound 97Compound 97
28Compound 96Control 12Compound 51Compound 51
29Compound 20Control 13Compound 191Compound 191
30Compound 12Control 14Compound 77Compound 77
31Compound 140Control 15Compound 108Compound 108
32Compound 60Control 10Control 1Control 1
33Compound 57Control 11Compound 131Compound 131
34Compound 59Control 12Compound 96Compound 96
35Compound 179Control 13Compound 20Compound 20
36Compound 24Control 14Compound 12Compound 12
37Compound 46Control 15Compound 140Compound 140
38Compound 62Control 10Control 1Control 1
39Compound 109Control 11Compound 60Compound 60
40Compound 31Control 12Compound 57Compound 57
41Compound 187Control 13Compound 59Compound 59
42Compound 66Control 14Compound 179Compound 179
43Compound 47Control 15Compound 24Compound 24
44Compound 183Control 10Control 1Control 1
45Compound 44Control 11Compound 46Compound 46
46Compound 11Control 12Compound 62Compound 62
47Compound 145Control 13Compound 109Compound 109
48Compound 117Control 14Compound 31Compound 31
49Compound 94Control 15Compound 187Compound 187
50Compound 84Control 10Control 1Control 1
51Compound 175Control 21Compound 66Compound 66
52Compound 19Control 22Compound 47Compound 47
53Compound 4Control 23Compound 183Compound 183
54Compound 95Control 24Compound 44Compound 44
55Compound 6Control 25Compound 11Compound 11
56Compound 169Control 20Control 1Control 2
57Compound 17Control 21Compound 145Compound 145
58Compound 200Control 22Compound 117Compound 117
59Compound 164Control 23Compound 94Compound 94
60Compound 7Control 24Compound 84Compound 84
61Compound 54Control 25Compound 175Compound 175
62Compound 15Control 20Control 1Control 2
63Compound 13Control 21Compound 19Compound 19
64Compound 33Control 22Compound 4Compound 4
Combine lists (3)
Cell Formulas
RangeFormula
D2:D64D2=MOD(ROWS(D$2:D2)-1,C$2)
E2:E64E2=IF(D2=0,B$2,INDEX(A$2:A$230,COUNTIF(D$2:D2,">0")))
F2:F64F2=IF(D2=0,B2,INDEX(A$2:A$230,COUNTIF(D$2:D2,">0")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Data Validation List - Auto update cell values with a twist
Replies
1
Views
983
Fritz23
F
SanjayGMusafir
  • Solved
Filtering a list further
Replies
3
Views
210
Fluff
Fluff
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
469
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
M
  • Question
SOS for dividing every row by a rolling 16th row, creating a % composition by group
Replies
2
Views
271
Fluff
Fluff

Forum statistics

Threads
1,215,646
Messages
6,125,997
Members
449,279
Latest member
Faraz5023

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top