title: Excel Pivot Table Sorting using Multiple Fields and No Grouping
body: <p>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</p>
  
  <p>UPDATE:</p>
  
  <p>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</p>
  
  <p><a href="https://i.stack.imgur.com/u2lSx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/u2lSx.jpg" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/cuXPa.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cuXPa.jpg" alt="enter image description here"></a></p>
  
  <p>which gives me this result in my form list</p>
  
  <p><a href="https://i.stack.imgur.com/AVrYg.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AVrYg.jpg" alt="enter image description here"></a></p>
  
  <p>as you can see the Last Name Grouping entry also shows up in the list which is not desirable.</p>
  
  <p>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.</p>
  
  <p>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...</p>
  
  <pre><code>=IFERROR(INDEX(SortedSpacedRange,SMALL((IF(LEN(SortedSpacedRange),ROW(INDIRECT("1:"&amp;ROWS(SortedSpacedRange))))),ROW(A1)),1),"")
  </code></pre>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/290rQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/290rQ.jpg" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/m50jJ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/m50jJ.jpg" alt="enter image description here"></a></p>
  
  <p>I hope someone finds this useful. Thanks again Andreas</p>