Yep - thats the plan exactly. Can do the maths from there - sumif, or pivot table, to get number of items listed in each category. The only caveat is that secondary categories are not accounted for. However, for now, primary categories will get me going.
So the full solution (for primary categories) is:
First, get to the File Management Centre (Hover over My Ebay (at top right) > Selling/Sold > Selling Tools panel > File Management Centre)
Next, get the full list of categories supported by Ebay (Left menu > Overview section > right-click "Instructions and Resources" > Open link in new tab > Selling Manager Pro File Management Centre User Guide (pdf) > scroll down to the contents > find the "Template field definitions" section, then the "Design and display fields" subsection, then click on "*Category" (which is on page 46 at the moment) > in the first paragraph is a link for "Category IDs" - click that
This will download a CSV file containing all Ebay categories (9,000+ at the moment).
Next, you want a list of your active listings:
Back in the File Management Centre > Left Menu > Download section > Download Files > Listings and Records dropdown > choose "Active" > Email Address field - enter your email address > Save button
This will email you a CSV list of all your active listings.
If you open your CSV list of active listings in Excel, then you should see data filling columns up to column W (at time of writing).
Also open your list of categories (the first CSV) - that will have 2 columns of data. The category ID in column A and the Category path in column B.
Select these 2 columns (Click on column A header and drag to column B before releasing the mouse button) > Copy (eg. CTRL+C) > go to your workbook for the list of active listings (the other CSV) > click in cell AA1 (not A1) > Paste (CTRL+V)
Select cells AA1 and AB1 > right-click > Delete... > Shift cells up (this gets rid of the column headers).
Now select columns AA1 and AB1 > Alt+D, then S on your keyboard (to access the sort dialogue) > Sort by column AA from Smallest to Largest > OK button (because we will use the VLOOKUP function which requires the index - our category IDs - to be in alphabetical order).
In cell X2, enter the following formula: =VLOOKUP(P2,AA:AB,2)
This assumes column P has the heading "Category Number". If it does not, then change P2 to whichever column has the category numbers.
Now copy cell X2 (Select the cell, then CTRL+C) > CTRL+End on your keyboard (this takes you to the last cell being used in this sheet) > use your Left arrow on the keyboard to move across to Column X > CTRL+SHIFT+Up to select all cells from here back up to X2 > Paste (CTRL+V)
Column X now displays the full path to the (primary) category for each of your listings.
Now let's count them.
In cell Y2, enter the following formula: =COUNTIF(X:X,X2)
Now similarly copy this formula down the column (Copy cell Y2 > CTRL+End > Left arrow back to column Y > CTRL+Shift+Up > Paste)
Column Y now shows the total number of listings in the category that is displayed on that row. In my case I have fewer than 2,000 listings, but there are over 9,000 categories in columns AA and AB - hence I have about 7,000 rows (under my listings) showing "#N/A" for the category.
Now to produce a simple list of the categories you are using:
Go to the last row of your listings and select columns X and Y back up to the top > Insert menu > Pivot Table > OK.
This creates a new worksheet.
In the PivotTable Fields menu area, drag the value from the main panel, onto the "Rows" panel. Drag it again from the main panel onto the "Values" panel.
You should now have an alphabetised list of the categories in which you have active listings, together with a count of the number of listings in each category.
