Filter Unique Values

nburaq

nburaq

Board Regular
Joined
Apr 2, 2021
Messages
220
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Gents,
I have data sheet consist of more than 5000 rows and I would like to filter my results based on data in the columns D,E and F. It will basically show unique values when I enter the material number. I tried to do it with =UNIQUE(FILTER(A2:K33;H2:H33=M1)) formula but it does not work how I want because it doesn't retrieve the data combination of group, counter and line as duplicate value. So each time I have to filter the date first and then I have to use advanced filter to delete duplicate values as combination of group, counter and line. ( Red highlighted part is what I want) Thanks again for the solution and help.

Analyze_02.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1OrderPlanItemGroupCounter LineDescpMaterialMaterial DescQtyUnit1155201201201PL001201101GREL012130ENGINE115520GEAR3PC
21201201PL001201101GREL012130ENGINE115520GEAR3PC1201204PL001204101GREL012130ENGINE115520GEAR2PC
31201202PL001202102GREL013330ENGINE115521OIL3PC1201221PL001221121GREL0322230ENGINE115520GEAR3PC
41201203PL001203103GREL0142430ENGINE115522GEAR2PC
51201204PL001204101GREL012130ENGINE115520GEAR2PC
61201205PL001205105GREL0162110ENGINE115524GEAR0PC1155201201201PL001201101GREL012130ENGINE115520GEAR3PC
71201206PL001206106GREL0172230ENGINE115525OIL3PC1201221PL001221121GREL0322230ENGINE115520GEAR3PC
81201207PL001207107GREL018530ENGINE115526GEAR3PC
91201208PL001208108GREL019730ENGINE115527OIL3PC
101201209PL001209109GREL013330ENGINE115521GEAR3PC
111201210PL001210110GREL0211830ENGINE115529OIL3PC
121201211PL001211111GREL0221830ENGINE115530GEAR3PC
131201212PL001212112GREL0232430ENGINE115531OIL3PC
141201213PL001213113GREL0242430ENGINE115532GEAR0PC
151201214PL001214114GREL0251930ENGINE115533OIL3PC
161201215PL001215115GREL0251930ENGINE115534GEAR0PC
171201216PL001216116GREL0271630ENGINE115535OIL3PC
181201217PL001217117GREL0281630ENGINE115536GEAR0PC
191201218PL001218118GREL0291630ENGINE115537OIL3PC
201201219PL001219119GREL0301630ENGINE115538GEAR3PC
211201220PL001220120GREL0312230ENGINE115539OIL0PC
221201221PL001221121GREL0322230ENGINE115520GEAR3PC
231201222PL001222122GREL033530ENGINE115541OIL3PC
241201223PL001223123GREL034530ENGINE115542GEAR2PC
251201224PL001224124GREL035630ENGINE115543OIL3PC
261201225PL001225125GREL036630ENGINE115544GEAR3PC
271201226PL001226126GREL0372330ENGINE115545OIL3PC
281201227PL001227127GREL0382330ENGINE115546GEAR0PC
291201228PL001228128GREL039730ENGINE115547OIL3PC
301201229PL001229129GREL040730ENGINE115548GEAR0PC
311201230PL001230130GREL0411830ENGINE115549OIL3PC
321201231PL001231131GREL0421830ENGINE115550GEAR0PC
331201232PL001232132GREL0431830ENGINE115551OIL3PC
34
Sheet2
Cell Formulas
RangeFormula
N1:X3N1=UNIQUE(FILTER(A2:K33,H2:H33=M1))
Dynamic array formulas.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Tricky! I'm not entirely sure this is right, but it works so far:

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1OrderPlanItemGroupCounter LineDescpMaterialMaterial DescQtyUnit115520OrderPlanItemGroupCounterLineDescpMaterialMaterial DescQtyUnit
21201201PL001201101GREL012130ENGINE115520GEAR3PC1201201PL001201101GREL012130ENGINE115520GEAR3PC
31201202PL001202102GREL013330ENGINE115521OIL3PC1201221PL001221121GREL0322230ENGINE115520GEAR3PC
41201203PL001203103GREL0142430ENGINE115522GEAR2PC
51201204PL001204101GREL012130ENGINE115520GEAR2PC
61201205PL001205105GREL0162110ENGINE115524GEAR0PC
71201206PL001206106GREL0172230ENGINE115525OIL3PC
81201207PL001207107GREL018530ENGINE115526GEAR3PC
91201208PL001208108GREL019730ENGINE115527OIL3PC
101201209PL001209109GREL013330ENGINE115521GEAR3PC
111201210PL001210110GREL0211830ENGINE115529OIL3PC
121201211PL001211111GREL0221830ENGINE115530GEAR3PC
131201212PL001212112GREL0232430ENGINE115531OIL3PC
141201213PL001213113GREL0242430ENGINE115532GEAR0PC
151201214PL001214114GREL0251930ENGINE115533OIL3PC
161201215PL001215115GREL0251930ENGINE115534GEAR0PC
171201216PL001216116GREL0271630ENGINE115535OIL3PC
181201217PL001217117GREL0281630ENGINE115536GEAR0PC
191201218PL001218118GREL0291630ENGINE115537OIL3PC
201201219PL001219119GREL0301630ENGINE115538GEAR3PC
211201220PL001220120GREL0312230ENGINE115539OIL0PC
221201221PL001221121GREL0322230ENGINE115520GEAR3PC
231201222PL001222122GREL033530ENGINE115541OIL3PC
241201223PL001223123GREL034530ENGINE115542GEAR2PC
251201224PL001224124GREL035630ENGINE115543OIL3PC
261201225PL001225125GREL036630ENGINE115544GEAR3PC
271201226PL001226126GREL0372330ENGINE115545OIL3PC
281201227PL001227127GREL0382330ENGINE115546GEAR0PC
291201228PL001228128GREL039730ENGINE115547OIL3PC
301201229PL001229129GREL040730ENGINE115548GEAR0PC
311201230PL001230130GREL0411830ENGINE115549OIL3PC
321201231PL001231131GREL0421830ENGINE115550GEAR0PC
331201232PL001232132GREL0431830ENGINE115551OIL3PC
Sheet2
Cell Formulas
RangeFormula
N2:X3N2=FILTER(A2:K33,IFERROR(MATCH(D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&M1,D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&H2:H33,0)=SEQUENCE(ROWS(D2:D33)),0))
Dynamic array formulas.
 
