The data looks something like this:
wk2 wk3
1 1
2 1
2 3
2 3
2 4
4 4
I want to count the frequency each number appears in both rows, but count them separately, as such
num. wk2.. wk3
1...... 1.... 2..
2...... 4.... 0..
3...... 0.... 2..
4...... 1.... 2..
How do we do that on a single pivot table?
Thanks!
Last edited by puay; 07-29-2014 at 07:31 AM.
Maybe like this?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hmm alright, that works fine. Thank You Glenn.
What if the numbers are changed to words instead? And there are several columns and thousands of rows, with dozens of distinct words in each column. (Hypothetical scenario)
And I do not know in advance what words there are.
Given table:
wk2 wk3
as as
as as
ad as
ap al
al al
al aq
Table I hope to get:
num. wk2.. wk3
ad... 1.... 0..
al... 2.... 2..
ap... 1.... 0..
aq... 0.... 1..
as... 2.... 3..
The question you asked is easy. Yes countif will do the job perfectly. You will see that you need a unique list of all combinations. That's easy enough if all your values are in a single column. The question you didn't ask "How do I assemble a unique list when my values are in multiple rows?" took longer to sort out.
So... how is this?
The two sorting columns are arrayformulas and need to be set using CTRL + SHIFT + ENTER and NOT just Enter
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks