First, pardon my absence, I had a family emergency needing attending.

Okay, so in my haste I have created confusion. With that, let me start over. In an Excel spreadsheet I have the following:

In the first Row, column cells B1 through AA1, I have numbers 1 through 26 - this is a static Row and will be the returned value.

In the second Row, cells B2 through Z2 have varying numerical values between 1 and 50. Each cell is non-distinct so multiple cells MAY have duplicate values, i.e., cells B2, H2, L2 may have the value “49”, or not, etc.

In cells AC2 through AL2 is the formula {=LARGE(B2:AA2,{1,2,3,4,5,6,7,8,9,10})} (this is a CSE array). This formula returns the Top 10 cell values from the cells B2 through AA2.

In cells AN2 through AW2 is the formula =MATCH(AC2,$B2:$AA2,0) - - - each cell changes the formula to match the successive cell - =MATCH(AD2,$B2:$AA2,0), etc.

Now for the problem.

If there are three cells throughout Row 2 array equaling “49”, say cell B2, H2 and L2 then cells AC2, AD2 and AE2 will each return “49.” So then Cells AN2, AO2 and AP2 each return “1” (the first cell equaling “49”), but I want AN2 to return “1”, AO2 to return “7” and AP2 to return “11” (from the static row - cells B1, H1, and L1).

If anyone would like to have a copy of the spreadsheet to peruse, please let me know and have an address in your profile. I sent one copy, but no joy.

Thanks for your consideration.

This message has been edited. Last edited by: craigcpa,

This makes no sense. Needs a better explanation, or a screen shot or something. or email it to me and i can look at it.

I think I understand what you're doing. MATCH returns the offset of the range/array argument. Use that offset in ADDRESS to form an address and dereference that address using INDIRECT

These are the function signatures:
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

INDIRECT(ref_text, [a1])

What you want is something like this:

=INDIRECT(ADDRESS(1, MATCH(...), 1))

You can leave off the last two arguments of ADDRESS and the last one of INDIRECT, and let them default to their default values.

This should solve your second problem. I'm not sure about your first one - LARGE returning 47 multiple times.

Very confusing. There are no "Row 101AA through 101AJ". There are cells AA through AJ in row 101. Is that what you're talking about?

JP

Craigcpa,
Have you figured this out?

I think I have a line on how to make this work, but you will probably have to add a supplemental page to the spreadsheet.

The way to get this to work is to sort the values in Columns B-Z (large to small) [Rows > 1] and parallel sort the indices in Row 1 along with them. For this, you will obviously have to copy the values into another page and sort them.

In each successive call to MATCH, the scanning range argument (2nd arg), you shorten that by one cell from the front.

Example:
1) MATCH(AA101, B101:Z101, 0)
2) MATCH(AB101, C101:Z101, 0)
...

Note: The addresses and ranges in the above MATCH function calls, would be into the 2nd page of sorted numbers.

Note: Manually sorting 150 rows of data individually sucks. However, there is an upcoming SORT function that might do the job. I don't have that in my version of Excel, yet (July 2020), but you might.

Wow. You people are scary.

I'm coming here next time I have an Excel question.

I’m an Excel whisperer.

I can no doubt help you but I’m trying to understand what you’re trying to accomplish.

What’s 4, 12, 13, 18, and 20? These are the column headings of the values with the max value in each row? Right off the bat, I know there’s a formula that converts a column letter to a number.

In cases like this, I prefer starting from the ground up. I would need to understand the nature of the data set. Can all values in a row be the same number? What are you trying to accomplish?

I am always amazed at what people that are very proficient with Excel can do.

I use it quite a bit, but nothing with overly complicated formulas.

The last two companies that I have worked for each have had a highly skilled Excel guru.

Bump to show edited OP. Secondly, will go through suggestions starting with Storm’s 2nd post. Thanks all, and I’ll keep you all up to date with how this is going.

I sent you an email

quote:

Originally posted by Storm:
Craigcpa,
Have you figured this out?

I think I have a line on how to make this work, but you will probably have to add a supplemental page to the spreadsheet.

The way to get this to work is to sort the values in Columns B-Z (large to small) [Rows > 1] and parallel sort the indices in Row 1 along with them. For this, you will obviously have to copy the values into another page and sort them.

In each successive call to MATCH, the scanning range argument (2nd arg), you shorten that by one cell from the front.

Example:
1) MATCH(AA101, B101:Z101, 0)
2) MATCH(AB101, C101:Z101, 0)
...

Note: The addresses and ranges in the above MATCH function calls, would be into the 2nd page of sorted numbers.

Note: Manually sorting 150 rows of data individually sucks. However, there is an upcoming SORT function that might do the job. I don't have that in my version of Excel, yet (July 2020), but you might.

So, “TRANSPOSE” the entire sheet to another sheet and use this secondary as a “Column lookup?”

quote:

Originally posted by BlackTalonJHP:
I sent you an email

E-mail replied.

quote:

Originally posted by craigcpa:

If anyone would like to have a copy of the spreadsheet to peruse, please let me know and have an address in your profile. I sent one copy, but no joy.

Thanks for your consideration.