Last edited:
Upvote 0
Solution
Eric W said:
Tricky! I'm not entirely sure this is right, but it works so far:

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1OrderPlanItemGroupCounter LineDescpMaterialMaterial DescQtyUnit115520OrderPlanItemGroupCounterLineDescpMaterialMaterial DescQtyUnit
21201201PL001201101GREL012130ENGINE115520GEAR3PC1201201PL001201101GREL012130ENGINE115520GEAR3PC
31201202PL001202102GREL013330ENGINE115521OIL3PC1201221PL001221121GREL0322230ENGINE115520GEAR3PC
41201203PL001203103GREL0142430ENGINE115522GEAR2PC
51201204PL001204101GREL012130ENGINE115520GEAR2PC
61201205PL001205105GREL0162110ENGINE115524GEAR0PC
71201206PL001206106GREL0172230ENGINE115525OIL3PC
81201207PL001207107GREL018530ENGINE115526GEAR3PC
91201208PL001208108GREL019730ENGINE115527OIL3PC
101201209PL001209109GREL013330ENGINE115521GEAR3PC
111201210PL001210110GREL0211830ENGINE115529OIL3PC
121201211PL001211111GREL0221830ENGINE115530GEAR3PC
131201212PL001212112GREL0232430ENGINE115531OIL3PC
141201213PL001213113GREL0242430ENGINE115532GEAR0PC
151201214PL001214114GREL0251930ENGINE115533OIL3PC
161201215PL001215115GREL0251930ENGINE115534GEAR0PC
171201216PL001216116GREL0271630ENGINE115535OIL3PC
181201217PL001217117GREL0281630ENGINE115536GEAR0PC
191201218PL001218118GREL0291630ENGINE115537OIL3PC
201201219PL001219119GREL0301630ENGINE115538GEAR3PC
211201220PL001220120GREL0312230ENGINE115539OIL0PC
221201221PL001221121GREL0322230ENGINE115520GEAR3PC
231201222PL001222122GREL033530ENGINE115541OIL3PC
241201223PL001223123GREL034530ENGINE115542GEAR2PC
251201224PL001224124GREL035630ENGINE115543OIL3PC
261201225PL001225125GREL036630ENGINE115544GEAR3PC
271201226PL001226126GREL0372330ENGINE115545OIL3PC
281201227PL001227127GREL0382330ENGINE115546GEAR0PC
291201228PL001228128GREL039730ENGINE115547OIL3PC
301201229PL001229129GREL040730ENGINE115548GEAR0PC
311201230PL001230130GREL0411830ENGINE115549OIL3PC
321201231PL001231131GREL0421830ENGINE115550GEAR0PC
331201232PL001232132GREL0431830ENGINE115551OIL3PC
Sheet2
Cell Formulas
RangeFormula
N2:X3N2=FILTER(A2:K33,IFERROR(MATCH(D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&M1,D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&H2:H33,0)=SEQUENCE(ROWS(D2:D33)),0))
Dynamic array formulas.
Click to expand...
it was indeed tricky one but this formula works like a charm!! Thanks a lot ! I tried to solve the steps in the formula but I was lost after MATCH part.
ROWS(D2:D33)=32
SEQUENCE(32)=Number of rows to return
MATCH()=H2;H5;H22
FILTER(A2:K33;H2=SEQUENCE(32) <------- this part was a bit confusing :)
Also in the previous formula =INDEX(UNIQUE(FILTER(A2:E6,A2:A6=G1)),SEQUENCE(ROWS(UNIQUE(FILTER(A2:E6,A2:A6=G1)))),{2,3,5})
{2,3,5} was listing the values not column bu column but row by row strange righ?
Anyway, thanks again for this perfect formula (y)
 
