Return a unique list of values based on a variable condition

E

ElBB23

New Member
Joined
Feb 10, 2017
Messages
28
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
295
Cubist
Cubist
I
  • Solved
FILTER formula criteria based of a variable with VBA
Replies
2
Views
320
ItalianPlatinum
I
J
  • Question Question
Newer user looking to link multiple tables that autopopulate data based on results
Replies
5
Views
127
Jaytam49
J
I
  • Solved
In Excell VBA the set variable change without clear reason
Replies
5
Views
131
Micron
M
K
  • Solved
Use 'Unique', 'Filter' and 'If' statements.
Replies
7
Views
709
Fluff
Fluff

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

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