Hello friends, Hope all is well with you all!
The attachment has a sheet. the sheet has a database with information.
I kindly request that you please help me with a formula to show the results just like it is displayed in cells H2:H4.
The results that I am getting from the formula that I have is displayed in G2:G4; which is wrong.
i.e. I wish to please see unique names (without repetition) based on the conditions and the combinations of the cells F2:F4.
thank you so much!
Hope this works, copy this on G2
=INDEX($C$2:$C$16;SMALL(IF($A$2:$A$16=LEFT($F2;SEARCH(" ";$F2;1)-1);ROW($C$2:$C$16)-ROW($C$2)+1);COUNTIF($F$2:F2;F2)))
then press F2 button to open the formula, then enter with press all together CTRL+SHIFT+ENTER button, if succes will show curly bracket that enclosed the formula "{formula}" (array formula type)
last step copied down as necessary
Thank you very much, but the formula still needs a slight adjustment. Please open the attachment and apply the formula that you suggested on cell F2:F4 (btw I did apply CTRL+SHIFT+ENTER button). And change the cell C3 to the word CAR.
The results should be CAR BIKE BUS. But, instead I am getting CAR CAR BUS.
I am hoping to only get the unique names please, thank a lot!
This array formula** entered in H2:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(C$2:C$16,MATCH(0,IF(A$2:A$16&" "&B$2:B$16=F$2,COUNTIF(H$1:H1,C$2:C$16)),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.
Thank you so much Tony, but please note that ranges of column H, have nothing to do with formula. I want to please show the unique results based on the columns A, C, and F. Really appreciate it!
I am referring to the last part of the formula that you provided: COUNTIF(H$1:H1,C$2:C$16)),0))))
thanks
Last edited by countryfan_nt; 08-12-2016 at 08:07 PM.
In your sample file column H is where you show us what results you expect so I assumed that's where you want the formulas to go. If that's not where you want the formulas to go then tell us where.
Here's your file with the formula implemented.
Last edited by Tony Valko; 08-12-2016 at 08:43 PM.
I apologize, I miss understood. thank you, I thought that column H was a database, but in fact it is where the formula is placed!
thank you very much!
You're welcome. Thanks for the feedback!
Hello, and sorry to bother you again!
I am now trying to connect 3 different scenarios; Please help, because I keep getting FALSE. I want to connect the following 3 possibilities (Yes, I am applying the { } to the combined formulas:
A.
=IF(P9="Nursing",IF(COUNTIFS('Approved Salary - Nursing'!$I$3:$I$363,O9,'Approved Salary - Nursing'!$B$3:$B$363,R9),MIN((IF('Approved Salary - Nursing'!$B$3:$B$363=R9,IF('Approved Salary - Nursing'!$I$3:$I$363=O9,IF('Approved Salary - Nursing'!$J$3:$J$363>0,'Approved Salary - Nursing'!$J$3:$J$363),
( WITH )
B.
=IF(OR(P10="Administrative & Support Service",P10="Paramedical"),IF(COUNTIFS('Approved Salary Admin&Paramedic'!$C$3:$C$1330,'INPUT SHEET'!R10,'Approved Salary Admin&Paramedic'!$H$3:$H$1330,'INPUT SHEET'!O10),MIN(IF('Approved Salary Admin&Paramedic'!$B$3:$B$1330=R10,IF('Approved Salary Admin&Paramedic'!$I$3:$I$1330>0,'Approved Salary Admin&Paramedic'!$I$3:$I$1330)))))
if A & B equal FALSE then result will be: "NO MATCH"
sorry & thank you very much!
The Pivot table was invented for this............
Kind regards,
Piet Bom
I would have to see the file to put that all in context.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks