Hi

For a Pivot Table to work, each column has to have a unique heading in
the first row.
It does not matter if there is data in every column of each row.

From your posting, it is difficult to see your layout.

One alternative using non-array formulas which automates the extraction of
the uniques list from 4 source cols and the corresponding counts of the
uniques ..

Illustration in this sample construct:
http://www.savefile.com/files/841298
Multicol merge n uniques extract n count.xls

Source data assumed in cols A to F, data from row5 down
where col C = Lube data, col D = air-primary data,
col E = air-secondary data, col F = fuel-inner data

Using 4 adjacent cols to the right, cols G to J

In G5:
=OFFSET(C$5,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))
Copy down to cover the max expected extent of aggregated data in the 4
source cols C to F. If you expect source data per col to span up to say 100
rows down, then copy down by a total of 400 rows (4 cols x 100 rows = 400
rows). Col G functions to merge the contents of cols C to F into a single
col. Empty source cells will be returned as zeros.  

In H5:
=IF(G5=0,"",IF(COUNTIF(G$5:G5,G5)>1,"",ROW()))
Leave H1:H4 blank. This is a criteria col to ignore the zeros and flag the
unique items extracted in col G.

In I5:
=IF(ROWS($1:1)>COUNT(H:H),"",INDEX(G:G,SMALL(H:H,ROWS($1:1))))

In J5:
=IF(I5="","",COUNTIF(G:G,I5))
Select H5:J5, fill down to the same extent as for col G. Hide away cols G &
H. Col I will return the list of unique items from cols C to F, while col J
returns the counts of these unique items. All results will be neatly bunched
at the top.

You could then just do a simple copy n paste special as values for cols I &
J elsewhere, and use Data > Sort menu to sort by the uniques col.