Pivot from multiple sheets

Hi,

I need to make a pivot from multiple sheets.

I have tried to make by consolidating the sheets, hoeverthere are some
limitations to that. It does not give me the flexibility for presentation of
the data.

for example i have the below data if i make a pivot then it wont give the
breakdown of the column "B". it always gives the breakdown of column "A" and
its count.

In the below data the count of "Mark" is 4 and out of this Automatic = 3 and
Manual = 1 which is possible for a single sheet. however pivot by
consolidating sheets does not give the requred output. Please help if can be
done and i am not aware of

Name Process
Mark Automatic
Henry Automatic
Jacob Manual
Steve Automatic
Mark Manual
Henry Automatic
Mark Automatic
Henry Manual
Mark Automatic