Return a unique list of values based on a variable condition

E

ElBB23

New Member
Joined
Feb 10, 2017
Messages
26
Hi all,

I have tried a couple of formulas for this problem but the "variable" part is causing an issue.

I have two columns with variable data, duplicated data in. I need a formula to look at one of these columns and pull through a unique list (i.e only one of each value) based on a semi-variable criteria for the other column.


So, the semi-variable criteria will always start the same but, could be slightly different at the end. I have tried using a wildcard i.e "Test 1*" to indicate the start of the text is fixed but not the end but, it doesn't seem to work. I've attached the table below as an example.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Condition: "Test 1*"[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Test 1 - Other[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Test 1 - Other[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 1 - Other[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2 - Other [/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be very much appreciated.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Column1​
[/td][td]
Column 2​
[/td][td][/td][td][/td][td]
Begins with​
[/td][td]
List​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
Test 1​
[/td][td]
A​
[/td][td][/td][td][/td][td]
Test 1​
[/td][td]
A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Test 1​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td]
B​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Test 1 - Other​
[/td][td]
A​
[/td][td][/td][td][/td][td][/td][td]
C​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
Test 1 - Other​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
Test 2​
[/td][td]
A​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
Test 2​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
Test 1​
[/td][td]
C​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Test 1​
[/td][td]
A​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
Test 2​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
Test 1 - Other​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
Test 2​
[/td][td]
A​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
Test 2 - Other​
[/td][td]
B​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2 copied down
=IFERROR(INDEX(B$2:B$13,SMALL(IF(FREQUENCY(IF(LEFT(A$2:A$13,LEN(E$2))=E$2,MATCH(B$2:B$13,B$2:B$13,0)),ROW(A$2:A$13)-ROW(A$2)+1),ROW(A$2:A$13)-ROW(A$2)+1),ROWS(F$2:F2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0
Hi,

Thanks for getting back to me and apologies for the delay the formula works perfectly. Just another query, I apologise if I need to start a new thread and I can but, could I add another condition to the formula. I have tried the below but it is coming back blank.

=IFERROR(INDEX(B$2:B$13,SMALL(IF(FREQUENCY(IF((LEFT(A$2:A$13,LEN(E$2))=E$2,)*(D$2:D$13=F$5),MATCH(B$2:B$13,B$2:B$13,0)),ROW(A$2:A$13)-ROW(A$2)+1),ROW(A$2:A$13)-ROW(A$2)+1),ROWS(F$2:F2))),"")​

[TABLE="class: cms_table_grid, width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 10.06px; margin-bottom: 0px;">[TR]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Column1​
[/TD]
[TD]
Column 2​
[/TD]
[TD]Column 3[/TD]
[TD][/TD]
[TD]
Begins with​
[/TD]
[TD]
List​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]Condition2[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]One[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
C​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
B​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Test 2 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

Thanks for the reply and sorry about the delay in getting back to you. The formula worked perfectly, just a quick question. Would I be able to add another variable to this?

I have tried the below but, it is not working:-
=IFERROR(INDEX(B$2:B$13,SMALL(IF(FREQUENCY(IF((LEFT(A$2:A$13,LEN(E$2))=E$2)*(C$2:C$13=F$5),MATCH(B$2:B$13,B$2:B$13,0)),ROW(A$2:A$13)-ROW(A$2)+1),ROW(A$2:A$13)-ROW(A$2)+1),ROWS(F$2:F2))),"")

I apologise if I need to start another thread and will do if required :)
​

[TABLE="class: cms_table_grid, width: 0"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Column1​
[/TD]
[TD]
Column 2​
[/TD]
[TD]Column 3[/TD]
[TD][/TD]
[TD]
Begins with​
[/TD]
[TD]
List​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]Condition 2[/TD]
[TD]

​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD]One[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
C​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
A​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
B​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Test 1 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Test 2​
[/TD]
[TD]
A​
[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Test 2 - Other​
[/TD]
[TD]
B​
[/TD]
[TD]Onw[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try

Array formula in G2 copied down
=IFERROR(INDEX(C$2:C$13,SMALL(IF(FREQUENCY(IF((LEFT(B$2:B$13,LEN(F$2))=F$2)*(D$2:D$13=F$5),MATCH(C$2:C$13,C$2:C$13,0)),ROW(C$2:C$13)-ROW(C$2)+1),ROW(C$2:C$13)-ROW(C$2)+1),ROWS(G$2:G2))),"")
Ctrl+Shift+Enter

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
I
  • Solved
FILTER formula criteria based of a variable with VBA
Replies
2
Views
277
ItalianPlatinum
I
J
  • Question Question
Newer user looking to link multiple tables that autopopulate data based on results
Replies
5
Views
84
Jaytam49
J
K
  • Solved
Use 'Unique', 'Filter' and 'If' statements.
Replies
7
Views
271
Fluff
Fluff
T
  • Question Question
Select from a term list, but input the corresponding key
Replies
1
Views
164
sijpie
sijpie

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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