Sorting Subtotals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeB - 20 Apr 2008 19:18 GMT

Thanks for the answers on my previous question. I ended up not using
them (those formulas are far too complex for a dabbler like me). I
created two columns to find the origin of either "http://" or "http://
www." (always "1" if found), multiplied that by either 7 or 11 to get
the starting position and then had a columsn for Max() of the previous
two columns. Then I did another column to find the trailing "/" and
then a Mid() with the values obtained in the preceding columns.

Not I have the domain names, and I can sort the table to get
Subtotals. What I'd like to do is to again sort the table after I have
subtotals, so I can get the domain with the higest count at the top of
the list.

How do I do this?

Thanks

Reply to this Message

Pete_UK - 20 Apr 2008 20:19 GMT

Don't use Data | Subtotals.

Instead you can extract a list of all the unique domain names on
another sheet, and then use COUNTIF.

Insert a new sheet. Copy your domain names (including a header) then
select the new sheet and with the cursor in A1 do Edit | Paste Special

| Values (check) | OK then <Esc>. If you don't have a header row then

insert a new row 1 and put "Domain" in A1. Then highlight all you data
including the header in sheet2 and click on Data | Filter | Advanced
Filter. In the pop-up you should select Unique Records only as well as
Copy to another location, and enter C1 in the destination box. Click
OK and you will have a unique list in column C - you can delete
columns A and B.

Then in B2 you can enter this formula:

=COUNTIF(Sheet1!D:D,A2)

I've assumed that your domain names are in column D of the first sheet
- adjust to suit.

Then you can copy this down to cover your unique list, to give you a
count against each domain name. You can sort this in descending order.

Hope this helps.

Pete

> Thanks for the answers on my previous question. I ended up not using
> them (those formulas are far too complex for a dabbler like me). I
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Rate this thread: