Unique Records (Excel 2000)

Topic

New Reply

(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:

sys	stage	id	amt	code	line	name
R00000770677-1	NEW	10698928	1282.55	99-88-0000-9999-444	1	JOE
R00000770677-1	OLD	10698928	1282.55	99-88-0000-9999-444	2	PREVA

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

To count uniqe “Sys” items, you can do it with a pivot table
Select a cell in the data range
Data – pivot table report

Put in the row
Put in the row
Put another field (eg ) into Data (it should be 'Count of Amt' or 'Sum of Amt' as desired)

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"

Only unique records are shown.

Steve

Reply | Quote

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

Thanks and best regards.

Mitch

Reply | Quote

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

Thanks and best regards.

Mitch

Reply | Quote