Excelgreenhand
Board Regular
- Joined
- Oct 18, 2009
- Messages
- 101
I have a situation like this with two columns
colA colB
100011 AA
100012 AA
100013 AA
100014 AA
1000XX AA
200011 AA
200012 AA
200013 AA
200014 AA
2000XX AA
300011 AA
300012 AA
300013 AA
300014 AA
3000XX AA
100011 BB
100012 BB
100013 BB
100014 BB
1000XX BB
200011 BB
200012 BB
200013 BB
200014 BB
2000XX BB
300011 BB
300012 BB
300013 BB
300014 BB
3000XX BB
The result to be obtained is I would like to grab under "colA" is the first 4 digits, if duplicate (unique only) then ignore, therefore
under AA for 1000 (1 only), 2000 (1 only) and 3000 (1 only) and
under BB for 1000 (1 only), 2000 (1 only) and 3000 (1 only).
Should it be done with COUNTIF/SUMIF for U N I Q U E values !?.
The above is sales data with the "colA" being the product code and "colB" the product classification.
If a "colC" be involved with the salesman info, like
colA colB colC
100011 AA SalesA
100012 AA SalesA
100013 AA SalesA
100014 AA SalesB
1000XX AA SalesC
xx
x
xx
It would be possible for one more criteria, therefore to sum up under
SalesA has under classification "AA" has how many sets of products sold (1st four digits of 1000XX is regarded as one set of product)
Any ideas.
Thanks.
colA colB
100011 AA
100012 AA
100013 AA
100014 AA
1000XX AA
200011 AA
200012 AA
200013 AA
200014 AA
2000XX AA
300011 AA
300012 AA
300013 AA
300014 AA
3000XX AA
100011 BB
100012 BB
100013 BB
100014 BB
1000XX BB
200011 BB
200012 BB
200013 BB
200014 BB
2000XX BB
300011 BB
300012 BB
300013 BB
300014 BB
3000XX BB
The result to be obtained is I would like to grab under "colA" is the first 4 digits, if duplicate (unique only) then ignore, therefore
under AA for 1000 (1 only), 2000 (1 only) and 3000 (1 only) and
under BB for 1000 (1 only), 2000 (1 only) and 3000 (1 only).
Should it be done with COUNTIF/SUMIF for U N I Q U E values !?.
The above is sales data with the "colA" being the product code and "colB" the product classification.
If a "colC" be involved with the salesman info, like
colA colB colC
100011 AA SalesA
100012 AA SalesA
100013 AA SalesA
100014 AA SalesB
1000XX AA SalesC
xx
x
xx
It would be possible for one more criteria, therefore to sum up under
SalesA has under classification "AA" has how many sets of products sold (1st four digits of 1000XX is regarded as one set of product)
Any ideas.
Thanks.