CSE Array question

HWL

HWL

Active Member
Joined
Dec 1, 2009
Messages
462
Hi there, supposed I have in col A, a bunch of "X" but not in sequence -- so perhaps an "X" in A2, then in A8, then in A12.

In col B I have some data too.

What I want is to be able to in col C, have a formula I can drag down that will look at col A an if there is an "X", bring the data from col B into col C, but in sequence. So column C would have data in C1-C5 assuming there are 5 "X" in col A.

I know this requires a ctrl+shift+enter array but not sure how to make it work.
Thanks
 
Sort by date Sort by votes
sorry for disturbing you, I posted a thread that someone said I have to get advice from VBA user's. He said that I need "CSE array" or something like that.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I've already tried this formula from him
=(LEN(LEFT(A2;SEARCH("x";A2)))-LEN(SUBSTITUTE(LEFT(A2;SEARCH("x";A2));".";""))+1)*SUM(IF(MID("."&MID(A2;SEARCH("x";A2)+1;20);COLUMN(1:1);1)=".";--(0&MID(MID(A2;SEARCH("x";A2)+1;20);COLUMN(1:1);FIND(".";MID(SUBSTITUTE(A2;"/";".");SEARCH("x";A2)+1;20);COLUMN(1:1))-COLUMN(1:1)))))

but the result is value

I'm using Excel 2007 and Windows XP

I really need some help, please. Thank you so much

Best Regards

Jasa
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hello Jasa,

Input in A1
20.10.10x5.15.25/

Paste into B1

Code: 
=(LEN(LEFT(A1,SEARCH("x",A1)))-LEN(SUBSTITUTE(LEFT(A1,SEARCH("x",A1)),".",""))+1)*SUM(IF(MID("."&MID(A1,SEARCH("x",A1)+1,20),COLUMN(1:1),1)=".",--(0&MID(MID(A1,SEARCH("x",A1)+1,20),COLUMN(1:1),FIND(".",MID(SUBSTITUTE(A1,"/","."),SEARCH("x",A1)+1,20),COLUMN(1:1))-COLUMN(1:1)))))
click cell B1 and press all 3 of these keys; Control, Shift, and Enter
(this is what is meant by the CSE)

The result will be the expected 135.

Hope that helps.



Jasa P said:
sorry for disturbing you, I posted a thread that someone said I have to get advice from VBA user's. He said that I need "CSE array" or something like that.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I've already tried this formula from him
=(LEN(LEFT(A2;SEARCH("x";A2)))-LEN(SUBSTITUTE(LEFT(A2;SEARCH("x";A2));".";""))+1)*SUM(IF(MID("."&MID(A2;SEARCH("x";A2)+1;20);COLUMN(1:1);1)=".";--(0&MID(MID(A2;SEARCH("x";A2)+1;20);COLUMN(1:1);FIND(".";MID(SUBSTITUTE(A2;"/";".");SEARCH("x";A2)+1;20);COLUMN(1:1))-COLUMN(1:1)))))

but the result is value

I'm using Excel 2007 and Windows XP

I really need some help, please. Thank you so much

Best Regards

Jasa
Click to expand...
 
Upvote 0
HWL said:
Hello Jasa,

Input in A1
20.10.10x5.15.25/

Paste into B1

Code: 
=(LEN(LEFT(A1,SEARCH("x",A1)))-LEN(SUBSTITUTE(LEFT(A1,SEARCH("x",A1)),".",""))+1)*SUM(IF(MID("."&MID(A1,SEARCH("x",A1)+1,20),COLUMN(1:1),1)=".",--(0&MID(MID(A1,SEARCH("x",A1)+1,20),COLUMN(1:1),FIND(".",MID(SUBSTITUTE(A1,"/","."),SEARCH("x",A1)+1,20),COLUMN(1:1))-COLUMN(1:1)))))
click cell B1 and press all 3 of these keys; Control, Shift, and Enter
(this is what is meant by the CSE)

The result will be the expected 135.

Hope that helps.
Click to expand...

Thank u so much for helping me. It works. :) Someone wrote about VBA code for this. But I don't really understand. can u take a look at this one, please. Thank u so much

<table border="0" cellpadding="0" cellspacing="0" width="383"><colgroup><col style="mso-width-source:userset;mso-width-alt:14006;width:287pt" width="383"> </colgroup><tbody><tr style="height:16.5pt" height="22"> <td class="xl65" style="height:16.5pt;width:287pt" width="383" height="22">Function funnySum(vStr As String) As Long</td> </tr> <tr style="height:16.5pt" height="22"> <td class="xl65" style="height:16.5pt" height="22">Dim bArr() As Byte, conCnt As Long</td> </tr> <tr style="height:16.5pt" height="22"> <td class="xl65" style="height:16.5pt" height="22">Dim dotCnt As Long, isLeft As Boolean</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">If vStr = vbNullString Then Exit Function</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">bArr = Left(vStr, InStr(1, vStr, "/") - 1)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">For i = UBound(bArr) - 1 To 0 Step -2</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> If isLeft Then</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> If bArr(i) < 47 Then dotCnt = dotCnt + 1</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> Else</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> If bArr(i) > 47 Then</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> If bArr(i) < 58 Then</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> funnySum = funnySum + (bArr(i) - 47) * 10 ^ conCnt</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> conCnt = conCnt + 1</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> ElseIf bArr(i) = 120 Then</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> isLeft = True</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> End If</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> Else</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> conCnt = 0</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> End If</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21"> End If</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">Next</td> </tr> </tbody></table>
Click to expand...
<table border="0" cellpadding="0" cellspacing="0" width="383"><colgroup><col style="mso-width-source:userset;mso-width-alt:14006;width:287pt" width="383"> </colgroup><tbody><tr style="height:16.5pt" height="22"> <td class="xl63" style="height:16.5pt;width:287pt" width="383" height="22">
</td> </tr> <tr style="height:16.5pt" height="22"> <td class="xl63" style="height:16.5pt" height="22">
</td> </tr> <tr style="height:16.5pt" height="22"> <td class="xl63" style="height:16.5pt" height="22">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt" height="21">
</td> </tr> </tbody></table>
 
