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]
 
Sort by date Sort by votes
If data in A-column are the dates (actually the numbers) in USA format,
the Advanced Filter can be used:

Criteria range and the data range before filtering:
Excel Workbook
ABC
1DateDateColor
2>=15-03-12Black
3
4DateColorValues
515-Mar-12BluePQR
614-Feb-12GreenABC
720-Apr-12BlackPQR
828-Jan-12WhiteXYZ
924-Jan-12BlueABC
101-Mar-12YellowXYZ
1120-Jan-12BlackXYZ
124-May-12OrangeABC
136-Mar-12RedXYZ
1429-Jan-12YellowXYZ
155-Apr-12BlueABC
1616-Feb-12WhitePQR
1715-May-12YellowABC
1810-Feb-12BlueXYZ
1911-Feb-12OrangePQR
2013-Jun-12BlackPQR
2128-Apr-12BlackABC
2224-May-12RedXYZ
2310-Jun-12OrangePQR
249-Mar-12WhitePQR
Sheet


The result after applying of advanced filter:
Excel Workbook
ABC
4DateColorValues
720-Apr-12BlackPQR
2128-Apr-12BlackABC
Sheet
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
ZVI said:
If data in A-column are the dates (actually the numbers) in USA format,
the Advanced Filter can be used:

Criteria range and the data range before filtering:
Click to expand...

Thanks for the suggestion, but in my case I can't use the advanced filter. Actually its a kind of dashboard, usually I sent out to some targeted audience. Needless to say, I can't explain them how the advanced filter works. Hence I am looking up for formula/functions.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

Totally agreed with you, but due to the fixed structure of my dashboard, can't use the Pivot.
 
Upvote 0
scorpio_libra said:
Thanks for the suggestion, but in my case I can't use the advanced filter. Actually its a kind of dashboard, usually I sent out to some targeted audience. Needless to say, I can't explain them how the advanced filter works. Hence I am looking up for formula/functions.
Click to expand...
Try this...

Data:

Sheet1

*ABC
Black
Blue
White
Yellow
Blue
Orange
Green
White
Yellow
Red
White
Blue
Blue
Black
Black
Orange
Yellow
Red
Orange
Black

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 81px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Color[/TD]
[TD="align: center"]Values[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1/20/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1/24/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1/28/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]1/29/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2/10/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2/11/2012[/TD]

[TD="align: center"]PQR[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2/14/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]2/16/2012[/TD]

[TD="align: center"]PQR[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]3/1/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]3/6/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]3/9/2012[/TD]

[TD="align: center"]PQR[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]3/15/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]4/5/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]4/20/2012[/TD]

[TD="align: center"]ZZZ[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]4/28/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]5/4/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]5/15/2012[/TD]

[TD="align: center"]ABC[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]5/24/2012[/TD]

[TD="align: center"]XYZ[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]6/10/2012[/TD]

[TD="align: center"]PQR[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]6/13/2012[/TD]

[TD="align: center"]PQR[/TD]

</TBODY>



Summary:

Book1
EFGHI
1FromToColorCountValues
21/20/20124/30/2012Black3XYZ
3____ZZZ
4____ABC
5_____
Sheet1

Enter this array formula** in H2. This will return the count of records that meet the criteria.

=SUM(IF(FREQUENCY(IF(A2:A21>=E2,IF(A2:A21<=F2,IF(B2:B21=G2,MATCH(C2:C21,C2:C21,0)))),ROW(C2:C21)-ROW(C2)),1))

Enter this array formula** in I2. This will extract the unique items.

=IF(ROWS(I$3:I3)>H$2,"",INDEX(C$2:C$21,SMALL(IF(A$2:A$21>=E$2,IF(A$2:A$21<=F$2,IF(B$2:B$21=G$2,IF(MATCH(B$2:B$21&C$2:C$21,B$2:B$21&C$2:C$21,0)=ROW(C$2:C$21)-ROW(C$2)+1,ROW(C$2:C$21)-ROW(C$2)+1)))),ROWS(I$3:I3))))