Upvote 0
One more question regarding =FILTER(A2:K33,IFERROR(MATCH(D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&M1,D2:D33&"|"&E2:E33&"|"&F2:F33&"|"&H2:H33,0)=SEQUENCE(ROWS(D2:D33)),0))
If the data evaluated by this formula is a result of filter function, in this case ranges will always change, In this meaning is it possible to apply variable range to this function instead D2:D33?
I ask this because this data D2:D33 was filtered using by filter function from another sheet based on the material number. Thanks again!
 
Upvote 0
Here's how the formula works. SEQUENCE(ROWS(D2:D33)) just creates an array from 1 to the number of rows in the data {1;2;3;4;5 ...}. Then we create a key for each row in the table, consisting of the D, E, F columns, plus the material number. Then we search the table (MATCH) for that key in columns D,E,F, and H. So for row 1 of the data (row 2 of the spreadsheet), we create GREL012|1|30|115520, and we find it on row 1 of the data. 1 = 1 (from the SEQUENCE array), so that gives us TRUE and we keep the row. The row 2 key is GREL013|3|30|115520, and we don't find it anywhere. So MATCH gives an error, and the IFERROR changes it to 0, or don't keep it. Row 3 is also not found. Row 4 gives us the key GREL012|1|30|115520 (which is the same as row 1). Then the MATCH gives us 1 because it finds it on the first row. 1 <> 4 (from the SEQUENCE array), so this returns FALSE, and the FILTER will not keep it.

I experimented quite a bit before I settled on this. I thought it was interesting that it's a UNIQUE formula that doesn't use the UNIQUE function.

