Results 1 to 2 of 2

Thread: Multiple columns with duplicate data to display as unique numbers in a pivot

  1. #1

    Multiple columns with duplicate data to display as unique numbers in a pivot

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I could really do with some help on the following xl problem as my brain is feeble to work it out :-/

    I have a sheet with multiple individual asset that that have between 0 and 4 capex numbers (CX-1 To CX-4) in columns T to U. what I need is to be able to show data in a pivot table of each capex and the equipment associated with it.

    I am trying to create a pivot table using data that is both duplicated (non unique), and that has a many-to-one relationship with individual items (As in the pic below, CX-1 To CX-4 relate to columns T to U).
    Essentially, a purchased item is tracked against several invoice numbers as the cost of an item is shared amongst involved parties. The invoice (numbers) will also not just relate to one item as the invoice can contain multiple items on the shopping list.
    Essentially I would like to be able to pivot table by unique invoice number and have the result spit out all the items relate to it.

    My trouble is that there are + 16k rows with a possible 4 numbers per row, but only 59 individual numbers.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	57.5 KB 
ID:	3219

    Any help will be appreciated.

    Thanks in advance.


  2. #2
    Excel 2010 with free PowerPivot and Power Query Add-Ins.
    Compatible with Office 2013 Pro Plus.
    Need link to full file at a file sharing site.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts