Formula to extract unique records only

[hrayani]

Hi,

I need a formula to extract only the first occurrence of the data or unique records I can say. The formula should
look all the 3 columns in a row and should treat it as a duplicate if all the 3 cells are equal - otherwise not.

here is a sample data in A2:C17

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Article[/TD]
[TD="class: xl65, width: 64"]Quality[/TD]
[TD="class: xl65, width: 64"]Unit[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]BB[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Pair[/TD]
[/TR]
[TR]
[TD="class: xl66"]DD[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Dozen[/TD]
[/TR]
[TR]
[TD="class: xl66"]EE[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Grams[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]BB[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Pair[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]DD[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Dozen[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Pair[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Dozen[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Grams[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
</tbody>[/TABLE]


unique data required in columns H2:J17

in the above case the answer in H2:J17 would be

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Article[/TD]
[TD="class: xl65, width: 64"]Quality[/TD]
[TD="class: xl65, width: 64"]Unit[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Kgs[/TD]
[/TR]
[TR]
[TD="class: xl66"]BB[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Pair[/TD]
[/TR]
[TR]
[TD="class: xl66"]DD[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Dozen[/TD]
[/TR]
[TR]
[TD="class: xl66"]EE[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Grams[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Pair[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Dozen[/TD]
[/TR]
[TR]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Grams[/TD]
[/TR]
</tbody>[/TABLE]


Rest all rows till 17 will show blanks as there is no more unique data.

If any one can provide a solution

Regards,

Humayun

 

[Kamolga]

Copy A1:C17 to H1:J17, go on data tab and click remove duplicates (then tick "my data has a header")

 

[hrayani]

Thanks for reply,

Yes i can do that manually but i need a formula to do that coz the actual data will keep on adding. So if there is a formula then i do not have to remove duplicates manually everytime....

I found one formula which works for a single column....

=IFERROR(INDEX($A$2:$A$17, MATCH(0,COUNTIF($H$1:H1, $A$2:$A$17), 0)),"")

but i dont know how to get it to work for 3 columns...

Any Idea ???

 

[James006]

Hello,

In cell H2 ... test following Array Formula:

=IFERROR(INDEX(A$2:A$17,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$17&$B$2:$B$17&$C$2:$C$17),0)),"")

Hope this will help

 

[Kamolga]

It is possible with an array formula {}, but could not find it yet.
A better solution than formulae going down and showing "" if error is to set this data in a table (so range extend automatically, no need to update the source), make a pivot

(tabular form) ​

of the table in h1 putting Article, quantity and unit in rows and refresh the pivot automatically when you activate the sheet:
right click sheet name, click view code and paste

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

 

[hrayani]

Hi James,

I tried it... All it is giving the first cell value in all the cell till row 17

 

[hrayani]

A better solution than formulae going down and showing "" if error is to set this data in a table (so range extend automatically, no need to update the source), make a pivot

The formula wont do down more than 70 rows... coz there will not be more than 70 unique values... Yes the actual data currently runs at over 600 rows and will be increasing everyday.

I am not familiar with tables and pivot..

It is possible with an array formula {}, but could not find it yet

Yes u r right... I am hopefull i will get one here

 

[James006]

Hello,

You can test following:

Sub Unique3Cols()
    With Worksheets("Sheet1")
        .Range("A1:C17").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("H1:J1"), Unique:=True
    End With
End Sub

HTH

 

[Nishant94]

Maybe something like this?

Book1
	A	B	C
1	Article	Quality	Unit
2	AA	1	Kgs
3	BB	2	Pair
4	DD	3	Dozen
5	EE	4	Grams
6	AA	1	Kgs
7	BB	2	Pair
8	AA	1	Kgs
9	AA	1	Kgs
10	DD	3	Dozen
11	AA	3	Pair
12	AA	5	Dozen
13	AA	5	Grams
14	AA	1	Kgs
15	AA	1	Kgs
16	AA	1	Kgs
17	AA	1	Kgs
18
19
20
21	Count		7
22	Article	Quality	Unit
23	AA	1	Kgs
24	BB	2	Pair
25	DD	3	Dozen
26	EE	4	Grams
27	AA	3	Pair
28	AA	5	Dozen
29	AA	5	Grams
30
31
Sheet2

Cell Formulas
Range	Formula
C21	{=SUM(IF(FREQUENCY(MATCH($A$2:$A$17&$B$2:$B$17&$C$2:$C$17,$A$2:$A$17&$B$2:$B$17&$C$2:$C$17,0),ROW($A$2:$A$18)-ROW($A$1)),1,0))}
A23	{=IF(ROWS(A$23:A23)>$C$21,"",INDEX(A$2:A$17,SMALL(IF(COUNTIFS($A$22:$A22,$A$2:$A$17,$B$22:$B22,$B$2:$B$17,$C$22:$C22,$C$2:$C$17)=0,ROW($A$2:$A$17)-ROW($A$1)),1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hrayani]

Hi Nishant,

Thanks for the solution.... working perfect....

Is it possible to merge these 2 formulas.... I dont know if i am sounding weird..