Top 3 formula or macro

Here's an example. I didn't attach the whole workbook because it's quite large (over 1000 columns). What I did attach, though, shows what I'm looking for. The columns I want for each row are highlighted in yellow. The differences between what I'm currently getting and what I want it to return are in red.

Thanks for taking a look.

Attachment 131766

I'll try attaching it again. My attachment looks different than that of others who have uploaded attachments. I'm new to this forum, so it's entirely possible, likely even, that I did it wrong. If it still doesn't work, I'll email it to you or find another way to link to it.

That almost works, except that it eliminates redundant values, rather than returning all of them. I need the categories of the three highest values, even if they are redundant. Because of what the data represents, in an ideal situation, almost every row would have two values of 1.0, then several with lower values. Using LARGE would work great if there weren't redundant values, but since there are, all I get is a duplicate of the first one of the two redundant values and none of the second because each cell does the calculation anew and returns the first value that satisfies the criteria. I guess I need some way to ignore the first value that satisfies the criteria and return the second if there is one (for the second column, and likewise for the third), or an array function or VBA program that can generate and return all the desired values simultaneously.

Yes, that works. Thank you. I didn't explain cell AV6; I guess I just assumed it would make sense. In that case, there are duplicate values for the third highest value, and I want both of them.

I was about to ask what "ReturnOutcome" is, but then I found it is a VBA object you (or someone else?) wrote. It works great in the example file, but not quite when I copied it to my actual spreadsheet. I copied the appropriate cells to the appropriate place and changed the references appropriately, but it didn't work. Then I found I had to copy the VB module to my workbook, after which the error changed from a #NAME error to a #VALUE error. Now it gets interesting: I have calculation set to "manual" because it's a large complicated spreadsheet that takes several seconds to calculate so naturally I don't want it to do that every time I make any change. If I hit the "calculate now" button in the example workbook you sent me, it calculates both sheets and it works, but if I click that button or the "calculate sheet" but in my workbook with the actual data, it returns a #VALUE error. I tried just copying everything from the sheet you sent me to a new workbook, copied the module to the new workbook, and that also produces the error. It seems I'm missing something here. What is the proper way to implement what you sent me into my workbook?

A good start would be a sample of your actual workbook.

The example you provided has leading/trailing spaces etc., and seems to have a different layout from your actual workbook.

The VBa in the example I posted is "hard coded" to your posted sample.

Post what you have tried up 'till now.

What I posted was a sample of my actual workbook. I just copied and pasted some of the columns with the data I need help with because the whole thing is rather large and complex. If you think it will help, I'll go ahead and post the actual workbook.* The columns I'm trying to get the information from are columns AJC through AML.

It seems to me that I'm doing something wrong where it comes to copying the module in the workbook you sent me. The reason I think that is because, as a test, I created a new workbook and just copied and pasted everything from the example file into the new one. I then copied the module to the new workbook. (Ctrl+A, Ctrl+C, Ctrl+N, Ctrl+V, go into VB editor, drag module from drfarmkid_example.xls to Book1.xls, Save As test.xls) This new one should have been exactly the same as the example you sent me. That one didn't work either, and in fact that one always produces the #VALUE error, regardless of which workbook I press the "calculate now" button in.

Thanks again for your help.

*When I tried to upload the workbook, I received an error that the file was too large (~2.6MB). I deleted all but 5 of the rows; that should be sufficient.

No, that range is not constant. I may expand or contract or move as I process more data.

I wondered about the range issue, but I wasn't sure. I don't know VBA at all, and have only a basic understanding of programming and not enough working knowledge of any language to do anything useful. I tried to follow your code, and I more-or-less understand it, but I wasn't sure what "Sheets("Sheet1").Range("Headers")" meant. I thought that might be the problem, and I changed "Sheet1" to match the name of the sheet in my workbook with no effect, and I thought "Headers" might refer to a named range, but I couldn't find anywhere that it was defined so I wasn't sure.

After reading you last post, I tried defining AJC:AML as "Headers", and changing "Headers" in the code to "AJC:AML" and both worked. It will work to do that, as long as I remember to go change that any time the range changes. It may also make it harder to give the spreadsheet to colleagues to use. Is there a way to make it automatic?

1.
Okay, done. I do have a question about this, though, that perhaps you can help me understand. I wasn't familiar with dynamic ranges and didn't know how to create them until reading your post. After a quick Google search, I learned what I needed to know, and the example I read used an OFFSET function to create it. Following that example, I tried creating a dynamic range using the formula

Please Login or Register  to view this content.

and that seemed to work just as well. It's a simpler formula that I can actually understand, whereas I can sort of follow yours but the nuances escape me. Perhaps you can help me understand why you did it the way you did.

2.
Done, that works fine as well.

3.
Rows 1 and 2 are indeed generated manually based on data from past experiments. Just a little background: these data come from large numbers of DNA sequences. They essentially represent versions of genes that appear together in certain defined groups. They will change from time to time as we discover new groups and new sequences and/or find that the current groups/sequences are incomplete or incorrect.

4.
That list comes from Row 1. They are the same, except that the blank cells have been removed. I generated that by copying/transposing Row 1 into another spreadsheet, removing the blank rows with a filter, then copying/transposing back into its current location. It's important that it's in the same order as Row 1, since the formulas in the rows below reference those to find the appropriate locations in the main block of data. I had planned to just redo that whenever the changes mentioned in #3 above necessitated an update, but if there's a simple way to automate that I certainly wouldn't complain.

1/. With the potential size of your data matrix you should seriously avoid volatile functions such as OFFSET().
INDEX("your range",MATCH(),MATCH() ) is much more efficient.

4/. Okay, I think I'm with you when combined with your reply to "3/,", this can easily be produced automaticaly.

To much beer tonight ... lookback in the morning ... ...

Now that I'm back at work, I've taken a look at what you did. I like it. It's certainly set up better than what I had, and I think it will work better. I really appreciate all your help. I've also learned several tricks and approaches that should be useful in the future as well.