Combining columns in pivot table results.

Very new to pivot tables!!! I have looked, read, and searched and to no avail I cannot seem to find the solution. So, now I turn to the experts.

The situation:

I have a table that shows: Column A = Date; Column B = Tag Number; Column C = Full Time Equivalent 1; Column D = Full Time Equivalent 2; additional data to the right of Column D.

Employees have been placed in both FTE 1 and FTE 2 columns. For example, Mickey Mouse on one day is placed in FTE 1 position, while on another day Mickey Mouse is placed in the FTE 2 position.

How can I get the number of times Mickey Mouse is in both FTE 1 and FTE 2 positions combined? Is there an easy way to do this? For example, I want to create a pivot table based on a value in Column F, but I want the total times Mickey Mouse was listed in both columns.

Any help is greatly appreciated.

Sometimes the best solution to a pivot table issue is to use helper columns in the source data. In this case, =COUNTIF([FTE 1],[@[FTE 1]])+COUNTIF([FTE 2],[@[FTE 1]]) - it counts the number of time Mickey is in FTE1 and adds it to the number of times he's in FTE2.

A word of caution: if Goofy were to join the organization and appears only in FTE2, he's not accounted for. The formula only works if everyone in the organization appears in at lest FTE1 at least once.

Also since the formula has the total everywhere it appears, if you want to display the value on a pivot table, use average otherwise you get the totals total. Unfortunately, this does screw up the Grand Total for the columns.

To do this really right, you would have to normalize the data which will take VB Code. And that will await some free time tomorrow.

Free time came. I renamed your original table from Table1 to Table_Original and added a second table, Table_Normalized.

Usually, pivot tables are most powerful when based on normalized data. That's what this code does; it changes the layout of the data. In this case, instead of having two separate columns each with a name, we have a name and associated with the name is either FTE 1 or FTE 2.

Running the macro converts the original table into a normalized table. The results are shown on the FTE Totals sheet.