Layout of ODBC Data Import

[gc.johnson]

Hi


I have a database table that contains data in the following format: -


Category Sub-Category Error Code
CAT1 SUBCAT1 ERRCOD1
CAT1 SUBCAT2 ERRCOD2
CAT2 SUBCAT1 ERRCOD1
CAT2 SUBCAT2 ERRCOD1
CAT2 SUBCAT2 ERRCOD2


When imported into Excel I would like it to appear as follows: -


CAT1 CAT1 CAT2 CAT2
SUBCAT1 SUBCAT2 SUBCAT1 SUBCAT2
ERRCOD1 Y N Y Y
ERRCOD2 N Y N Y


Where there is a Y wherever an error code is used for
category/sub-category, and N (or blank) wherever not used.


I tried using a PivotTable to do this but failed miserably! Does
anyone have any ideas how to do this???


Thanks
Gary

 

[Max]

One way which might suffice. Do up a pivot table first, then set-up the
desired table in a new sheet using formulas to read/amend the pivot table

In step3 of the pivot table wizard:

Drag Error Code & drop in ROW
Drag Category & drop in COLUMN
Double-click on Category, check "None" under Subtotals
Drag Sub-Category & drop in COLUMN (to the right of Category)
Drag Error Code & drop in DATA (will appear as "Count of Error Code")
Click Finish

Name the pivot sheet: PT (say)
(The pivot table will appear within A1:F6)

Then in a new sheet:
Put in A1: =IF(PT!A2="","",PT!A2)
Copy A1 across to E1, fill down to E4
Replace the formula in B3 with: =IF(PT!B4="","N","Y")
Copy B3 across to E3, fill down to E4
(To change the 1's to "Y" & blanks to "N")