Special sum of texts
Hi,
Thanks for answering, what this table contains data:
Column Data1 is components,
Data2 semi products named aaxx
Data3 and Data4 contain semi products contain name bbxx and cccxxx
For simplest cases:
If there is only bbxx and in any other row with bbxx there is nothing more
in column Data2, Data3 and Data4 the sum is bbxx
Data1 Data2 Data3 Data4 Correct_SUM
1 bb01 bb01
2 bb01 bb01
If there is only cccxx and in any other row with cccxx there is nothing more
in column Data2, Data3 and Data4 the sum is bbxx
3 ccc02 ccc02
4 ccc02 ccc02
For aax in column Data2 and nothing more in data3 and Data4 the sum is aax
19 aa4 aa4
20 aa4 aa4
Of there is cccxx and in one with rows with cccxx is cccyy the sum for all
rows with cccxx and cccyy is cccxx+cccyy (I add "*" to show row with
connections)
5 ccc06 ccc06+ccc07
6* ccc06 ccc07 ccc06+ccc07
7 ccc07 ccc06+ccc07
8 ccc07 ccc06+ccc07
In Case when data3 and data4 contani cccxx and in one row with cccxx is bbxx
then sum is only mane cccxx
9 bb02 ccc01
10* ccc01 bb02 ccc01
11 ccc01 ccc01
Similar in case with aaxx in Data2 and cccxxin column Data3 or Data4
12 ccc03 ccc03
13* aa1 ccc03 ccc03
14 aa1 ccc03
And one with complicated situation column Data3 or data4 contain cccxx and
cccyy and in the same row in column Data2 contain aaxx for both cccxx and
cccyy
15 aa2 ccc04 ccc04+ccc05
16 aa3 ccc05 ccc04+ccc05
17 aa2 ccc05 ccc04+ccc05
18 aa3 ccc05 ccc04+ccc05
Another situation ccc06 join with ccc07 and ccc08 with ccc09, but i the same
time ccc06 connect with aa5 which is connected with ccc08, and second
ccc07-aa7-ccc09
22 aa5 ccc06 ccc06+ccc07+ccc08+ccc09
23 aa6 ccc06 ccc07 ccc06+ccc07+ccc08+ccc09
24 aa7 ccc07 ccc06+ccc07+ccc08+ccc09
25 aa8 ccc06 ccc06+ccc07+ccc08+ccc09
26 aa5 ccc08 ccc06+ccc07+ccc08+ccc09
27 aa6 ccc08 ccc09 ccc06+ccc07+ccc08+ccc09
28 aa7 ccc09 ccc06+ccc07+ccc08+ccc09
29 aa8 ccc08 ccc06+ccc07+ccc08+ccc09
So only when bbxx or aaxx is alone then is sum there is bbxx or aaxx, but
when this manes is with name cccxx only cccxx is in sum. Connections between
cccxx and cccyy make sum cccxx+cccyy.
Now I do it manually but for long sum of cccxx (once I got 18 cccxx is SUM -
similar to last example with ccc06+ccc07+ccc08+ccc09) I could make mistake.
€˛Joel€¯ pisze:
I don't see the pattern in your data. I would need a much better description
of you requirements before I could accurately complete this task.
"Bartosz" wrote:
Thanks for try, but it takie only one line to find konections, but the same
name differend rows is thesame thing, so Your macro took only information
from one row, but this macro have to look for data in all rows.
I try ta add ma table onece again with infromation which I got after Your
macro run.
Your_macro Correct_SUM
1 bb01 bb01 bb01
2 bb01 bb01 bb01
3 ccc02 ccc02 ccc02
4 ccc02 ccc02
5 ccc06 ccc06 ccc06+ccc07
6 ccc06 ccc07 ccc06 ccc06+ccc07
7 ccc07 ccc07 ccc06+ccc07
8 ccc07 ccc06+ccc07
9 bb02 bb02 ccc01
10 ccc01 bb02 ccc01 ccc02
11 cc01 ccc03
12 ccc03 ccc03 ccc03
13 aa1 ccc03 ccc03
14 aa1 aa1 ccc03
15 aa2 ccc04 ccc04 ccc04+ccc05
16 aa3 ccc05 ccc04+ccc05
17 aa2 ccc05 ccc05 ccc04+ccc05
18 aa3 ccc05 ccc04+ccc05
19 aa4 aa4 aa4
20 aa4 aa4 aa4
€˛Joel€¯ pisze:
Try this
Sub SumData()
'Get Last row
LastRow = 0
For ColCount = 2 To 4
LRow = Cells(Rows.Count, ColCount).End(xlUp).Row
If LRow LastRow Then
LastRow = LRow
End If
Next ColCount
For RowCount = 1 To LastRow
If Range("C" & RowCount) = "" And _
Range("D" & RowCount) = "" Then
If UCase(Left(Range("B" & RowCount), 2)) = "AA" Then
Range("E" & RowCount) = Range("B" & RowCount)
End If
Else
If UCase(Left(Range("C" & RowCount), 3)) = "CCC" Or _
UCase(Left(Range("D" & RowCount), 3)) = "CCC" Then
If UCase(Left(Range("C" & RowCount), 3)) = "CCC" Then
Range("E" & RowCount) = Range("C" & RowCount)
End If
If UCase(Left(Range("D" & RowCount), 3)) = "CC" Then
If Range("E" & RowCount) < "" Then
Range("E" & RowCount) = Range("E" & RowCount) & "+"
End If
Range("E" & RowCount) = _
Range("E" & RowCount) & Range("D" & RowCount)
End If
Else
If UCase(Left(Range("C" & RowCount), 2)) = "BB" Or _
UCase(Left(Range("D" & RowCount), 2)) = "BB" Then
If UCase(Left(Range("C" & RowCount), 2)) = "BB" Then
Range("E" & RowCount) = Range("C" & RowCount)
End If
If UCase(Left(Range("D" & RowCount), 2)) = "BB" Then
If Range("E" & RowCount) < "" Then
Range("E" & RowCount) = Range("E" & RowCount) & "+"
End If
Range("E" & RowCount) = _
Range("E" & RowCount) & Range("D" & RowCount)
End If
End If
End If
End If
Next RowCount
End Sub
|