+ Reply to Thread
Results 1 to 3 of 3

Pivot Table Query - Looking for a Neat, Clean Solution

  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Pivot Table Query - Looking for a Neat, Clean Solution

    I have 2 columns of data. I need to generate a pivot table that lists all instances where a col 1 has more than 1 unique reference in col 2.

    I do not want to remove duplicates as we need these later. (See attached sample sheet)

    e.g. in pivot table it should show:

    Col 1 Count
    arrivealive.co.za 3

    Then when you click on the 3 it opens sheet to show:
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za salearners.co.za
    arrivealive.co.za k53.gavinhoole.ws


    Col 1: Col 2:
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za getyourlearners.co.za
    arrivealive.co.za salearners.co.za
    arrivealive.co.za salearners.co.za
    arrivealive.co.za k53.gavinhoole.ws
    arrivealive.co.za k53.gavinhoole.ws
    arrivealive.co.za k53.gavinhoole.ws
    arrivealive.co.za k53.gavinhoole.ws
    autostart.co.za salearners.co.za
    autostart.co.za salearners.co.za
    biketalk.co.za salearners.co.za
    brainexperiment.org getyourlearners.co.za

    Any help will be most appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Pivot Table Query - Looking for a Neat, Clean Solution

    I don't really understand what you're asking since you already know how to lay the pivot table out.

    Just put column one in the rows field and column 2 in the values field? - Or have I missed something?

  3. #3
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Pivot Table Query - Looking for a Neat, Clean Solution

    If I do a pivot table it almost works but not quite! :-)

    Problems
    1) It shows the duplicates. I.e. it shows
    col 2:
    row 1: arrivealive.co.za 9 (for getyourlearners.co.za)

    If I remove duplicates it works, but this means another massive table and another couple of steps in the process. It should only show 1 here not 9.

    2) How do I get it to only show values where count is total count is greater than 1 (i.e. where col a has more than 1 unique reference in col b)?

    See sample2 attached.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1