Hello Team,
I want a formula to retrieve unique values from combination of 2 columns. Below is the screenshot.
Order # Part # Answer
1 p1 p1
1 p2 p3
2 p1 p4
3 p3
4 p2
5 p4
As you can see from the above screenshot, my requirement is to retrieve only p1, p3 and p4.
Really appreciate any help!!!
With data in columns A:B and headers in row 1 try array entering this formula in C2 and fill down until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.It works at my end.Formula:
Please Login or Register to view this content.
Dave
@ FlameRetired
Thank you so much it worked like a charm!!!
But when I enter p2 for Order 5 the formula does not return any value and it will be blank, any idea why is it so ?
@ FlameRetired
Thank you so much it worked like a charm!!!
But when I enter p2 for Order 5 the formula does not return any value and it will be blank, any idea why is it so ?
@ FlameRetired
One more thing, the screenshot what I had posted is just an example. Let me put the real example as it might give more understanding of the problem.
Order# Part # Answer
44384 4183682 4183682
42525 5095769 5095769
42525 5095769 4811673
44284 4811673
42780 5095769
As you can see from the above, 5095769 part# is repeating for multiple Order# hence we would be considering it only once. Here we always have to check if Part# which we consider has repeated for some other Order# then we will consider that Part# only once.
Hope I'm clear in explaining this.
Try this...
Data Range
This array formula** entered in C2:
=IFERROR(INDEX(B$2:B$6,MATCH(0,COUNTIF(C$1:C1,B$2:B$6),0)),"")
** 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.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@ Tony,
Thank you so much for the suggestion and this works great but when I change the Part # 5095770 for Order#42525 it gives me wrong results.
Below is the example:-
Order# Part # Answer
44384 4183682 4183682
42525 5095769 5095769
42525 5095770 5095770
44284 4811673 4811673
42780 5095769
Scenario:- If Order# 42525 has 2 different Part# (5095769 & 5095770) then we should consider only one Part# (5095769 or 5095770) and the considered Part# 5095769 can be repeating for some other Order# 42780 but we will consider that Part# only once.
so from the above example we should be expecting answer like:-
Answer
4183682
5095769
4811673
Hope this example gives a clear picture!!!
This seems to do what you want...
Data Range
This array formula** entered in C2:
=IFERROR(INDEX(B$2:B$6,SMALL(IF(FREQUENCY(MATCH(A$2:A$6,A$2:A$6,0),ROW(A$2:A$6)-ROW(A$2)+1),IF(COUNTIF(C$1:C1,B$2:B$6)=0,ROW(B$2:B$6)-ROW(B$2)+1)),1)),"")
** 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.
Copy down until you get blanks.
@ Tony,
You are brilliant, this works perfectly!!!
Meanwhile I tried to apply Vlookup formula which also works but not sure if itis right logic.
Thank you so much!!!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks