Combining information VB script

[murrayjl03]

Hi All,

I am looking for a VB script which will help me combine information which I currently do manually.

I pasted the table which i currently have below and is in sheet 1.

I am looking for the following:

1. Sheet 2 Col A should show all the types of fruit from Sheet 1 Col A without the duplicates e.g. only 1 instance of 'Apple'
2. Sheet 2 Col B should have the sum of all the same types of fruit from Sheet 1 Col E E.g. Apple would have 5 (1+4)
3. Sheet 2 Col C should have the sum of all the same types of fruit from Sheet 1 Col F E.g. Apple would have 2 (1+1)
4. Sheet 2 Col E should have the sum of all the same types of fruit from Sheet 1 Col H E.g. Apple would have 3 (0+3)
5. Sheet 2 Col G should have the average of all the same types of fruit from Sheet 1 Col J E.g. Apple would have 4 ((4x2)/2)

Any help would be massively appreciated and save a lot of time!


[TABLE="class: grid, width: 640"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 384, colspan: 6"]Priority 1[/TD]
[/TR]
[TR]
[TD="align: left"]Fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Count[/TD]
[TD="align: left"]Met SLA[/TD]
[TD="align: left"]SLA 1[/TD]
[TD="align: left"]Met SLA[/TD]
[TD="align: left"]SLA 2[/TD]
[TD="align: left"]MTTR[/TD]
[/TR]
[TR]
[TD="align: left"]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: left"]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]Bannana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: left"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: left"]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: left"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: left"]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: left"]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

re 5. apple is (4+2) /2 =3 or am I missing something

 

[murrayjl03]

No you are correct that was my error! I will update the question now.

 

[oldbrewer]

[TABLE="width: 1253"]
<colgroup><col span="2"><col><col><col><col span="6"><col><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 13"]Sheet 2 Col A should show all the types of fruit from Sheet 1 Col A without the duplicates e.g. only 1 instance of 'Apple'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]Sheet 2 Col B should have the sum of all the same types of fruit from Sheet 1 Col E E.g. Apple would have 5 (1+4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]Sheet 2 Col C should have the sum of all the same types of fruit from Sheet 1 Col F E.g. Apple would have 2 (1+1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]Sheet 2 Col E should have the sum of all the same types of fruit from Sheet 1 Col H E.g. Apple would have 3 (0+3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]Sheet 2 Col G should have the average of all the same types of fruit from Sheet 1 Col J E.g. Apple would have 4 ((4x2)/2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]Any help would be massively appreciated and save a lot of time![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Priority 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]DUMMY1[/TD]
[TD]DUMMY2[/TD]
[TD]DUMMY3[/TD]
[TD]Count[/TD]
[TD]Met SLA[/TD]
[TD]SLA 1[/TD]
[TD]Met SLA[/TD]
[TD]SLA 2[/TD]
[TD]MTTR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bannana[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]14.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peach[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bannana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]apple formulas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$13:$A$21=$N11)*($E$13:$E$21))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]pivot table gives unique list of fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$13:$A$21=$N11)*($F$13:$F$21))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Count of Fruit[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]easy to read this automatically[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$13:$A$21=$N11)*($H$13:$H$21))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]by checking that the fruit column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bannana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]is not blank or Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$13:$A$21=$N11)*($J$13:$J$21))/2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Peach[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]