Sorting alike objects under the same heading

[LECXE]

How would I sort the following in excel:

code description Main category Sub Category
1234 apple fruit tree
4567 orange fruit tree
3256 strawberry fruit ground
7890 potatoe vegetable ground
9876 gemsquash vegetable ground

to look like this:

FRUIT
1234 apple
4567 orange
3256 strawbwrry

VEGETABLE
7890 potatoe
9876 gemsquash

thanx

 

[ETID]

The pivot table is the tool of choice (for me any way),it is designed to do exactly the type of stuff you need and much more.

 

[LECXE]

I have tried pivot tables, but I keep getting totals of the count of the amount of rows in the table.

 

[Nikita6003]

Hiya,

would using the AdvancedFilter option do? This allows you to filter any db table in Excel using complex criteria, and lets you copy the resulting dataset to any location in your workbook.

I set up a test workbook with your example as base table. I named the table range DBList
I then Set up 2 criteria ranges, consisting of the column header + the criterium needed for each (in your example your columnheader would be Main category with the criterium fruit in the row below - this range I called Fruit - and another with column header Main category and criterium vegetable:.
next comes the VBA: use this code to filter your table & copy results to another location:

Sub UsingAdvancedFilter()
    Dim l_wkbTest As Workbook
    Dim l_wksSheetToFilter As Worksheet
    
    Set l_wkbTest = ThisWorkbook
    Set l_wksSheetToFilter = l_wkbTest.Sheets("Database")
    
    'AdvancedFilter agruments are: RangeToFilter, CriteriumRange, RangeToCopyResultTo, UniquerecordsOnly
    l_wksSheetToFilter.Range("DBList").AdvancedFilter xlFilterCopy, Range("Fruit"), Range("A17"), False
    l_wksSheetToFilter.Range("DBList").AdvancedFilter xlFilterCopy, Range("Vegetable"), Range("A25"), False
    
    Set l_wksSheetToFilter = Nothing
    Set l_wkbTest = Nothing
End Sub

Hope this helps ;-)

Cheers
Nikki

 

[LECXE]

thanx for your time, but I don't even know where to start implementing your code.My Excel is not that advanced.If I could email the spreadsheet to you , it would help.How adventures do you feel today.

 

[Nikita6003]

Hi LEXCE

Sorry I took a while - been celebrating the new year ;-)
My email = Nikita6003@hotmail.com

If u can send today I can take a look; if not - I'm off on holiday until the 12th of Jan

Cheers
Nikki

PS - happy new year

 

[LECXE]

Hi I'll send it.
I found a feature on Excel , Microsoft Access Report..It imports the information into an Access database and allows you to do a report.It even puts a BUTTON on the excel spreadsheet....AMAZING...got to see it to appreciatte it.

 

[combo]

Hi,
Try back pivot table. Put Main category, Code and Description as row fields.
Now do some formatting. Right click each of row fields and select field setting (probably, I have non-English version of excel) and then select "none" in subtotals. Right click table again, select "table options" and deselect totals in format section of displayed dialog.
Now your table should look more or less as you expect

combo

 

[LECXE]

Hi Combo

I pretty much lost U here..Now do some formatting. Right click each of row fields and select field setting (probably, I have non-English version of excel) and then select "none".The Columns have got headings..
CODE DESCRIPTION PRICE SUB CATEGORY.
I just can't seem to get it, it still gives me totals.

Thanx

 

[combo]

Hi LECXE,
May be I tried do be too short in my previous suggestion.

To be clear: pivot table has four areas: rows, columns, page and data. With pivot table wizard, field names in pivot table with pivot table toolbar you can manipulate the field position by draggind heders into desired position. (You can also change order and place of fields in pivot table by dragging them -raised coloured cells with field names- into desired position directly on the worksheet.)
After creation of pivot table all suggestions below concern pivot table, not source data.

