Hi,
I'm a long time reader, first time poster, so please forgive me if I haven't done this correctly.
I have an array in Excel, that was generated by the Frequency function. I'm wondering if it is possible to modify this array using Excel functions?
In particular, I either want to remove the last element, so {1;2;3;4} becomes {1;2;3} or add an element to the end, so {1,2,3} becomes {1,2,3,4}.
Perhaps if I describe my problem, someone could suggest an alternative solution;
I have two columns of data that are in a table, one contains a reference number (the same reference number could be duplicated several times in the column), the second a price against that reference. I want to sum the prices, but not include prices that are against a duplicated reference number. So for example if I have;
AAA 5
BBB 3
AAA 2
CCC 4
The result would be 12 (= 5+3+4).
I've managed to create an array corresponding to the reference number column that has a 1 if it is the first time the reference number has occured , or a zero if not. So on the above example I would have (1,1,0,1). However, this isn't exactly what I've got, as this array is created by the Frequency function, so has one more element than this. So I actually have (1,1,0,1,0). If I could remove the last element of this array, I could use SumProduct with the price array to get my answer. Or I could add a 0 to the end of the price array. Im using a Structure Reference (MyTable[Prices]) for the price array, as the length of this column changes.
I'd ideally like this to be done without VBA, but if it's not possible, a VBA solution would be better than no solution.
Many thanks,
Dan.
I'm a long time reader, first time poster, so please forgive me if I haven't done this correctly.
I have an array in Excel, that was generated by the Frequency function. I'm wondering if it is possible to modify this array using Excel functions?
In particular, I either want to remove the last element, so {1;2;3;4} becomes {1;2;3} or add an element to the end, so {1,2,3} becomes {1,2,3,4}.
Perhaps if I describe my problem, someone could suggest an alternative solution;
I have two columns of data that are in a table, one contains a reference number (the same reference number could be duplicated several times in the column), the second a price against that reference. I want to sum the prices, but not include prices that are against a duplicated reference number. So for example if I have;
AAA 5
BBB 3
AAA 2
CCC 4
The result would be 12 (= 5+3+4).
I've managed to create an array corresponding to the reference number column that has a 1 if it is the first time the reference number has occured , or a zero if not. So on the above example I would have (1,1,0,1). However, this isn't exactly what I've got, as this array is created by the Frequency function, so has one more element than this. So I actually have (1,1,0,1,0). If I could remove the last element of this array, I could use SumProduct with the price array to get my answer. Or I could add a 0 to the end of the price array. Im using a Structure Reference (MyTable[Prices]) for the price array, as the length of this column changes.
I'd ideally like this to be done without VBA, but if it's not possible, a VBA solution would be better than no solution.
Many thanks,
Dan.
