Help Consolidating Multiple Formulas in to 1 cell (No Helper Columns)

Please see the attached book.


I'm really struggling with this one. I can get the desired results using helper columns however I really need to get rid of the helper columns.


But I'm totally at a lost.


Maybe I'm just not going about this the right way. Please help

NO HELPER.xlsx

Not even one? my friend?

no not even 1 lol. i can get it down if i really push it to 1 but the formula was very complex and my head exploded (and i lost it when i shut down without saving as i didnt know what i was doing anymore)

hi twiggywales, try this array formula. dont feel very confident though. anyway, my assumption was that your list below is in accordance with the one above:

Please Login or Register  to view this content.

i also excluded an IFERROR. do add them if the formula is acceptable.

why the referance to L2? i see what your doing i think. im just working it all out now. this is sooo clever

dont worry i figured out why

so that if the result is the same it does the next line

My best for today! Too tired!

Maybe is a good start.....

Edit: Of course in another column, we can get the uniques names of column M.

there is however a problem if you change some of the values, see attached

it works but it wont just pull out the unique values (I may have been unclear with that sorry)


NO HELPER.xlsxV2

ok you have officially killed my brain cells from doing this. haha

Please Login or Register  to view this content.

@Fotis:
just wondering if you received my reply from your PM. a few people asked me questions & when i answered, there were no replies

Originally Posted by benishiryo

ok you have officially killed my brain cells from doing this. haha

Please Login or Register  to view this content.

@Fotis:
just wondering if you received my reply from your PM. a few people asked me questions & when i answered, there were no replies

Good morning.

NO. I didn't received any PM from you.

I thoquht that you just ignored my question.

I don't know what happened!

wow thats brillant benishiryo

couple of questions why are you matching
"~"&NameRange:NameRange&""

is it in case there are blanks in the array? (thats the only way i can think of)

im very unfamiliar with FREQUENCY so thanks for improving my understanding.

thanks to all here this seems to be solved.

there is 1 problem the if there is a blank i will return a 0 however there will never be a blank so dont worry, thanks guys.

@Fotis:

luckily i asked. i could have been mistaken as someone proud haha. saw your reply too. you're welcome~

@twiggywales:
i'm extremely unfamiliar with the FREQUENCY formula too. that's why my brain was exploding. from what i know, the tilde is just in case you have special characters like "*" in front. so instead of searching as a wildcard, it finds an asterisk with your text. the "" is to convert the range to texts. i might be wrong though

Edit: in your case, you probably dont need them

i am late to the game, but i thought this was such an interesting problem that i did not want to miss out on the fun... i had the benefit of insights from the submissions from Teethless Mama and benishiryo to aid and abet my efforts.

i have messed around with the data a little bit, and have highlighted the changes that i made to original data (Teethless Mama's file). my aim was to solve it in a way that no gaps existed in the output, duplicates would be taken care of, and sequencing of data would not matter.

EDIT

there was a glitch in the original submission - if there were a blank in the data table's Job column, formula output blanks. in order to fix that, i had to mix Teethless Mama's solution with mine. this solution now takes care of blanks in addition to duplicates, gaps and sequencing.

Thanks I can't check this until Monday but I'll take a look as I've been struggling to apply the solution to my real sheet maybe I'll be able to see what other ways I can do.

Thanks in advance

This is the simplest version I can come up with....

=IFERROR(INDEX(C$23:C$30,MATCH(0,ISNA(MATCH(B$23:B$30,IF((B$3:B$18=N$2)*(C$3:C$18=N$3),D$3:D$18),0))+COUNTIF(L$2:L2,C$23:C$30),0)),"")

should cope with blanks, duplicates etc.

....or this is the same version using icestationzbra's named ranges, assuming first formula in V3 copied down

=IFERROR(INDEX(Name,MATCH(0,ISNA(MATCH(JobX,IF((V1_=N$2)*(V2_=N$3),Job),0))+COUNTIF(V$2:V2,Name),0)),"")