I’ll take a copy.

I can’t get to my laptop until Saturday evening at the latest.

The problem with your method is that your intermediary step only pulls the top 10 values without carrying the positional information.

My solution would be as follows:

1st sheet is your data as is. Column headings of date, then 1, 2, 3, etc. rows going down by date with the data in each cell.

2nd sheet: copy the same column and row headings.
In Cell B2, enter formula (I would click on the sheet 1 cells to get the actual format referencing the cells in sheet 1 if I were you since I’m doing this by memory ). “= Rank (‘Sheet 1’! B2, ‘Sheet 1’! $B2:$AA2) + Countif (‘Sheet 1’! $B2:B2, ‘Sheet’! B2) - 1”
Copy the formula across the columns and down the rows. What’s key is the $ signs and not having it where I don’t have it.

What this does is rank each cell in each row in descending order with out duplicates. The same values in each row will have a number based on its position.

In sheet 3, copy the same row headings. But for columns, label 1 through 10.
In Cell B2, enter the formula “ = index (sheet 2! $B$1:$AA$1, match (B$1, sheet 2! $B2:$AA2,0))
Copy across and down the cells. This pulls the column headings of the cells in each row according to their rank 1 through 10. Confirm you are pulling the right column headings. You may have to add or subtract the match portion by 1. I’m just doing this in my head on my iPhone.

If you want to know the actual values then add a second set of columns 1 through 10. Let’s say you skip column L and start in N with same headings as 1 through 10. In cell N2, enter “ = index ( sheet 1! $B2:$AA2, match (sheet 1! $B$1:$AA$1, B2,0))” Copy down and across.
This third section pulls the value from sheet 1 by finding the column heading in the second section. So in the third sheet, you have a section showing for each row which column is 1 through 10 and a second section showing the top 10 values for that row.

The formulas as I wrote them from memory may need tweaking to ensure they are working and picking up the right numbers.

I’m an Excel Whisperer because not only do I know the formulas and VBA, I also understand the relationships between the different sets of numbers and information.

quote:

Originally posted by craigcpa:

quote:

Originally posted by Storm:
Craigcpa,
Have you figured this out?

I think I have a line on how to make this work, but you will probably have to add a supplemental page to the spreadsheet.

The way to get this to work is to sort the values in Columns B-Z (large to small) [Rows > 1] and parallel sort the indices in Row 1 along with them. For this, you will obviously have to copy the values into another page and sort them.

In each successive call to MATCH, the scanning range argument (2nd arg), you shorten that by one cell from the front.

Example:
1) MATCH(AA101, B101:Z101, 0)
2) MATCH(AB101, C101:Z101, 0)
...

Note: The addresses and ranges in the above MATCH function calls, would be into the 2nd page of sorted numbers.

Note: Manually sorting 150 rows of data individually sucks. However, there is an upcoming SORT function that might do the job. I don't have that in my version of Excel, yet (July 2020), but you might.

So, “TRANSPOSE” the entire sheet to another sheet and use this secondary as a “Column lookup?”

Not Transpose but write and copy a reference formula:
To get the numbers for sheet1 into sheet2, you can use a reference. Like for Sheet2, Cell A1 use the formula: =Sheet1!A2.

Below are screenshots from the test spreadsheet I set up to do this. It works, but probably is not what you want to use for a final solution, as you will still have to do a number of things manually, for instance sorting the values in sheet2.

In this test spreadsheet, I used 10 random values (between 1-10) for the data (image 1), five of the greatest values from this set (image 1), and the calculated addresses of the original indices of the 5 largest values, using ADDRESS, and MATCH (image 2). To get the index value, just wrap the call to ADDRESS with INDIRECT.





The function calls that generate the addresses above (Columns: P, Q, R, S, T):

=ADDRESS(2,MATCH(K2,Sheet2!A1:$J1,-1),TRUE,1,"Sheet2")

=ADDRESS(2,MATCH(L2,Sheet2!B1:$J1,-1)+1,TRUE,1,"Sheet2")

=ADDRESS(2,MATCH(M2,Sheet2!C1:J1,-1)+2,TRUE,1,"Sheet2")

=ADDRESS(2,MATCH(N2,Sheet2!D1:$J1,-1)+3,TRUE,1,"Sheet2")

=ADDRESS(2,MATCH(O2,Sheet2!E1:$J1,-1)+4,TRUE,1,"Sheet2")

Note two things about the function calls above:
1) Each range argument (#2) in MATCH starts one cell to the right of the previous one. This is to skip over the last largest value found.

2) Incrementally larger integers are added to the return values of MATCH. This is the offset, because we are starting each successive call to MATCH further to the right.

Sheet 2 - Data and original indices sorted:



CraigCPA, I'm sending my test spreadsheet to the email you have in your profile.

HTH,
Storm

This message has been edited. Last edited by: Storm,

quote:

Originally posted by craigcpa:
Thanks all, and I’ll keep you all up to date with how this is going.

I sent you a copy of your spreadsheet back with the solution as I described above.

It gives you the column headings of the top 10 values for each row in descending order and in a second set, it gives you the actual top 10 values for each row.