0

I have 2D array from which I have to find the 50 largest values and to create one dimensional array. Also, I have to find the position of these values and extract the data from the rows and the columns - the date and the hour.

excel sheet

I used LARGE function to find the 50 largest values but need to extract also the date (row) and the hour (column) from the array.

Thank you!

Improve this question
2
  • 1
    Welcome! Please share some code of what you're trying to do and what isn't working for you Commented Apr 11, 2021 at 5:35
  • I want to find the 50 largest values from the array which I have alredy done with LARGE function. But I also have to identify the position of these values in the array - row and column. Thank you! Commented Apr 11, 2021 at 6:10

1 Answer 1

Reset to default
1

Since you are able to find the Large(k) number, I am going to start my solution from finding the row and column for each Large(k) number.

Lets consider the below sample data. Sample data

Formula used for Date row

=INDEX(A2:A6,LET(x,(B2:E6=C7)*SEQUENCE(ROWS(A2:A6)),MIN(IF(x>0,x))))

Formula used for Hr Column

=INDEX(B1:E1,,LET(x,(B2:E6=C7)*SEQUENCE(,COLUMNS(B1:E1)),MIN(IF(x>0,x))))
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you sir! That was exactly what i needed!
There is a problem with the TEXTJOIN function which is limited to 252 arguments. Mine's are 12 000.
I am not using textjoin function
@DimitarGeorgiev What does TEXTJOIN have to do with your problem or this answer?
I figured it out! Job done! Thank you again!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.