Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Values and only returning one...
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Values and only returning one...
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) "Andy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Values and only returning one...
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) "Andy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|