You can also format fields in the pivot table. To do this you have tree ways:
1. You can double-click the field name on the pivot table (coloured one) or or pivot table wizard (activate any cell within pivot table area, display pivot table wizard, click "settings" (or similar, left-bottom button on wizard's 3rd page) and double-click field),
2. Right-click field name or any of its data in pivot table and from the context menu celect "field settings",
3. If you are in pivot table field and have visible pivot table toolbar, click "field settings" on it.
In any of above, set "subtotals" to "none". Do so for all named fields in row.

Probably you still have row "Total" at the bottom. To remove this, display "options" for the whole pivot table (either second-left button on p.3 of wizard or "table settings" on context menu (right-click any cell of pivot table or drop-down "pivot table" item of pivot table toolbar. Deselect "column totals".

You can change layout of the table (at least in excel 2000 and XP, I dont remember earlier version options). When you are in "field settings" dialog, you can select "layout" or "advanced" to see options. You can also choose "format report" from the toolbar to use predefined format of report.

You can drag fields on pivot table to change their order, can also put them as page field and then select desired item.

The disadvantage of pivot table use:
1. You should put something into data area or it will stay empty (XP, don't now about 2k and earlier) - but you can hide that column of worksheet,
2. Yoy can't have data from two fields in one column (as you probably need to).


Hope this will be useful
Combo

 

[Nikita6003]

Hi LEXCE

Sorry to get back to this so late, but I've been away on holiday.

I got your files & figured the best way around this is a little bit of (fairly generic) VBA code. I've based this on your contractcategorizedSAF037.xls file, but it's easily adaptible to the other file as well.

I started with the one sheet in the workbook (name = "Saf037&quot & added a button with the following code under it:

Private Sub cmdReportInTables_Click()
    Dim l_wkbCurrent As Workbook
    Dim l_wksReport As Worksheet
    Dim l_wksTable As Worksheet
    
    Dim l_lRow As Long
    Dim l_lMaxRow As Long
    Dim l_lReportRow As Long
    
    Dim l_bNewTable As Boolean
    Dim l_sCurrentCategory As String
    
    Set l_wkbCurrent = ThisWorkbook
    Set l_wksTable = l_wkbCurrent.Sheets("Saf037")
    If SheetExists(l_wkbCurrent, "PivotReport") Then
        'Take the existing report & clear
        Set l_wksReport = l_wkbCurrent.Sheets("PivotReport")
        l_wksReport.UsedRange.Clear
    Else
        'Create new report
        Set l_wksReport = l_wkbCurrent.Sheets.Add
        l_wksReport.Name = "PivotReport"
    End If
    
    'Determine max number of rows
    l_lMaxRow = l_wksTable.UsedRange.Rows.Count
    'Determine if there are any empty formatted rows coz' Excel will include these in the UsedRange
    For l_lRow = l_lMaxRow To 1
        If l_wksTable.Cells(l_lRow, 1) <> &quot;&quot; Then
            l_lMaxRow = l_lRow
            Exit For
        End If
    Next l_lRow
    
    'Now sort the table on column Description - this to get the groups together
    l_wksTable.Range(&quot;Table&quot;).Sort Key1:=&quot;main category&quot;, Order1:=xlAscending, Header:=xlYes
    
    'Set check string which'll check to see if a new MainCategory table should be created
    l_sCurrentCategory = l_wksTable.Cells(2, 4)
    'Set Newtable boolean to TRUE - the first table's new & should get a header
    l_bNewTable = True
    'Set startrow of report to 1
    l_lReportRow = 1
    
    'report per group on separate report sheet
    For l_lRow = 2 To l_lMaxRow
        'Create header if new table
        If l_bNewTable Then
            'Report Main Category
            l_wksReport.Cells(l_lReportRow, 1) = l_sCurrentCategory
            l_lReportRow = l_lReportRow + 1
            
            'Table header
            l_wksReport.Cells(l_lReportRow, 1) = &quot;Code&quot;
            l_wksReport.Cells(l_lReportRow, 2) = &quot;Description&quot;
            l_lReportRow = l_lReportRow + 1
            
            'Set NewTable boolean to FALSE - we only need this once per table
            l_bNewTable = False
        End If
        
        'Report items for this category
        l_wksReport.Cells(l_lReportRow, 1) = l_wksTable.Cells(l_lRow, 1)
        l_wksReport.Cells(l_lReportRow, 2) = l_wksTable.Cells(l_lRow, 2)
        
        'Move to new row on report sheet
        l_lReportRow = l_lReportRow + 1
        
        'Test to check if a new table should be started
        If l_wksTable.Cells(l_lRow + 1, 4) <> l_sCurrentCategory Then
            'Set NewTable variables
            l_bNewTable = True
            l_sCurrentCategory = l_wksTable.Cells(l_lRow + 1, 4)
            
            'Add extra empty row between the current and the new table
            l_lReportRow = l_lReportRow + 1
        End If
        
    Next l_lRow
    
    
    'release objects - just in case xl gets nasty
    Set l_wksReport = Nothing
    Set l_wksTable = Nothing
    Set l_wkbCurrent = Nothing
    
End Sub


Private Function SheetExists(p_wkbWorkbook As Workbook, p_sWorksheetName As String) As Boolean
    On Error GoTo ErrExit
    
    'Assume the sheet does not exist
    SheetExists = False
    'Try to activate it: if xl can't, the error handler will close the Function & return FALSE
    p_wkbWorkbook.Sheets(p_sWorksheetName).Activate
    'The sheet exists - return TRUE!
    SheetExists = True

ErrExit:
End Function

The little SheetExists function's an old one I usually add to any xl VBA project - it's darn handy! ;-)

The result will be: a table for each MainCategory, with all items for that category reported under the header. Between each CategoryTable there's an empty line

Hope this helps - if not for what you were building, perhaps for some other project

Cheers
Nikki ;-)

ps - had a great holiday, btw