Upvote 0
Hi, sorry for disturbing u again. But, can I use this formula for more complicated combination? For example:

20.10.10x5.15.25/20.10.10x5.15/20x5.15.25/20.10x5.15/20.10x5.15.25/
The result should be 370 (45*3+20*3+45*1+20*2+45*2)

10x5.55.15/10.50x5.55.15/10.50.50x5.15/50x5.55/10.50x5.55.15/10.50x5.55/
The result should be 615 (75*1+75*2+20*3+60*1+75*2+60*2)

'Cause I've tried use the array formula, but the result just take the first combination before "/"

My goal are:
1. The result for combination before "x" should be the amount of dots (.) +1
2. The result for combination between "x" and "/" should be the sum of the numbers
3. The final result should be the multiplied of 1 and 2

Can you help me to resolve this one? thank you so much. I put the image too, thank u

6780010088
http://www.flickr.com/photos/77270117@N02/6780010088/

Any help would be great
and I really appreciate your help, thank u so much

Best Regards

Jasa
 
Upvote 0
Jasa P said:
Hi, sorry for disturbing u again. But, can I use this formula for more complicated combination? For example:

20.10.10x5.15.25/20.10.10x5.15/20x5.15.25/20.10x5.15/20.10x5.15.25/
The result should be 370 (45*3+20*3+45*1+20*2+45*2)

10x5.55.15/10.50x5.55.15/10.50.50x5.15/50x5.55/10.50x5.55.15/10.50x5.55/
The result should be 615 (75*1+75*2+20*3+60*1+75*2+60*2)

'Cause I've tried use the array formula, but the result just take the first combination before "/"

My goal are:
1. The result for combination before "x" should be the amount of dots (.) +1
2. The result for combination between "x" and "/" should be the sum of the numbers
3. The final result should be the multiplied of 1 and 2

Can you help me to resolve this one? thank you so much. I put the image too, thank u

6780010088
http://www.flickr.com/photos/77270117@N02/6780010088/

Any help would be great
and I really appreciate your help, thank u so much

Best Regards

Jasa
Click to expand...

Since this isn't my code and when I first helped you I was just showing you how to use CSE, help me understand how this works so I can perhaps provide a more clear solution. Let's return to the first issue.

20.10.10x5.15.25/

Explain how this results in 135

What does a "." period signify? Addition? What does "x" signify? What does "/" signify? Step through it for me so I can put together a piece of code that will work with any combination. Thanks
 
Upvote 0
HWL said:
Since this isn't my code and when I first helped you I was just showing you how to use CSE, help me understand how this works so I can perhaps provide a more clear solution. Let's return to the first issue.

20.10.10x5.15.25/

Explain how this results in 135

What does a "." period signify? Addition? What does "x" signify? What does "/" signify? Step through it for me so I can put together a piece of code that will work with any combination. Thanks
Click to expand...

Sorry, I went back and looked at your original explanation. I understand now. Give me a few moments to figure out how to make it work on an infinite combination.
 
Upvote 0
HWL said:
Since this isn't my code and when I first helped you I was just showing you how to use CSE, help me understand how this works so I can perhaps provide a more clear solution. Let's return to the first issue.

20.10.10x5.15.25/

Explain how this results in 135

What does a "." period signify? Addition? What does "x" signify? What does "/" signify? Step through it for me so I can put together a piece of code that will work with any combination. Thanks
Click to expand...
Hi, thank u so much for replying my post. But, don't worry 'cause I already got the solutions of this problems. I really appreciate your help. Thank u :)
If u want to help me, I just posted a new thread here
HTML: 
http://www.mrexcel.com/forum/showthread.php?p=3058340&posted=1#post3058340
Thanks :)


Best regards,


Jasa
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Rotate an array index?
Replies
5
Views
95
Cubist
Cubist
G
  • Solved
Assign value to array element and use ARRAYTOTEXT function
Replies
1
Views
78
gifariz
G
G
  • Solved
Formula to make combination of elements given possible variables?
Replies
5
Views
324
felixstraube
F
B
  • Solved
Search vlookup more than 1 column
2 3
Replies
23
Views
553
DanteAmor
DanteAmor
F
  • Solved
TEXTSPLIT not working as expected inside of BYROW. Workaround?
2
Replies
11
Views
759
felixstraube
F

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

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