CODEWITHSUNDEEP
excelpivot-tableexcel-2016
Tim
Tim

Reputation: 1249

Excel Pivot Table Sorting using Multiple Fields and No Grouping

I have an Excel Workbook with contact information on one worksheet and a form on another. I want to create another worksheet that has a filtered set of the contacts FULLNAME to display in a list on the form worksheet. I have it working with a pivot table with FULLNAME but I can't add a sort on LASTNAME without grouping the items, which is not what I want. Any suggestions that doesn't include data duplication

UPDATE:

So I have it partially working the way I want it with this method. The below image is a piviot table based on a table in a worksheet. You can see that the names are sorted by first name. I would like them sorted by last name

enter image description here

So when I add the last name to the Piviot table so I can sort by last name, of course it groups the data like so, which is expected.

enter image description here

which gives me this result in my form list

enter image description here

as you can see the Last Name Grouping entry also shows up in the list which is not desirable.

UPDATE: Using Andreas solution below and a couple other modifications I was able to complete this problem. I'm posting a completed solution because I know someone else will need it.

So using Andreas solution in the image below it gave me the first set of data to the right of my pivot table. As you can see it has spaces and will be unsightly binding that table to a list. So using the following macro function I was able to create column 3 data with no spaces. The function is as follows...

=IFERROR(INDEX(SortedSpacedRange,SMALL((IF(LEN(SortedSpacedRange),ROW(INDIRECT("1:"&ROWS(SortedSpacedRange))))),ROW(A1)),1),"")

Rather than using ranged data in the function I created a table out of the second column data called "SortedSpacedRange". This allows me to use it across multiple sheets better. Don't forget to use Ctrl+Shift+Enter to insert the function.

enter image description here

As you can see below I now have a last name sorted list with no spaces other than the one I placed purposfully in the first row of the unspaced table.

enter image description here

I hope someone finds this useful. Thanks again Andreas

Upvotes: 1

Views: 2140

Answers (1)

Andreas
Andreas

Reputation: 23958

Now I see what you need.
Only a few days ago I asked how to sort out items from a pivot table here:
https://stackoverflow.com/questions/39383971/how-can-i-detect-pivot-table-new-items

What you need is: 

Function IsItem(c)
    If c.IndentLevel <> 0 Then ' I think this will work, not tested. Typing on my phone
        IsItem = True
    Else
        IsItem = False
    End If

End Function

This will return true/false if the item in the pivot table is a new item or a "sum".

So in a new column next to the pivot table use the following formula:

EDIT: it obviously is not A1 the pivot table starts at, my bad. Just replace the cell with whatever cell is the first one. END OF EDIT. EDIT 2: I see now that it's A4 that is the first one. END OF EDIT 2.

=if(A4="","",if(IsItem(A4),A4,""))

It first looks at the cell and if it's empty the return empty.
If not empty it looks at the value and if it is the full name (not only lastname), it will return the name, else empty.

Now if you fill down this formula, you should have a list of only the full names that you can use in your form list.

The vba code should be placed in a module and the file needs to be saved as macroactivated workbook xlsm before you add the formulas on the sheet.

Upvotes: 1

Related Questions