Hello i'm trying to group the CODE values and get a total of the count based on certain instances. i cant use a pivot table when doing this so VBA code is preferred
Then i would like to do the same but for "Coconut" only in the FRUIT2 Column using certain instances
Then i would like to know the only the "Apple" only in the FRUIT1 Column using certain instances.
and so on and so on...for only the Fruit1 fields.
All of this data is on an open workbook, would like move it to another.
Here is my current Code, im just not should what i should do next for sorting.
Here is the Current data i have.
NOTE: Grey and "Blank" should be combined with the white value.
<tbody>
</tbody>
Here is the final result im kinda looking for with a total row added below with the Z column blank then starting a new group "COCONUT"
<tbody>
</tbody>
Any Help would be greatly appreciated, ive been trying to wrap my head around how to logically think of a way to sort this out using a macro.
Thanks
Then i would like to do the same but for "Coconut" only in the FRUIT2 Column using certain instances
Then i would like to know the only the "Apple" only in the FRUIT1 Column using certain instances.
and so on and so on...for only the Fruit1 fields.
All of this data is on an open workbook, would like move it to another.
Here is my current Code, im just not should what i should do next for sorting.
Code:
Sub CopyColumnToWorkbook()Dim sourceColumn As Range, targetcolumn As Range
Set sourceColumn = Workbooks("test1.csv").Worksheets(1).Columns("A")
Set targetcolumn = Workbooks("test2.xlsm").Worksheets(1).Columns("A")
sourceColumn.Copy Destination:=targetcolumn
ActiveSheet.Range("A1:A10000").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
Here is the Current data i have.
NOTE: Grey and "Blank" should be combined with the white value.
A | B | C | D | E | F |
CODE | FRUIT1 | FRUIT2 | YES/NO | COLOR | COUNT |
1 | APPLE | COCONUT | YES | RED | 1 |
1 | ORANGE | COCONUT | YES | GREEN | 1 |
1 | BANANA | COCONUT | YES | WHITE | 1 |
1 | STRAWBERRY | COCONUT | YES | GREY | 1 |
1 | GRAPE | COCONUT | YES | 1 | |
1 | CHERRY | COCONUT | YES | RED | 1 |
1 | PINEAPPLE | COCONUT | YES | GREEN | 1 |
1 | PEAR | COCONUT | YES | WHITE | 1 |
1 | CORN | COCONUT | YES | GREY | 1 |
1 | CORN | COCONUT | YES | 1 | |
1 | CORN | APPLE | NO | WHITE | 1 |
1 | CORN | ORANGE | NO | WHITE | 1 |
1 | CORN | BANANA | NO | WHITE | 1 |
1 | CORN | STRAWBERRY | NO | WHITE | 1 |
1 | CORN | GRAPE | NO | WHITE | 1 |
2 | APPLE | COCONUT | YES | RED | 1 |
2 | ORANGE | COCONUT | YES | GREEN | 1 |
2 | BANANA | COCONUT | YES | WHITE | 1 |
2 | STRAWBERRY | COCONUT | YES | GREY | 1 |
2 | GRAPE | COCONUT | YES | 1 | |
2 | CHERRY | COCONUT | YES | RED | 1 |
2 | PINEAPPLE | COCONUT | YES | GREEN | 1 |
2 | PEAR | COCONUT | YES | WHITE | 1 |
2 | CORN | COCONUT | YES | GREY | 1 |
2 | CORN | COCONUT | YES | 1 | |
2 | CORN | APPLE | NO | WHITE | 1 |
2 | CORN | ORANGE | NO | WHITE | 1 |
2 | CORN | BANANA | NO | WHITE | 1 |
2 | CORN | STRAWBERRY | NO | WHITE | 1 |
2 | CORN | GRAPE | NO | WHITE | 1 |
<tbody>
</tbody>
Here is the final result im kinda looking for with a total row added below with the Z column blank then starting a new group "COCONUT"
A | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||||||||||
CODE | TOTAL |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||||||||||
1 | 15 | 10 | 5 | 2 | 2 | 11 | 2 | 0 | 2 | 0 | 6 | 5 | ||||||||||||
2 | 15 | 10 | 5 | 2 | 2 | 11 | 2 | 0 | 2 | 0 | 6 | 5 | ||||||||||||
TOTAL | 30 | 20 | 10 | 4 | 4 | 22 | 4 | 0 | 4 | 0 | 12 | 10 |
<tbody>
</tbody>
Any Help would be greatly appreciated, ive been trying to wrap my head around how to logically think of a way to sort this out using a macro.
Thanks