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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

I
  • Solved
FILTER formula criteria based of a variable with VBA
Replies
2
Views
274
ItalianPlatinum
I
J
  • Question Question
Newer user looking to link multiple tables that autopopulate data based on results
Replies
5
Views
78
Jaytam49
J
T
  • Question Question
Select from a term list, but input the corresponding key
Replies
1
Views
148
sijpie
sijpie
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
119
jkpieterse
jkpieterse
B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
320
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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