Pull list of unique values based on multiple criterias

S

scorpio_libra

Board Regular
Joined
Mar 30, 2009
Messages
52
Extract unique values with multiple criteria</SPAN>
I have approx 20,000 record in an excel sheet, wanted to pull the unique values of D column based on the three different criteria.
</SPAN>Date= B Column
Color= C Column
Values= D Column

1st criteria = From Date = Cell F4
2nd criteria = To Date = </SPAN>Cell G4
3rd criteria = Color = </SPAN>Cell I3

I tried lots of possible combinations of formula (below listed for your reference), however nothing worked.</SPAN>
=INDEX(iValue,(iDate>=$F$4)*(iDate<=$G$4)*(Color=$I$3)*(MATCH(0,COUNTIF($E$2:E2,iValue),0)))</SPAN>
Any possible helps would be highly appreciated. Due to security reasons there is strict no no to macro.</SPAN>
Hence I want the answers in formula.</SPAN>


[TABLE="width: 279"]
<TBODY>[TR]
[TD]Date</SPAN>
[/TD]
[TD]Color</SPAN>
[/TD]
[TD]values</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15-Mar-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14-Feb-12</SPAN>
[/TD]
[TD]Green</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20-Apr-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]28-Jan-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]24-Jan-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-Mar-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20-Jan-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4-May-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]6-Mar-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jan-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5-Apr-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15-May-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10-Feb-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]11-Feb-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]13-Jun-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]28-Apr-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]24-May-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10-Jun-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9-Mar-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9-May-12</SPAN>
[/TD]
[TD]Green</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Excel Workbook
ABCDEFGHI
1DateColorvalues
215-Mar-12BluePQR148unique combination
314-Feb-12GreenABC127unique combination
420-Apr-12BlackPQR148unique combination
528-Jan-12WhiteXYZ138unique combination
624-Jan-12BlueABC147unique combination
71-Mar-12YellowXYZ138unique combination
820-Jan-12BlackXYZ148unique combination
94-May-12OrangeABC137unique combination
106-Mar-12RedXYZ148unique combination
1129-Jan-12YellowXYZ138unique combination
125-Apr-12BlueABC147unique combination
1316-Feb-12WhitePQR138unique combination
1415-May-12YellowABC137unique combination
1510-Feb-12BlueXYZ148unique combination
1611-Feb-12OrangePQR138unique combination
1713-Jun-12BlackPQR148unique combination
1828-Apr-12BlackABC147unique combination
1924-May-12RedXYZ148unique combination
2010-Jun-12OrangePQR138unique combination
219-Mar-12WhitePQR138unique combination
227-Jan-12RedXYZ148unique combination
2326-Feb-12RedPQR148unique combination
249-May-12GreenABC127unique combination
Blad1
Cell Formulas
RangeFormula
E2=COUNTIF(A$2:A$24,A2)
E3=COUNTIF(A$2:A$24,A3)
E4=COUNTIF(A$2:A$24,A4)
E5=COUNTIF(A$2:A$24,A5)
E6=COUNTIF(A$2:A$24,A6)
E7=COUNTIF(A$2:A$24,A7)
E8=COUNTIF(A$2:A$24,A8)
E9=COUNTIF(A$2:A$24,A9)
E10=COUNTIF(A$2:A$24,A10)
E11=COUNTIF(A$2:A$24,A11)
E12=COUNTIF(A$2:A$24,A12)
E13=COUNTIF(A$2:A$24,A13)
E14=COUNTIF(A$2:A$24,A14)
E15=COUNTIF(A$2:A$24,A15)
E16=COUNTIF(A$2:A$24,A16)
E17=COUNTIF(A$2:A$24,A17)
E18=COUNTIF(A$2:A$24,A18)
E19=COUNTIF(A$2:A$24,A19)
E20=COUNTIF(A$2:A$24,A20)
E21=COUNTIF(A$2:A$24,A21)
E22=COUNTIF(A$2:A$24,A22)
E23=COUNTIF(A$2:A$24,A23)
E24=COUNTIF(A$2:A$24,A24)
F2=COUNTIF(B$2:B$24,B2)
F3=COUNTIF(B$2:B$24,B3)
F4=COUNTIF(B$2:B$24,B4)
F5=COUNTIF(B$2:B$24,B5)
F6=COUNTIF(B$2:B$24,B6)
F7=COUNTIF(B$2:B$24,B7)
F8=COUNTIF(B$2:B$24,B8)
F9=COUNTIF(B$2:B$24,B9)
F10=COUNTIF(B$2:B$24,B10)
F11=COUNTIF(B$2:B$24,B11)
F12=COUNTIF(B$2:B$24,B12)
F13=COUNTIF(B$2:B$24,B13)
F14=COUNTIF(B$2:B$24,B14)
F15=COUNTIF(B$2:B$24,B15)
F16=COUNTIF(B$2:B$24,B16)
F17=COUNTIF(B$2:B$24,B17)
F18=COUNTIF(B$2:B$24,B18)
F19=COUNTIF(B$2:B$24,B19)
F20=COUNTIF(B$2:B$24,B20)
F21=COUNTIF(B$2:B$24,B21)
F22=COUNTIF(B$2:B$24,B22)
F23=COUNTIF(B$2:B$24,B23)
F24=COUNTIF(B$2:B$24,B24)
G2=COUNTIF(C$2:C$24,C2)
G3=COUNTIF(C$2:C$24,C3)
G4=COUNTIF(C$2:C$24,C4)
G5=COUNTIF(C$2:C$24,C5)
G6=COUNTIF(C$2:C$24,C6)
G7=COUNTIF(C$2:C$24,C7)
G8=COUNTIF(C$2:C$24,C8)
G9=COUNTIF(C$2:C$24,C9)
G10=COUNTIF(C$2:C$24,C10)
G11=COUNTIF(C$2:C$24,C11)
G12=COUNTIF(C$2:C$24,C12)
G13=COUNTIF(C$2:C$24,C13)
G14=COUNTIF(C$2:C$24,C14)
G15=COUNTIF(C$2:C$24,C15)
G16=COUNTIF(C$2:C$24,C16)
G17=COUNTIF(C$2:C$24,C17)
G18=COUNTIF(C$2:C$24,C18)
G19=COUNTIF(C$2:C$24,C19)
G20=COUNTIF(C$2:C$24,C20)
G21=COUNTIF(C$2:C$24,C21)
G22=COUNTIF(C$2:C$24,C22)
G23=COUNTIF(C$2:C$24,C23)
G24=COUNTIF(C$2:C$24,C24)
I2=IF(MIN(E2:G2)=1,"unique combination","false")
I3=IF(MIN(E3:G3)=1,"unique combination","false")
I4=IF(MIN(E4:G4)=1,"unique combination","false")
I5=IF(MIN(E5:G5)=1,"unique combination","false")
I6=IF(MIN(E6:G6)=1,"unique combination","false")
I7=IF(MIN(E7:G7)=1,"unique combination","false")
I8=IF(MIN(E8:G8)=1,"unique combination","false")
I9=IF(MIN(E9:G9)=1,"unique combination","false")
I10=IF(MIN(E10:G10)=1,"unique combination","false")
I11=IF(MIN(E11:G11)=1,"unique combination","false")
I12=IF(MIN(E12:G12)=1,"unique combination","false")
I13=IF(MIN(E13:G13)=1,"unique combination","false")
I14=IF(MIN(E14:G14)=1,"unique combination","false")
I15=IF(MIN(E15:G15)=1,"unique combination","false")
I16=IF(MIN(E16:G16)=1,"unique combination","false")
I17=IF(MIN(E17:G17)=1,"unique combination","false")
I18=IF(MIN(E18:G18)=1,"unique combination","false")
I19=IF(MIN(E19:G19)=1,"unique combination","false")
I20=IF(MIN(E20:G20)=1,"unique combination","false")
I21=IF(MIN(E21:G21)=1,"unique combination","false")
I22=IF(MIN(E22:G22)=1,"unique combination","false")
I23=IF(MIN(E23:G23)=1,"unique combination","false")
I24=IF(MIN(E24:G24)=1,"unique combination","false")
 
