Complex sum!

[gledister]

Imagine we have these 2 cells like this:

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345><COLGROUP><COL style="WIDTH: 259pt; mso-width-source: userset; mso-width-alt: 12617" width=345><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 259pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=345>1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)</TD></TR></TBODY></TABLE>
And at the end we need to have:
1 28
3 29
3 30
6 31
7 32
7 33
4 34
5 36
2 38

can someone give me any idea?!

Thanks in advance!

 

[fhoest]

Hi, i'msorry i don't understand what you want exactly i'm french and i learn english so if you 've a folder
post here so i can help you (maybe)

 

[gledister]

So imagine we have this two raw unformatted cells:

<TABLE style="WIDTH: 281pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=374><COLGROUP><COL style="WIDTH: 281pt; mso-width-source: userset; mso-width-alt: 13677" width=374><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 281pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=374>1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)</TD></TR></TBODY></TABLE>

And we see that we have in the first row that we have 1 of 29, 1 of 30, 4 of 31, 5 of 32, 5 of 33, 2 of 34, 2 of 36 and 1 of 38.

You need to read the second row the same way.

What we wanna do is sum the values of this two rows exactly as they belong to....

so at the end we need to have 1 of 28, 1+2=3 of 29, 1+2=3 of 30, 5+2=7 of 32, 5+2=7 of 33, 2+2=4 of 34, 2+3=5 of 36 and 1+1=2 of 38...

am I clear now?

Thanks in advance!

 

[fhoest]

Yes i think i understand now
so i work for you find a solution and i send you a response
if i find


in cell (a1)= 1 (29) , cell (b1) =1(30) , cell (c1)= 4 (31)
in cell (a2)= 1(28) , cell (b2) = 2 (29) , cell (c2) = 2 (30)
this you want explain me
and the result the number of (29) found or summ, (30) found or summ etc...

best regards.

 

[gledister]

sum...thanks!

 

[Peter_SSs]

I think you will need a macro to do that.

Try this in a copy of your workbook.

It assumes that the data is in column A starting at cell A2. It also assumes all values are whole numbers. It produces results in columns C:D as shown below.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ComplexSum()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Bits<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, v <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Data = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value<br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>        .CompareMode = vbTextCompare<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Data, 1)<br>            Bits = Split(Replace(Replace(Data(i, 1), "(", ""), ")", ""))<br>            <SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Bits) - 1 <SPAN style="color:#00007F">Step</SPAN> 2<br>                n = Bits(j): v = Bits(j + 1)<br>                <SPAN style="color:#00007F">If</SPAN> .Exists(v) <SPAN style="color:#00007F">Then</SPAN><br>                    .Item(v) = .Item(v) + n<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    .Add v, n<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> j<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        Range("C1:D1").Value = Array("Sum", "Value")<br>        Range("C2").Resize(.Count, 2).Value = Application.Transpose(Array(.Items, .keys))<br>        Range("C2").Resize(.Count, 2).Sort Key1:=Range("D2"), Order1:=xlAscending<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Excel Workbook
	A	B	C	D
1			Sum	Value
2	1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)		1	28
3	1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)		3	29
4			3	30
5			6	31
6			7	32
7			7	33
8			4	34
9			5	36
10			2	38
11
Sum

 

[mancemonster]

Assuming you mean that...

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 259pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17 width=345>1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17>1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 259pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17 width=345>1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17>1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345 x:str><COLGROUP><COL style="WIDTH: 259pt; mso-width-source: userset; mso-width-alt: 12617" width=345><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 259pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #666699 1pt solid" id=td_post_2663770 class=xl25 height=17 width=345>1 (29) 1 (30) 4 (31) 5 (32) 5 (33) 2 (34) 2 (36) 1 (38)
</TD></TR><TR style="HEIGHT: 13.5pt" title="Post 2663757" height=18><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #666699 1pt solid" class=xl24 height=18>1 (28) 2 (29) 2 (30) 2 (31) 2 (32) 2 (33) 2 (34) 3 (36) 1 (38)
</TD></TR></TBODY></TABLE>

Are in cells you will need to do the text to columns function to split the data..

Data-Text to Columns (Fixed width should split the data well enough).

The () will force the values -tve so if you do a find replace - with blank that will put everything +tive.

If your data is on rows
3&4...

Create a column with your 28,29,30 etc...

Then try

=INDEX($3:$3,1,MATCH(A7,$3:$3,0)-1)+INDEX($4:$4,1,MATCH(A7,$4:$4,0)-1)

This should pull through the values you require.

Regards

Roger

 

[fhoest]

Hi all,
i have this:

Sub test()
Dim c, d As Range
Dim x1, x2, y1, y2 As Variant
Dim n1, n2, n3 ,n4 As Integer
For Each d In Range("a1:c1")
x1 = Split(d.Text, "(")
y1 = Split(x1(1), ")")
n1 = x1(0)
For Each c In Range("a2:c2")
x2 = Split(c.Text, "(")
y2 = Split(x2(1), ")")
n2 = x2(0)
If y1(0) = y2(0) Then
n4 = CDbl(n1) + CDbl(n2)
Sheets(1).Cells(8, n3 + 1).Value = n4 & " * " & y2(0)
n3 = n3 + 1
End If
Next
Next
End Sub

Fred.

 

[fhoest]

here is for you.
http://www.cijoint.fr/cjlink.php?file=cj201103/cijLzOANvj.xlsm
regards
fred

 

[gledister]

Thanks Peter. It worked perfectly!