HELP: Excel 2010; finding duplicate numbers

[Zz Yzx]

Hi all;

Suppose I have an Excel 2010 sheet. Column A has ~100 numeric entries
with variable significant figures and/or decimal places. Duplicate
entries are not uncommon.

Now, suppose that of the ~100 values, "42" is listed 3 times, and "65"
is listed 5 times.

I need to find a way to count the duplicate entries, and produce a
summary, as follows:

Value Occurrences
42 3
65 5


Any help?

Thanks a heap,
-Zz

 

[Jim Cone]

Add a title directly above the list
Select the title and list
Insert | Pivot Table
On the pivot table layout view, drag the title to the row field.
Also, drag the title to the data field.
"OK" and "Finish" your way out of the pivot table.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"Zz Yzx" <[email protected]>
wrote in message

 

[Zaidy036]

Hi all;

Suppose I have an Excel 2010 sheet. Column A has ~100 numeric entries
with variable significant figures and/or decimal places. Duplicate
entries are not uncommon.

Now, suppose that of the ~100 values, "42" is listed 3 times, and "65"
is listed 5 times.

I need to find a way to count the duplicate entries, and produce a
summary, as follows:

Value Occurrences
42 3
65 5


Any help?

Thanks a heap,
-Zz

free ASAP Utilities has that function under "Range"

 

[Zz Yzx]

On Fri, 11 Feb 2011 09:49:42 -0800, "Jim Cone"

Thanks Jim. But I'm still not getting it.

Add a title directly above the list
Select the title and list
Insert | Pivot Table
On the pivot table layout view, drag the title to the row field.

I drag the title to the "Row Labels" area, and I get a list of the
values sorted low-high with the duplicate values removed.

Also, drag the title to the data field.

I don;t see a "data field" similar to the "row labels" field

"OK" and "Finish" your way out of the pivot table.

But what I need to do is identify the duplicate values and the number
of dublicates. i.e. produce a pivot table containing the duplicates
like this:

Value Occurences
42 3
65 5

Am I missing something?

Thanks again,
-Zz

 

[Jim Cone]

You are missing dragging the title to the data area.

When I start a new pivot table in xl2007 after selecting the data,
there are two areas (windows) on the worksheet.
At the far right is one area with the Title at the top.
At the left side of the sheet is a labeled white rectangle with one
portion that says: "Drop Data Items Here" - that is the data field.
Its very similar to magic. You will get a list exactly like the one you describe.

Experiment, make up a dummy list in a new workbook and drag stuff around,
you won't break anything. If it gets screwed up, just close the workbook.

In xl2003, there was a "Layout" button that when clicked presented a more coherent
display.
But that's the way with MS, they make the new office versions different, not necessarily
better.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)



"Zz Yzx" <[email protected]>
wrote in message

 

[Zz Yzx]

On Fri, 11 Feb 2011 14:46:05 -0800, "Jim Cone"

I think MS must have changed things again for XL2010.

You are missing dragging the title to the data area.

When I start a new pivot table in xl2007 after selecting the data,
there are two areas (windows) on the worksheet.
At the far right is one area with the Title at the top.
Check.

At the left side of the sheet is a labeled white rectangle with one
portion that says: "Drop Data Items Here" - that is the data field.

In XL2010 the box on the left now is titled "PivotTable 1", and reads
"To build a report choose fields from the PivotTable fields list".
Dragging from the data field box on the far right has no effect, but
checking the little box casues the title to appear in the "Sum" field
on the right side, and produces a sum of the data (minus the duplicate
values) in the block on the left. Dragging the title from the "Sum"
field" to the "Row Label" field produces the data values in a column,
minus the duplicate values.

I do appreciate your time.

-Zz