jimmysumpter
New Member
- Joined
- Jan 16, 2014
- Messages
- 11
Hi guys,
My excel skills are somewhat limited (compared to many on here) so I hope the following makes sense!
I have a list of group ID codes, which contain 1 or more product codes within them.
Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below
[TABLE="width: 500"]
<tbody>[TR]
[TD]Group ID[/TD]
[TD]Product Code[/TD]
[TD]Alternative[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240027[/TD]
[TD]160380[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240028[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240029[/TD]
[TD]160380[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240030[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.
So for the example above, group ID 56381 would have 50% codes with alternatives.
By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)
However some groups contain over 100 codes and the spreadsheet is 40K rows!
Any help is much appreciated, again apologies if it doesn't make sense!
Jimmy
My excel skills are somewhat limited (compared to many on here) so I hope the following makes sense!
I have a list of group ID codes, which contain 1 or more product codes within them.
Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below
[TABLE="width: 500"]
<tbody>[TR]
[TD]Group ID[/TD]
[TD]Product Code[/TD]
[TD]Alternative[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240027[/TD]
[TD]160380[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240028[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240029[/TD]
[TD]160380[/TD]
[/TR]
[TR]
[TD]56381[/TD]
[TD]240030[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.
So for the example above, group ID 56381 would have 50% codes with alternatives.
By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)
However some groups contain over 100 codes and the spreadsheet is 40K rows!
Any help is much appreciated, again apologies if it doesn't make sense!
Jimmy