Counting Values and only returning one...

[Andy]

In a table, I have a field which may contain duplicate entries (dups only in
the field, not row dups).

I need a query that will return only one record of each entry in this field,
as well as in a separate field, a count of how many were in the orginial
table.

i.e. if the value 06A34965 appeared three times in table 1, I want the query
to return just one row wiht 06A34965 with a count of 3. It does not matter
which of the 3 rows the query returns.

Thanks

 

[Bob Phillips]

Lookup Find in VBA help, that will get you the cell with the data.

Then use Activecell.EntireRow.Copy to c opy all the data

And use Application.CountIf(table_range, value) tyo count the instances.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[JLatham]

Assume your table goes from A10 to C100 (3 columns) and your 06A34965 type
entries are in column A. You can use VLOOKUP() to get the information from
the matching row and COUNTIF() to get the number of matches to 06A34965.

For example, assume you do your work up at row 1. In A1 you type the number
to match (06A34965)
In B1 put =COUNTIF(A10:A100,A1) to get the count of matches.
in C1 put =VLOOKUP(A1,A10:C100,1,FALSE) to get information from first match,
column B
in D1 put =VLOOKUP(A1,A10:C100,2,FALSE) to get information from first match,
column C

These would work used on another sheet, I just showed how on same sheet, the
cell references would just have to have sheet name placed in front of them.
Kind of like this:
=COUNTIF('Sheet1'!A10:A100,A1)
and
=VLOOKUP(A1,'Sheet1'!A10:C100,2,FALSE)