Return all unique values matching 2 criteria but where the data is in multiple columns!

Hi All,

I was just wondering if it is possible to return all unique values matching 2 criteria but where the data from which the unique values are to be taken is across multiple columns??!

Please see the attached spreadsheet which hopefully shows what I am trying to do.

I do have the following formula which returns a list of all unique values (in this case ‘Work Types’) from range E5:N16

=IFERROR(INDEX(AJ_WorkTypes, MIN(IF(COUNTIF(F$5:$F5, AJ_WorkTypes)=0, ROW(AJ_WorkTypes)-MIN(ROW(AJ_WorkTypes))+1)), MATCH(0, COUNTIF(F$5:$F5, INDEX(AJ_WorkTypes, MIN(IF(COUNTIF(F$5:$F5, AJ_WorkTypes)=0, ROW(AJ_WorkTypes)-MIN(ROW(AJ_WorkTypes))+1)), , 1)), 0), 1),"")

However I would like to tweak it or have a another formula that will return ONLY those Work Types that match Client in column C and Job in column D on the data table.

All help will be gratefully received.

Oliver

P.s. The above formula is stubbornly listing a zero where it finds blanks so if this can also be improved that will be good too.

Thank you very much!

Thank you avk.

I was hoping for a formula to enter in H6 in 'Formulas I am trying to work' sheet and copy down to H25 that would do the job in a single calculation (i.e. no helper tables / columns).

Although I could do a helper table as you suggest if this is the only way.

Do you think a single formula in H6 is possible?


Thank you very much.

Sorry but this is not solved.

Hi Dave,


Sorry not replied soon. Partly down to time difference and partly down to being at work today!

Thank you so much for doing this.

This is VERY impressive to say the least. Wish I had those Excel powers!!


The first solution is perfect. I did't now Name Manager could be used for packets of formula. I will try and dissect what it is going on here (but suspect I wont get that far!).

The second solution is also ingenious. I probably would have made a separate table to do a first stage filter and then used my existing 'find all unique values' formula to get down to the final list of unique values. Your helper column solution uses some formulas / methods i'm not familiar with.

I take it the formulas in F18 and G18 on the 'Table of Data' tab were from your workings and superfluous to requirements now?


I will now try and copy / adapt these formulas over to my actual spread sheet (with private data) and try to get them to work and in the process hopefully will start to understand some of the logic - if not the actual mechanics.


Thank you very much again!


Oliver

P.s. yes 'Amin' was a typo for 'Admin'