CODEWITHSUNDEEP
excelmacospivot-table
tktktk0711
tktktk0711

Reputation: 1694

Excel PivotTable for counting words for female and male

I have a excel like the following form:

 words      male      words    female
  I           2        rose      4
  am          3        baby      6 
  sunny       4        slim      9
  baby        5        travel    11
  football    9

I want this excel to be a new excel like the following form with Excel PivotTable. If there are common words between male and female, the value of each other, if there is no, just show their own value, other are space. 

   words       male        female
     I           2           
    am           3           
    sunny        4            
    baby         5          6
    football     9
    rose                    4
    slim                    9
    travel                  11

Thanks for your time and consideration!

Upvotes: 0

Views: 427

Answers (1)

Hambone
Hambone

Reputation: 16397

If you paste the data sets on top of each other (cut/paste) and add a label for male/female, a standard pivot table would do the trick:

words    count  gender
I        2      male
am       3      male
sunny    4      male
baby     5      male
football 9      male
rose     4      female
baby     6      female
slim     9      female
travel   11     female

Pivoted:

Row Labels  female  male
am                  3
baby        6       5
football            9
I                   2
rose        4   
slim        9   
sunny               4
travel      11

If you can't do this, are you open to VBA?

Upvotes: 1

Related Questions