Upvote 0
My apology, seems was unable to explain the requirement. I want to pull the unique list of values which is in D column. Same way that we used to do it through advancd filter and selecting the unique check box, which I am doing it easily by using the nesting of index, match and countif array formula. Howevr to make it more dynamic, here the challenge is, whenevr I change the date in date criteria cell or change the color in colors criteria cell. The unique list of values should change accordingly.


Can any 1 let me know how to upload the screenshot here?
 
Upvote 0
In my solution you can select a filter on collumn I.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer. <o:p></o:p>

​
 
Upvote 0
[TABLE="class: grid, width: 571, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Color[/TD]
[TD]Values[/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Criteria[/TD]
[/TR]
[TR]
[TD]15-Mar-12[/TD]
[TD]Blue[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="align: center"]From Date[/TD]
[TD="align: center"]To date[/TD]
[TD="align: center"]Color[/TD]
[/TR]
[TR]
[TD]14-Feb-12[/TD]
[TD]Green[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="align: right"]05-Apr-12[/TD]
[TD="align: right"]30-Apr-12[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]20-Apr-12[/TD]
[TD]Black[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28-Jan-12[/TD]
[TD]White[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"]Unique Values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24-Jan-12[/TD]
[TD]Blue[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="colspan: 2"]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01-Mar-12[/TD]
[TD]Yellow[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"]PQR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20-Jan-12[/TD]
[TD]Black[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-May-12[/TD]
[TD]Orange[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-Mar-12[/TD]
[TD]Red[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-Jan-12[/TD]
[TD]Yellow[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-Apr-12[/TD]
[TD]Blue[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16-Feb-12[/TD]
[TD]White[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15-May-12[/TD]
[TD]Yellow[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-Feb-12[/TD]
[TD]Blue[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-Feb-12[/TD]
[TD]Orange[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13-Jun-12[/TD]
[TD]Black[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28-Apr-12[/TD]
[TD]Black[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24-May-12[/TD]
[TD]Red[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-Jun-12[/TD]
[TD]Orange[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09-Mar-12[/TD]
[TD]White[/TD]
[TD]PQR[/TD]
[TD][/TD]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="3"></colgroup>[/TABLE]

Till date I was using the IFERROR(INDEX(Values,MATCH(0,COUNTIF($E$5:E5,Values),0)),"") in an array form, to get the unique list of values which is in 3rd Column i.e. ABC, XYZ, PQR etc. However as I mentioned earlier, my requirement ischanged, now I want to get the unique value based on the date and color criteria. If I change the FromDate or ToDate or Color cell, my unique list should changed accordingly.
 
Upvote 0
Oeldere said:
In my solution you can select a filter on collumn I.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer. <o:p></o:p>

​
Click to expand...

thanks for the help, have posted the data for your reference...
 
Upvote 0
scorpio_libra said:
Till date I was using the IFERROR(INDEX(Values,MATCH(0,COUNTIF($E$5:E5,Values),0)),"") in an array form, to get the unique list of values which is in 3rd Column i.e. ABC, XYZ, PQR etc. However as I mentioned earlier, my requirement ischanged, now I want to get the unique value based on the date and color criteria. If I change the FromDate or ToDate or Color cell, my unique list should changed accordingly.
Click to expand...

I have named my ranges, same as the data header, Date as Date, Color as Color and Value as Values.
 
Upvote 0
Hi,

With 20k registers, i think an array formula would seriously impair the performance. Particularly considering that the requirements demand a very complex formula.

What about a Pivot Table?
Date and Color as Page Fields
Values in Row Labels

You can use the Page Fields to filter the data accordingly with your needs.

In my opinion, easy to make and efficient.

M.
 
Upvote 0
Maybe easier and more efficient would be a parameterised query? Suggest google for examples.
 
Upvote 0
You must log in or register to reply here.

Similar threads

nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
348
Fluff
Fluff
S
  • Question Question
Latest Data Pull-up from the corresponding date-Single party
Replies
3
Views
126
Sergius
S
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
439
myall_blues
myall_blues
W
  • Solved
Usign the =sort function in a Dynamic Array to sort by Month
Replies
3
Views
558
Fluff
Fluff
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,654
Latest member
mememe101

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