Mixing
array from continuous data?
I have a quite difficult problem that i cant wrap my head around.. hope you can help me!
Lets say my data is in A1:G1 for example:
A1 B1 C1 D1 E1 F1 G1
X X 0 X X X 0
or
Y X X X X Z X
The thing i would need to come up with is how to get array from this data according to the X, BUT if like in example 1 there is 2 times X in the beginning and 3 x next to each other so the array should come out like {2;2;0;3;3;3;0} so i want the array to be 7 long and the array should show the x as number how many are next to each other.
example 2 should come out like {0;4;4;4;4;0;1}
if you can figure this out would really help me alot!
Edit:
Trying to give out better, more bigger picture of what i mean..
if data is :
A B C
1 X X
2 X X
3 X
it should come out as
A B C
1: 2 4 0
2: 0 4 2
3: 1 0 0
or in array {24;042;1}
on B1 and B2 there should be 4 because the formula should count horizontal but also vertical continuum. I tried to use usmanhaqs formula but i was not able to modify it so the count resets on every line.
Real size of the table is 7 times 7 cells.
I will use the array with another array (scoreboard which is also 7 times 7 cells, and has numbers 1, 2 or 3 on every cell) using sumproduct and it will give out the points of that player.
I appreciate your efforts on helping out a newbie learner on vba :)
excel vba excel-formula
asked Nov 20 '18 at 4:29
MikaelMikael
403
add a comment |
I have a quite difficult problem that i cant wrap my head around.. hope you can help me!
Lets say my data is in A1:G1 for example:
A1 B1 C1 D1 E1 F1 G1
X X 0 X X X 0
or
Y X X X X Z X
The thing i would need to come up with is how to get array from this data according to the X, BUT if like in example 1 there is 2 times X in the beginning and 3 x next to each other so the array should come out like {2;2;0;3;3;3;0} so i want the array to be 7 long and the array should show the x as number how many are next to each other.
example 2 should come out like {0;4;4;4;4;0;1}
if you can figure this out would really help me alot!
Edit:
Trying to give out better, more bigger picture of what i mean..
if data is :
A B C
1 X X
2 X X
3 X
it should come out as
A B C
1: 2 4 0
2: 0 4 2
3: 1 0 0
or in array {24;042;1}
on B1 and B2 there should be 4 because the formula should count horizontal but also vertical continuum. I tried to use usmanhaqs formula but i was not able to modify it so the count resets on every line.
Real size of the table is 7 times 7 cells.
I will use the array with another array (scoreboard which is also 7 times 7 cells, and has numbers 1, 2 or 3 on every cell) using sumproduct and it will give out the points of that player.
I appreciate your efforts on helping out a newbie learner on vba :)
excel vba excel-formula
asked Nov 20 '18 at 4:29
MikaelMikael
403
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40
add a comment |
I have a quite difficult problem that i cant wrap my head around.. hope you can help me!
Lets say my data is in A1:G1 for example:
A1 B1 C1 D1 E1 F1 G1
X X 0 X X X 0
or
Y X X X X Z X
The thing i would need to come up with is how to get array from this data according to the X, BUT if like in example 1 there is 2 times X in the beginning and 3 x next to each other so the array should come out like {2;2;0;3;3;3;0} so i want the array to be 7 long and the array should show the x as number how many are next to each other.
example 2 should come out like {0;4;4;4;4;0;1}
if you can figure this out would really help me alot!
Edit:
Trying to give out better, more bigger picture of what i mean..
if data is :
A B C
1 X X
2 X X
3 X
it should come out as
A B C
1: 2 4 0
2: 0 4 2
3: 1 0 0
or in array {24;042;1}
on B1 and B2 there should be 4 because the formula should count horizontal but also vertical continuum. I tried to use usmanhaqs formula but i was not able to modify it so the count resets on every line.
Real size of the table is 7 times 7 cells.
I will use the array with another array (scoreboard which is also 7 times 7 cells, and has numbers 1, 2 or 3 on every cell) using sumproduct and it will give out the points of that player.
I appreciate your efforts on helping out a newbie learner on vba :)
excel vba excel-formula
asked Nov 20 '18 at 4:29
MikaelMikael
403
I have a quite difficult problem that i cant wrap my head around.. hope you can help me!
Lets say my data is in A1:G1 for example:
A1 B1 C1 D1 E1 F1 G1
X X 0 X X X 0
or
Y X X X X Z X
The thing i would need to come up with is how to get array from this data according to the X, BUT if like in example 1 there is 2 times X in the beginning and 3 x next to each other so the array should come out like {2;2;0;3;3;3;0} so i want the array to be 7 long and the array should show the x as number how many are next to each other.
example 2 should come out like {0;4;4;4;4;0;1}
if you can figure this out would really help me alot!
Edit:
Trying to give out better, more bigger picture of what i mean..
if data is :
A B C
1 X X
2 X X
3 X
it should come out as
A B C
1: 2 4 0
2: 0 4 2
3: 1 0 0
or in array {24;042;1}
on B1 and B2 there should be 4 because the formula should count horizontal but also vertical continuum. I tried to use usmanhaqs formula but i was not able to modify it so the count resets on every line.
Real size of the table is 7 times 7 cells.
I will use the array with another array (scoreboard which is also 7 times 7 cells, and has numbers 1, 2 or 3 on every cell) using sumproduct and it will give out the points of that player.
I appreciate your efforts on helping out a newbie learner on vba :)
excel vba excel-formula
excel vba excel-formula
asked Nov 20 '18 at 4:29
MikaelMikael
403
asked Nov 20 '18 at 4:29
MikaelMikael
403
edited Nov 21 '18 at 10:43
Mikael
asked Nov 20 '18 at 4:29
MikaelMikael
403
asked Nov 20 '18 at 4:29
MikaelMikael
403
asked Nov 20 '18 at 4:29
MikaelMikael
403
403
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40
add a comment |
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40
add a comment |
2 Answers
2
active
oldest
votes
You can test this code
Function get_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
array_value = "{"
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
array_value = array_value & WorksheetFunction.Rept(j - i & ", ", j - i)
i = j - 1
Else
array_value = array_value & "0, "
End If
Next
array_value = Left(array_value, Len(array_value) - 2) & "}"
get_array = array_value
End Function
You can use it as below
EDIT
find below another function to return an array of values that can be used in the formulae
Function get_number_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
Dim number_array(1 To 50) As Long
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
For k = 1 To j - i
number_array(i + k - 1) = j - i
Next k
i = j - 1
Else
number_array(i) = 0
End If
Next
get_number_array = number_array
End Function
You have to use it same as the previous one, but it will return excel array.
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
|
show 5 more comments
For a formula solution, I can only come up with one for the special case where you have just X's and zeroes (example 1) so far:
=SUM(IF(A1:G1<>"X",0,INDEX(FREQUENCY(IF(A1:G1="X",COLUMN(A1:G1)),IF(A1:G1<>"X",COLUMN(A1:G1))),N(IF({1},SUBTOTAL(2,OFFSET(A1,0,0,1,COLUMN(A1:G1)))))+1,1)))
entered as an array formula using CtrlShiftEnter
I have wrapped it in a SUM function to demonstrate that it generates an array which can be passed to another function (result: 13) or it can be array-entered across several cells:
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53386246%2farray-from-continuous-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can test this code
Function get_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
array_value = "{"
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
array_value = array_value & WorksheetFunction.Rept(j - i & ", ", j - i)
i = j - 1
Else
array_value = array_value & "0, "
End If
Next
array_value = Left(array_value, Len(array_value) - 2) & "}"
get_array = array_value
End Function
You can use it as below
EDIT
find below another function to return an array of values that can be used in the formulae
Function get_number_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
Dim number_array(1 To 50) As Long
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
For k = 1 To j - i
number_array(i + k - 1) = j - i
Next k
i = j - 1
Else
number_array(i) = 0
End If
Next
get_number_array = number_array
End Function
You have to use it same as the previous one, but it will return excel array.
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
|
show 5 more comments
You can test this code
Function get_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
array_value = "{"
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
array_value = array_value & WorksheetFunction.Rept(j - i & ", ", j - i)
i = j - 1
Else
array_value = array_value & "0, "
End If
Next
array_value = Left(array_value, Len(array_value) - 2) & "}"
get_array = array_value
End Function
You can use it as below
EDIT
find below another function to return an array of values that can be used in the formulae
Function get_number_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
Dim number_array(1 To 50) As Long
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
For k = 1 To j - i
number_array(i + k - 1) = j - i
Next k
i = j - 1
Else
number_array(i) = 0
End If
Next
get_number_array = number_array
End Function
You have to use it same as the previous one, but it will return excel array.
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
|
show 5 more comments
You can test this code
Function get_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
array_value = "{"
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
array_value = array_value & WorksheetFunction.Rept(j - i & ", ", j - i)
i = j - 1
Else
array_value = array_value & "0, "
End If
Next
array_value = Left(array_value, Len(array_value) - 2) & "}"
get_array = array_value
End Function
You can use it as below
EDIT
find below another function to return an array of values that can be used in the formulae
Function get_number_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
Dim number_array(1 To 50) As Long
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
For k = 1 To j - i
number_array(i + k - 1) = j - i
Next k
i = j - 1
Else
number_array(i) = 0
End If
Next
get_number_array = number_array
End Function
You have to use it same as the previous one, but it will return excel array.
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
You can test this code
Function get_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
array_value = "{"
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
array_value = array_value & WorksheetFunction.Rept(j - i & ", ", j - i)
i = j - 1
Else
array_value = array_value & "0, "
End If
Next
array_value = Left(array_value, Len(array_value) - 2) & "}"
get_array = array_value
End Function
You can use it as below
EDIT
find below another function to return an array of values that can be used in the formulae
Function get_number_array(r As Range, match_chr As String)
Dim check_val
Dim array_value
Dim number_array(1 To 50) As Long
For i = 1 To r.Count
check_value = r.Item(i)
If (check_value = match_chr) Then
j = i + 1
Do While (j <= r.Count) And (check_value = r.Item(j))
j = j + 1
Loop
For k = 1 To j - i
number_array(i + k - 1) = j - i
Next k
i = j - 1
Else
number_array(i) = 0
End If
Next
get_number_array = number_array
End Function
You have to use it same as the previous one, but it will return excel array.
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
edited Nov 21 '18 at 5:20
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
answered Nov 20 '18 at 14:25
usmanhaqusmanhaq
1,123128
1,123128
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
|
show 5 more comments
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
This was really nice vba-code, but.. i cant use this as array, since there are the spaces between every character. i tried to remove them from the code and for some reason the last digit leaves out from the solution. eny ideas? edit: i figured this out, but another problem came up, this soluion is in quotemarks "" and i cant use it in functions that need arrays. for excample sumprodcuts..
– Mikael
Nov 21 '18 at 4:43
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
What is your exact goal, I can try to update the code to achieve that. Currently this function is returning the array as a string as per your original question, you can not use it in sum, average type functions.
– usmanhaq
Nov 21 '18 at 4:58
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
I have added another function to return number array that can be used in the excel formulae
– usmanhaq
Nov 21 '18 at 5:21
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
The excact goal is to calculate the result of a board game called kivi,
– Mikael
Nov 21 '18 at 5:38
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
goal of the function means what you would do with the returned array, but anyhow i have edited the answer and you may have already seen, you can use the returned array in the excel formulae.
– usmanhaq
Nov 21 '18 at 5:47
|
show 5 more comments
For a formula solution, I can only come up with one for the special case where you have just X's and zeroes (example 1) so far:
=SUM(IF(A1:G1<>"X",0,INDEX(FREQUENCY(IF(A1:G1="X",COLUMN(A1:G1)),IF(A1:G1<>"X",COLUMN(A1:G1))),N(IF({1},SUBTOTAL(2,OFFSET(A1,0,0,1,COLUMN(A1:G1)))))+1,1)))
entered as an array formula using CtrlShiftEnter
I have wrapped it in a SUM function to demonstrate that it generates an array which can be passed to another function (result: 13) or it can be array-entered across several cells:
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
add a comment |
For a formula solution, I can only come up with one for the special case where you have just X's and zeroes (example 1) so far:
=SUM(IF(A1:G1<>"X",0,INDEX(FREQUENCY(IF(A1:G1="X",COLUMN(A1:G1)),IF(A1:G1<>"X",COLUMN(A1:G1))),N(IF({1},SUBTOTAL(2,OFFSET(A1,0,0,1,COLUMN(A1:G1)))))+1,1)))
entered as an array formula using CtrlShiftEnter
I have wrapped it in a SUM function to demonstrate that it generates an array which can be passed to another function (result: 13) or it can be array-entered across several cells:
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
add a comment |
For a formula solution, I can only come up with one for the special case where you have just X's and zeroes (example 1) so far:
=SUM(IF(A1:G1<>"X",0,INDEX(FREQUENCY(IF(A1:G1="X",COLUMN(A1:G1)),IF(A1:G1<>"X",COLUMN(A1:G1))),N(IF({1},SUBTOTAL(2,OFFSET(A1,0,0,1,COLUMN(A1:G1)))))+1,1)))
entered as an array formula using CtrlShiftEnter
I have wrapped it in a SUM function to demonstrate that it generates an array which can be passed to another function (result: 13) or it can be array-entered across several cells:
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
For a formula solution, I can only come up with one for the special case where you have just X's and zeroes (example 1) so far:
=SUM(IF(A1:G1<>"X",0,INDEX(FREQUENCY(IF(A1:G1="X",COLUMN(A1:G1)),IF(A1:G1<>"X",COLUMN(A1:G1))),N(IF({1},SUBTOTAL(2,OFFSET(A1,0,0,1,COLUMN(A1:G1)))))+1,1)))
entered as an array formula using CtrlShiftEnter
I have wrapped it in a SUM function to demonstrate that it generates an array which can be passed to another function (result: 13) or it can be array-entered across several cells:
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
edited Nov 20 '18 at 20:14
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
answered Nov 20 '18 at 19:26
Tom SharpeTom Sharpe
12.3k31224
12.3k31224
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
add a comment |
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
I am becoming fan of yours, your answers really fascinate me, each time I see your answer I always get something new to learn. Still I don’t know how to use frequency and if({1} was new to me. I have googled frequency few times but I believe I have to search more.
– usmanhaq
Nov 21 '18 at 5:31
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
Thank you! Although I didn't manage a general answer to this question, it is quite a good case study of various techniques (which I have learnt from masters like @Barry Houdini) - I will add some references later.
– Tom Sharpe
Nov 21 '18 at 9:40
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53386246%2farray-from-continuous-data%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
Welcome to StackOverflow! What exactly did you try, and what was the result? Please go through the guide on How to ask a good question? so that others may be able to provide you with better assistance.
– Uzair A.
Nov 20 '18 at 4:48
So you are only looking for X, not for any other letters? Do you have room for helper columns/rows, or is it important that the solution is a single formula?
– Peter K.
Nov 20 '18 at 12:22
i have figured how to do it with multiple formulas and with multiple sheets, but i wanted to simplify this for the user and i just couldnt wrap my head around this.
– Mikael
Nov 21 '18 at 4:40