Copy down until you get blanks.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This may take a few seconds to calculate on 20k rows of data.
 
Upvote 0
For only unique items with usage of aux formulas in D-column (can be in hidden column as well):
Excel Workbook
ABCDEFG
1DateColorValuesCriteria
215-Mar-12BluePQRFALSEFrom DateTo DateColor
314-Feb-12GreenABCFALSE15-Mar-1230-Apr-12Black
420-Apr-12BlackPQRTRUE
528-Jan-12WhiteXYZFALSEUnique Values
624-Jan-12BlueABCFALSEPQR
71-Mar-12YellowXYZFALSEABC
820-Jan-12BlackXYZFALSE
94-May-12OrangeABCFALSE
106-Mar-12RedXYZFALSE
1129-Jan-12YellowXYZFALSE
125-Apr-12BlueABCFALSE
1316-Feb-12WhitePQRFALSE
1415-May-12YellowABCFALSE
1510-Feb-12BlueXYZFALSE
1611-Feb-12OrangePQRFALSE
1713-Jun-12BlackPQRFALSE
1828-Apr-12BlackABCTRUE
1924-May-12RedXYZFALSE
2010-Jun-12OrangePQRFALSE
219-Mar-12WhitePQRFALSE
Sheet
 
Last edited:
Upvote 0
Correction: $I$2:$I$21 have to be replaced by $D$2:$D$21 in E6 formula (actually I-column was my hidden one with formula shown for D-column)
But Tony's formula is much better! :cool:
 
Upvote 0
T. Valko said:
.
Click to expand...

Hi, Thanks for the help.

Formula is giving the exact unique extract for few of the values (extracting the incomplete list) and after that I am getting a #NUM error. could you please let me know if I am doing something weird.

{=IF(ROWS(B$42:B42)>$F$2,"",INDEX(Values,SMALL(IF(IssueDate>=$C$3,IF(IssueDate<=$D$3,IF(Color=$B$3,IF(MATCH(Color&Values,Color&Values,0)=ROW(Values)-ROW(Sheet1!$J$2)+1,ROW(Values)-ROW(Sheet1!$J$2)+1)))),ROWS(B$42:B42))))}

I am entering above formula in B41.
$F$2 = UniqueCount Cell, where I have entered the Sum,If & frequency nesting as mentioned by you, even getting the proper unique count
$C$3 = FromDate input cell (Start date)
$D$3 = ToDate input cell (End Date)
$B$3 = Color criteria input cell
Sheet1!$J$2 = very first data cell of the Value Column.
have named my ranges Value as Values Date as IssueDate Color as Color
 
Upvote 0
scorpio_libra said:
Hi, Thanks for the help.

Formula is giving the exact unique extract for few of the values (extracting the incomplete list) and after that I am getting a #NUM error. could you please let me know if I am doing something weird.

{=IF(ROWS(B$42:B42)>$F$2,"",INDEX(Values,SMALL(IF(IssueDate>=$C$3,IF(IssueDate<=$D$3,IF(Color=$B$3,IF(MATCH(Color&Values,Color&Values,0)=ROW(Values)-ROW(Sheet1!$J$2)+1,ROW(Values)-ROW(Sheet1!$J$2)+1)))),ROWS(B$42:B42))))}

I am entering above formula in B41.
$F$2 = UniqueCount Cell, where I have entered the Sum,If & frequency nesting as mentioned by you, even getting the proper unique count
$C$3 = FromDate input cell (Start date)
$D$3 = ToDate input cell (End Date)
$B$3 = Color criteria input cell
Sheet1!$J$2 = very first data cell of the Value Column.
have named my ranges Value as Values Date as IssueDate Color as Color
Click to expand...
I'll put together a sample file.

What version of Excel are you using?
 
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,954
Messages
6,175,601
Members
452,658
Latest member
GStorm

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