As far as the variable range goes, if the range is based on a SPILL formula, then you can do it like this:

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1OrderPlanItemGroupCounter LineDescpMaterialMaterial DescQtyUnit115520OrderPlanItemGroupCounterLineDescpMaterialMaterial DescQtyUnit
21201201PL001201101GREL012130ENGINE115520GEAR3PC1201201PL001201101GREL012130ENGINE115520GEAR3PC
31201202PL001202102GREL013330ENGINE115521OIL3PC1201221PL001221121GREL0322230ENGINE115520GEAR3PC
41201203PL001203103GREL0142430ENGINE115522GEAR2PC
51201204PL001204101GREL012130ENGINE115520GEAR2PC
61201205PL001205105GREL0162110ENGINE115524GEAR0PC
71201206PL001206106GREL0172230ENGINE115525OIL3PC
81201207PL001207107GREL018530ENGINE115526GEAR3PC
91201208PL001208108GREL019730ENGINE115527OIL3PC
101201209PL001209109GREL013330ENGINE115521GEAR3PC
111201210PL001210110GREL0211830ENGINE115529OIL3PC
121201211PL001211111GREL0221830ENGINE115530GEAR3PC
131201212PL001212112GREL0232430ENGINE115531OIL3PC
141201213PL001213113GREL0242430ENGINE115532GEAR0PC
151201214PL001214114GREL0251930ENGINE115533OIL3PC
161201215PL001215115GREL0251930ENGINE115534GEAR0PC
171201216PL001216116GREL0271630ENGINE115535OIL3PC
181201217PL001217117GREL0281630ENGINE115536GEAR0PC
191201218PL001218118GREL0291630ENGINE115537OIL3PC
201201219PL001219119GREL0301630ENGINE115538GEAR3PC
211201220PL001220120GREL0312230ENGINE115539OIL0PC
221201221PL001221121GREL0322230ENGINE115520GEAR3PC
231201222PL001222122GREL033530ENGINE115541OIL3PC
241201223PL001223123GREL034530ENGINE115542GEAR2PC
251201224PL001224124GREL035630ENGINE115543OIL3PC
261201225PL001225125GREL036630ENGINE115544GEAR3PC
271201226PL001226126GREL0372330ENGINE115545OIL3PC
281201227PL001227127GREL0382330ENGINE115546GEAR0PC
291201228PL001228128GREL039730ENGINE115547OIL3PC
301201229PL001229129GREL040730ENGINE115548GEAR0PC
311201230PL001230130GREL0411830ENGINE115549OIL3PC
321201231PL001231131GREL0421830ENGINE115550GEAR0PC
331201232PL001232132GREL0431830ENGINE115551OIL3PC
Sheet2
Cell Formulas
RangeFormula
A2:K33A2=AA2:AK33
N2:X3N2=FILTER(A2#,IFERROR(MATCH(INDEX(A2#,0,4)&"|"&INDEX(A2#,0,5)&"|"&INDEX(A2#,0,6)&"|"&M1,INDEX(A2#,0,4)&"|"&INDEX(A2#,0,5)&"|"&INDEX(A2#,0,6)&"|"&INDEX(A2#,0,8),0)=SEQUENCE(ROWS(A2#)),0))
Dynamic array formulas.


You'll have to identify the columns you want to check using INDEX and a column number.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Unique and Filter to select two or more different options from the same list
Replies
7
Views
108
Peter Davison
P
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
120
myall_blues
myall_blues
OvernightCellebrity
  • Solved
Unique + Filter?
2
Replies
11
Views
368
OvernightCellebrity
OvernightCellebrity
P
  • Question
Using Unique,Filter & ChooseCols and to see if I can use SUMIF with it?
2 3
Replies
22
Views
674
Peter Davison
P
SanjayGMusafir
  • Solved
Filter Unique Values out of a Multi-Column List
Replies
7
Views
375
Fluff
Fluff

Forum statistics

Threads
1,214,599
Messages
6,120,447
Members
448,966
Latest member
DannyC96

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top