Help Required to Evaluate Advanced Formula

[Treeman_JF]

Hi all,

As the title says, help would be appreciated to outline what each part of this formula does (it was given tome a while back),
as I wish to change the definition of a number in a cell.

The use of the formula is to return a unique value in A2 (ie. T123) to cell M2, only if there is a number '1' in cell J2.
The formula is in cell M2. I have similar formulas in cells N2 and O2 if there is a '2' or '3' in J2.
This formula is copied down throughout the sheet. I hope this make sense.

I am wanting to change the '1', '2', and '3' to different values but can't see where this would be in the formula.

Here is the formula:

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($J$2:$J$99)/(COLUMNS($M2:M2)=$J$2:$J$99),ROWS(M$2:M2))),"")

If a spreadsheet is required to see how this is used, I can attach it into a future reply (hopefully)

Many thanks

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[Treeman_JF]

Hi

Attached is an example file with two sheets labelled current and desired.
To be viewd in conjunction with the original post.

Many thanks

[Bo_Ry]

Please try at M2
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($J$2:$J$99)/(COLUMNS($M2:M2)=--LEFT($J$2:$J$99)),ROWS(M$2:M2))),"")

[Treeman_JF]

Please try at M2
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($J$2:$J$99)/(COLUMNS($M2:M2)=--LEFT($J$2:$J$99)),ROWS(M$2:M2))),"")

Thanks Bo_Ry, that worked!

Would you mind explaining how the =--LEFT bit works, even when the formula is copied to cells N2 and O2 to produce the different unique values correctly? Say if i wanted to add another work priority column 4. I like to know how things work!

Many thanks

[Bo_Ry]

=LEFT($J$2:$J$6) ={"3";"2";"1";"2";"1"} > text
=--LEFT($J$2:$J$6) ={3;2;1;2;1} => Number

@M2 COLUMNS($M2:M2) =1 => Number
@N2 COLUMNS($M2:N2) =2 => Number
@O2 COLUMNS($M2:O2) =3 => Number

=1/(COLUMNS($M2:M2)=--LEFT($J$2:$J$6)) ={#DIV/0!;#DIV/0!;1;#DIV/0!;1}
1 from J4 , J6 "1 year" the rest will get error #DIV/0!

This filter only year that match columns()

=AGGREGATE(15,6,ROW($J$2:$J$6)/(COLUMNS($M2:M2)=--LEFT($J$2:$J$6)),ROWS(M$2:M2)))
=AGGREGATE(15 small ,6 ignore error ,{#DIV/0!;#DIV/0!;4;#DIV/0!;6},1))

15 small to get small row number rank 1 = 4, rank 2 = 6
6 ignore error like #DIV/0!

[Treeman_JF]

So if I want to add another column titled 'Work Priority 4' in 'P', and I want the work priority in J column to be '6 Months' the formula doesn't work. How would you work around this?

[Bo_Ry]

Please try
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($J$2:$J$99)/(COLUMNS($M2:M2)=MATCH(--LEFT($J$2:$J$99,2),{1,2,3,6,12})),ROWS(M$2:M2))),"")

3-5 for Priority 3
6-11 for Priority 4
12 or more for Priority 5