Hi! I'm relatively new to Excel and VBA and I could really need some help with the following problem. I'm truly sorry if I'm posting a question already asked, after hours of googling I haven't found an answer that would suit my situation (although I think this will be relatively easy for someone more experienced!)
The worksheet I use looks something like below. In column A I have different values and in column B an ID code to correspond every single of those values. E.g. values in column A could be price and values in column B a product code. All cells in columns A and B contain references to another worksheet (so basically my values and ID codes are fetched from another worksheet with Index and Match function combination and they are not static, i.e. if I change certain settings the values and codes in columns A and B change too).
My goal is to find the three biggest values from column A with corresponding ID from column B. In cells D3:D5 I've used Large function to find the three biggest values from column A and in cells E3:E5 Vlookup function to find the ID numbers matching those values. The problem is that Vlookup only returns the first occurrence, i.e. if I have duplicate values in column A, I get the same ID twice. So in cell E4 i have the ID 11 instead of 33.
To solve this I've tried to create a VBA macro that would automatically sort columns A and B in respect to values in column A so that I could easily link my top three value-ID combinations to columns D and E. So far I've only managed to construct macros that either don't work or crash my workbook altogether. It seems that having cells containing references somehow imposes problems. At the end of the day all I want is to return the three biggest values from column A with corresponding ID from column B so I'm happy with any kind of solution that does the trick, even if it didn't use the sorting approach I have tried.
I would really appreciate if someone could help me! Thank you for your time.
<tbody>
</tbody>
The worksheet I use looks something like below. In column A I have different values and in column B an ID code to correspond every single of those values. E.g. values in column A could be price and values in column B a product code. All cells in columns A and B contain references to another worksheet (so basically my values and ID codes are fetched from another worksheet with Index and Match function combination and they are not static, i.e. if I change certain settings the values and codes in columns A and B change too).
My goal is to find the three biggest values from column A with corresponding ID from column B. In cells D3:D5 I've used Large function to find the three biggest values from column A and in cells E3:E5 Vlookup function to find the ID numbers matching those values. The problem is that Vlookup only returns the first occurrence, i.e. if I have duplicate values in column A, I get the same ID twice. So in cell E4 i have the ID 11 instead of 33.
To solve this I've tried to create a VBA macro that would automatically sort columns A and B in respect to values in column A so that I could easily link my top three value-ID combinations to columns D and E. So far I've only managed to construct macros that either don't work or crash my workbook altogether. It seems that having cells containing references somehow imposes problems. At the end of the day all I want is to return the three biggest values from column A with corresponding ID from column B so I'm happy with any kind of solution that does the trick, even if it didn't use the sorting approach I have tried.
I would really appreciate if someone could help me! Thank you for your time.
| A | B | C | D | E | F | G | |
| 1 | Value | ID | |||||
| 2 | 6 | 11 | Three biggest values | ID | |||
| 3 | 1 | 22 | 6 | 11 | |||
| 4 | 6 | 33 | 6 | 11 | |||
| 5 | 2 | 44 | 2 | 44 | |||
| 6 | #N/A | #N/A | |||||
| 7 | #N/A | #N/A |
<tbody>
</tbody>
