Solved
Using VBA to consolidate a Excel worksheet
Posted on 2000-02-25
I have a worksheet with a few hundred data groups and I like to consolidate the
data to another worksheet. Each data group starts with a title row looks like:
*TITLE (DATA GROUP NUMBER ONE)
Each group has 10-20 data lines. Each line represents a material layer and has 4
data in column A, B, C, and D, except the first line that has 5 data. The data
in column A is thickness of the layer and B, C, D are the material
specifications. The fifth data of first data line is the total thickness of the
layers in the group. There are 1-4 blank lines between the data groups. The data
looks like these:
Col A Col B Col C Col D Col F
45.02 3 P_1 33 228.26 <= first data line
27.32 3 P_1 33 col F = Sum of Col A
15.02 4 P_1 45 of the data group
6.47 4 P_1 45
7.47 3 P_1 33
28.34 3 P_1 21
98.62 3 P_2 45
I like to using VBA to rewrite the data into another worksheet. The title row
will be copied and blank lines between data groups will be removed. If the
adjacent layers have same specifications (Column B,C,D), the thickness will be
added up and become a single layer. Therefore, the number of data rows will be
reduced, but the order of the layers and total thickness of layers will remain
the same.
*TITLE (DATA GROUP NUMBER ONE)
72.34 3 P_1 33 228.26
21.49 4 P_1 45
7.47 3 P_1 33
28.34 3 P_1 21
98.62 3 P_2 45
*TITLE (DATA GROUP NUMBER TWO)
35.61 3 P_1 33 196.26
...
Can any body help?
Thanks in advance.
Best Regards.
TKC