Unique values
I need help for returning unique values. I can count the unique values but I
need to show them. For each different ManDocNo, I want to return the unique EPACodes. A simplified example below: ManDocNo EPAUCode EPAPCode EPAD1Code 24155580 24155580 D009 24155580 24155580 U151 D009 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 D001 AR1599445 U019 D001 AR1599445 D002 AR1599445 P087 AR1599445 P105 AR1599445 U006 D001 AR1599445 U019 D018 AR1599445 U031 AR1599445 U044 D022 AR1599445 U053 D001 AR1599445 U057 D001 AR1599445 U092 D002 AR1599445 U103 D002 Thanks for your help. |
Unique values
In say M2, type =A2
In M3, enter =IF(ISERROR(MATCH(0,COUNTIF(M$2:M2,$A$2:$A$200&"") ,0)),"", INDEX(IF(ISBLANK($A$2:$A$200),"",$A$2:$A$200),MATC H(0,COUNTIF(M$2:M2,$A$2:$A $200&""),0))) which is an array formula, so commit with Ctrl-Shift-Enter. Copy M3 down. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "travis" wrote in message ... I need help for returning unique values. I can count the unique values but I need to show them. For each different ManDocNo, I want to return the unique EPACodes. A simplified example below: ManDocNo EPAUCode EPAPCode EPAD1Code 24155580 24155580 D009 24155580 24155580 U151 D009 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 D001 AR1599445 U019 D001 AR1599445 D002 AR1599445 P087 AR1599445 P105 AR1599445 U006 D001 AR1599445 U019 D018 AR1599445 U031 AR1599445 U044 D022 AR1599445 U053 D001 AR1599445 U057 D001 AR1599445 U092 D002 AR1599445 U103 D002 Thanks for your help. |
Unique values
Different interpretation...
Assumptions: A1:D1 contains your headers/labels A2:D23 contains your data F2:F3 contains 24155580 and AR1599445 Formula: G2, copied across and down: =INDEX($B$2:$B$23,MATCH(0,IF(($A$2:$A$23=$F2)*($B$ 2:$B$23<""),COUNTIF($F 2:F2,$B$2:$B$23)),0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that you'll get #N/A when no more unique values exist. You can use conditional formatting to hide these errors. Also, to create a list of unique ManDocNo's for Column F, you can use 'Advanced Filter' and check 'Unique records only' and copy to your location, Column F. Hope this helps! In article , travis wrote: I need help for returning unique values. I can count the unique values but I need to show them. For each different ManDocNo, I want to return the unique EPACodes. A simplified example below: ManDocNo EPAUCode EPAPCode EPAD1Code 24155580 24155580 D009 24155580 24155580 U151 D009 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 AR1599445 D001 AR1599445 U019 D001 AR1599445 D002 AR1599445 P087 AR1599445 P105 AR1599445 U006 D001 AR1599445 U019 D018 AR1599445 U031 AR1599445 U044 D022 AR1599445 U053 D001 AR1599445 U057 D001 AR1599445 U092 D002 AR1599445 U103 D002 Thanks for your help. |
All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com