Pivot Table combining multiple columns
 

I have 20 columns that contain the same information Column B through Column U

Column A Column B Column C Column D Column E
Name of Customer Printer 1 Printer 2 Printer 3 Printer 4

I want to combine all 20 columns to give me a count of items that are equal
in all the columns and have the ability to expand out to see the customers.

So in this case:
Column A Column B Column C Column D Column E
Joe Blow Epson 7600 Epson 4000 HP 5500 C80
Petey Pablo Epson 4000 Epson 9600 Epson X2 HP2000

I would see the following
Printers Count of Company
Epson 4000 2
Epson 7600 1
Espon 9600 1
Espon X2 1
HP 2000 1
HP 5500 1
C80 1

Does anyone know how to do this?

Thank you

With your current data layout, you could use formulas to count the
number of each printer type.

To do this in a pivot table, you could rearrange your source data, e.g.:

Name Num Printer
Joe Blow 1 Epson 7600
Joe Blow 2 Epson 4000

To calculate the number, use a formula: =COUNTIF(A$1:A2,A2)

In the pivot table, put Printer in the Row area, and Name in the data area.


Pete Petersen wrote:
I have 20 columns that contain the same information Column B through Column U

Column A Column B Column C Column D Column E
Name of Customer Printer 1 Printer 2 Printer 3 Printer 4

I want to combine all 20 columns to give me a count of items that are equal
in all the columns and have the ability to expand out to see the customers.

So in this case:
Column A Column B Column C Column D Column E
Joe Blow Epson 7600 Epson 4000 HP 5500 C80
Petey Pablo Epson 4000 Epson 9600 Epson X2 HP2000

I would see the following
Printers Count of Company
Epson 4000 2
Epson 7600 1
Espon 9600 1
Espon X2 1
HP 2000 1
HP 5500 1
C80 1

Does anyone know how to do this?

Thank you




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html