Identify gaps in a sequence in one cell - Excel
I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way:
Example:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | ? | 20
2 | 02 - 09 | ? | 10
3 | 01 - 04, 06 - 11 | ? | 11
Now, I want Column B to display the missing item numbers, like this:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | 14 - 20 | 20
2 | 02 - 09 | 01, 10 | 10
3 | 01 - 04, 06 - 11 | 05 | 11
I have managed to create a long-winded formula for cases like in line 1, but that only works by checking the last two digits of a cell in Column A, subtracting it from Column C's value in that line, then fill in Column B with the result.
(basically something like this (with some special cases not affected by the "gap problem" at hand)): $C1-Right($A1;2)&" - "&$C1)
But my approach (obviously) can't handle the gaps like in lines 2 and 3, and I want to know if there is a way to solve my issue.
P.S. I wasn't sure what title to give to this problem, so I apologize if it is misleading.
windows-10 microsoft-excel worksheet-function
asked Dec 8 '18 at 19:48
MajorTanya
1
add a comment |
I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way:
Example:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | ? | 20
2 | 02 - 09 | ? | 10
3 | 01 - 04, 06 - 11 | ? | 11
Now, I want Column B to display the missing item numbers, like this:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | 14 - 20 | 20
2 | 02 - 09 | 01, 10 | 10
3 | 01 - 04, 06 - 11 | 05 | 11
I have managed to create a long-winded formula for cases like in line 1, but that only works by checking the last two digits of a cell in Column A, subtracting it from Column C's value in that line, then fill in Column B with the result.
(basically something like this (with some special cases not affected by the "gap problem" at hand)): $C1-Right($A1;2)&" - "&$C1)
But my approach (obviously) can't handle the gaps like in lines 2 and 3, and I want to know if there is a way to solve my issue.
P.S. I wasn't sure what title to give to this problem, so I apologize if it is misleading.
windows-10 microsoft-excel worksheet-function
asked Dec 8 '18 at 19:48
MajorTanya
1
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36
add a comment |
I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way:
Example:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | ? | 20
2 | 02 - 09 | ? | 10
3 | 01 - 04, 06 - 11 | ? | 11
Now, I want Column B to display the missing item numbers, like this:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | 14 - 20 | 20
2 | 02 - 09 | 01, 10 | 10
3 | 01 - 04, 06 - 11 | 05 | 11
I have managed to create a long-winded formula for cases like in line 1, but that only works by checking the last two digits of a cell in Column A, subtracting it from Column C's value in that line, then fill in Column B with the result.
(basically something like this (with some special cases not affected by the "gap problem" at hand)): $C1-Right($A1;2)&" - "&$C1)
But my approach (obviously) can't handle the gaps like in lines 2 and 3, and I want to know if there is a way to solve my issue.
P.S. I wasn't sure what title to give to this problem, so I apologize if it is misleading.
windows-10 microsoft-excel worksheet-function
asked Dec 8 '18 at 19:48
MajorTanya
1
I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way:
Example:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | ? | 20
2 | 02 - 09 | ? | 10
3 | 01 - 04, 06 - 11 | ? | 11
Now, I want Column B to display the missing item numbers, like this:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | 14 - 20 | 20
2 | 02 - 09 | 01, 10 | 10
3 | 01 - 04, 06 - 11 | 05 | 11
I have managed to create a long-winded formula for cases like in line 1, but that only works by checking the last two digits of a cell in Column A, subtracting it from Column C's value in that line, then fill in Column B with the result.
(basically something like this (with some special cases not affected by the "gap problem" at hand)): $C1-Right($A1;2)&" - "&$C1)
But my approach (obviously) can't handle the gaps like in lines 2 and 3, and I want to know if there is a way to solve my issue.
P.S. I wasn't sure what title to give to this problem, so I apologize if it is misleading.
windows-10 microsoft-excel worksheet-function
windows-10 microsoft-excel worksheet-function
asked Dec 8 '18 at 19:48
MajorTanya
1
asked Dec 8 '18 at 19:48
MajorTanya
1
asked Dec 8 '18 at 19:48
MajorTanya
1
asked Dec 8 '18 at 19:48
MajorTanya
1
asked Dec 8 '18 at 19:48
MajorTanya
1
1
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36
add a comment |
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36
add a comment |
1 Answer
1
active
oldest
votes
Your problem can be solved by using the TREND function:
- Taking the first data sample in Column A, you need to create 1st sequence from 1 to 13 and 2nd from 1 to 20.
- Write this Array Formula in Cell
C1, finish withCtrl+Shift+Enter& fill down.
{=TREND(A1:A13,B1:B13,B14:B20)}
- You get series from
14 to 20. In
D1enter this Formula to get the1st Cell value.
=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))Enter this Formula in
D2to getlast Cell value.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- In Cell
D4enter this one=D1&"-"&D2, you get14-20.
Note:
For 2nd data sample you need to create 2 series.
1- 2 to 9.
2- 1 to 10.
3rd data sample needs 3 series.
1- 1 to 4.
2- 6 to 11.
3- 1 to 11.
- Repeat the above shown steps for other data samples.
- Adjust cell references in the Formula as needed.
answered Dec 10 '18 at 11:23
Rajesh S
1
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1381955%2fidentify-gaps-in-a-sequence-in-one-cell-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your problem can be solved by using the TREND function:
- Taking the first data sample in Column A, you need to create 1st sequence from 1 to 13 and 2nd from 1 to 20.
- Write this Array Formula in Cell
C1, finish withCtrl+Shift+Enter& fill down.
{=TREND(A1:A13,B1:B13,B14:B20)}
- You get series from
14 to 20. In
D1enter this Formula to get the1st Cell value.
=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))Enter this Formula in
D2to getlast Cell value.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- In Cell
D4enter this one=D1&"-"&D2, you get14-20.
Note:
For 2nd data sample you need to create 2 series.
1- 2 to 9.
2- 1 to 10.
3rd data sample needs 3 series.
1- 1 to 4.
2- 6 to 11.
3- 1 to 11.
- Repeat the above shown steps for other data samples.
- Adjust cell references in the Formula as needed.
answered Dec 10 '18 at 11:23
Rajesh S
1
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
add a comment |
Your problem can be solved by using the TREND function:
- Taking the first data sample in Column A, you need to create 1st sequence from 1 to 13 and 2nd from 1 to 20.
- Write this Array Formula in Cell
C1, finish withCtrl+Shift+Enter& fill down.
{=TREND(A1:A13,B1:B13,B14:B20)}
- You get series from
14 to 20. In
D1enter this Formula to get the1st Cell value.
=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))Enter this Formula in
D2to getlast Cell value.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- In Cell
D4enter this one=D1&"-"&D2, you get14-20.
Note:
For 2nd data sample you need to create 2 series.
1- 2 to 9.
2- 1 to 10.
3rd data sample needs 3 series.
1- 1 to 4.
2- 6 to 11.
3- 1 to 11.
- Repeat the above shown steps for other data samples.
- Adjust cell references in the Formula as needed.
answered Dec 10 '18 at 11:23
Rajesh S
1
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
add a comment |
Your problem can be solved by using the TREND function:
- Taking the first data sample in Column A, you need to create 1st sequence from 1 to 13 and 2nd from 1 to 20.
- Write this Array Formula in Cell
C1, finish withCtrl+Shift+Enter& fill down.
{=TREND(A1:A13,B1:B13,B14:B20)}
- You get series from
14 to 20. In
D1enter this Formula to get the1st Cell value.
=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))Enter this Formula in
D2to getlast Cell value.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- In Cell
D4enter this one=D1&"-"&D2, you get14-20.
Note:
For 2nd data sample you need to create 2 series.
1- 2 to 9.
2- 1 to 10.
3rd data sample needs 3 series.
1- 1 to 4.
2- 6 to 11.
3- 1 to 11.
- Repeat the above shown steps for other data samples.
- Adjust cell references in the Formula as needed.
answered Dec 10 '18 at 11:23
Rajesh S
1
Your problem can be solved by using the TREND function:
- Taking the first data sample in Column A, you need to create 1st sequence from 1 to 13 and 2nd from 1 to 20.
- Write this Array Formula in Cell
C1, finish withCtrl+Shift+Enter& fill down.
{=TREND(A1:A13,B1:B13,B14:B20)}
- You get series from
14 to 20. In
D1enter this Formula to get the1st Cell value.
=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))Enter this Formula in
D2to getlast Cell value.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- In Cell
D4enter this one=D1&"-"&D2, you get14-20.
Note:
For 2nd data sample you need to create 2 series.
1- 2 to 9.
2- 1 to 10.
3rd data sample needs 3 series.
1- 1 to 4.
2- 6 to 11.
3- 1 to 11.
- Repeat the above shown steps for other data samples.
- Adjust cell references in the Formula as needed.
answered Dec 10 '18 at 11:23
Rajesh S
1
answered Dec 10 '18 at 11:23
Rajesh S
1
answered Dec 10 '18 at 11:23
Rajesh S
1
answered Dec 10 '18 at 11:23
Rajesh S
1
1
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
add a comment |
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
This is an outside the box solution Some clever stuff here, but it needs a custom solution for each row. There's also the undocumented steps of recreating the data in a usable format. By the time you're done figuring out what setup is needed and creating it, it might be less work to just do it manually. It seems like to be a valid answer, it needs to start with the given data in the given form, end with the specified result, and accomplish all of the required steps in between. What's here only really deals with part of the problem, and isn't an automated, general solution. But a good start.
– fixer1234
Dec 10 '18 at 23:07
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Thanks @fixer1234 for valuable observation. This is what I found is working with the given data. Hope OP & others will like it ☺
– Rajesh S
Dec 11 '18 at 8:21
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
Just write your concern why Down voted,,?
– Rajesh S
Dec 16 '18 at 6:05
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1381955%2fidentify-gaps-in-a-sequence-in-one-cell-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
A formula cannot do that - you will need a VBA macro. Good luck.
– harrymc
Dec 8 '18 at 20:35
I'd think a function that includes a For-Next loop that check to see if the cycle number of the loop is included in any of the values in column A would be a start. You'd, of course, set the top value of the loop to the corresponding value in column C.
– BillDOe
Dec 8 '18 at 20:39
Is Col A always in exactly that format (single digits w/leading zeros, space-hyphen-space between numbers to specify a range, comma-space between ranges)? Can Col A formatting be adjusted so more conducive to parsing? Do the ranges always start at 1? Can the total (Col C) be anything, or is there an upper limit that no range will exceed? Is there a maximum potential number of ranges in Col A? Can Col A also contain single numbers? Does Col B need to be a comma-delimited list of numbers plus ranges, or would it work as just a list of individual numbers, even if some are sequential numbers?
– fixer1234
Dec 9 '18 at 5:12
The Output patterns are not similar so I don't think any single Formula will works for all Rows,, better edit the Question and do necessary corrections otherwise it may attract to Vote it to Close !!
– Rajesh S
Dec 9 '18 at 9:36