Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Records (Excel 2000)

    (Edited by HansV to present data in table form)

    I have a spreadsheet filled with duplicate records. I would like to know the total by "sys" (see below). Is there any way to only count the "amt" column and still be able to display the "code" column. See below:

    <table border=1><td>sys</td><td>stage</td><td>id</td><td>amt</td><td>code</td><td>line</td><td>name</td><td>R00000770677-1</td><td>NEW</td><td align=right>10698928</td><td align=right>1282.55</td><td>99-88-0000-9999-444</td><td align=right>1</td><td>JOE</td><td>R00000770677-1</td><td>OLD</td><td align=right>10698928</td><td align=right>1282.55</td><td>99-88-0000-9999-444</td><td align=right>2</td><td>PREVA</td></table>
    I'd like to be able to show the sys, amt and code 1 time only vs. the 2 times here (for example, R00000770677-1. 2182/55 and 99-88-0000-9999-444). Is this possible in a spreadsheet that may have 5000 rows of data but only 2500 of them are unique.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Records (Excel 2000)

    Your question confuses me. Do you want to count unique records (as you seem to imply in the first paragraph) or to display only unique values (as you seem to imply in the last paragraph)? Those are different requirements.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unique Records (Excel 2000)

    To count uniqe "Sys" items, you can do it with a pivot table
    Select a cell in the data range
    Data - pivot table report
    <next><next>
    Put <sys> in the row
    Put <code> in the row
    Put another field (eg <Amt>) into Data (it should be 'Count of Amt' or 'Sum of Amt' as desired)
    <Finish>
    Now you have a count of the unique 'sys'

    To display the unique records:
    Data - filter - advanced filter
    Select "Filter the list in place"
    Check "Unique records only"
    <ok>

    Only unique records are shown.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records (Excel 2000)

    Steve, the display unique records did the trick!!!!

    Thanks and best regards.

    Mitch

Posting Permissions

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