Pulling whole row based on highest value of a specific supplier number

[lunatyck]

Hi everyone.

I am rather new to excel but since I am an IT major, I have some understanding of how the coding of this macro should work. However, I am not capable of writing this code on my own so I am asking for your help.

I have a spreadsheet with roughly 1000 lines. Within this spreadsheet, I have a list of suppliers (using unique supplier numbers), plants which have purchased from these suppliers, and how much each plant has purchased from these suppliers. Since multiple plants buy from the same suppliers, the supplier names come up 1 or more times within the spreadsheet with different plant buyers. I am needing a macro that will choose which plant has spent the most from a specific supplier, and pull that row of information into a new spread sheet or simply filter the data so it only displays the supplier and its highest buyer.

I have attached a small bit of the spreadsheet to show you what the columns look like (Raw.xlsx) and the numbers that I want pulled (Desired.xlsx).

My logic of how the macro should work is:

-Begin in column A and pick up the supplier number (unique).
-Go to the next row in column A and see if A2 matches A1
-If it matches, remember that and continue down until Ax != Ax+1
-Once the rows have been found with matching numbers for that supplier number, compare the values in column D, find the MAX, and pull/highlight that row.
-Once the row has been pulled/highlighted, continue to the next supplier number and repeat.

I prefer for the data to be pulled to a new spreadsheet since I will have to manually copy the highest values to another spreadsheet later on.

Please let me know if I am making sense or not, and thank you very much in advance.

[Pichingualas]

This is possible and not too hard to do. Do you mind if I sort the data prior to finding the highest value? It would make the code easier since it wouldn't need to do any comparison, as it would only need to pick the first row listed for each supplier number, which would also result in your code running faster. And by the way, you made a mistake when choosing the row to highlight for Company 5.

[lunatyck]

This is possible and not too hard to do. Do you mind if I sort the data prior to finding the highest value? It would make the code easier since it wouldn't need to do any comparison, as it would only need to pick the first row listed for each supplier number, which would also result in your code running faster. And by the way, you made a mistake when choosing the row to highlight for Company 5.

The order does not make a difference, the only important thing that no supplier gets left out and that each supplier only ends up having one entry with the highest buyer/amount. In regards to my mistake, I'm not surprised since I just quickly highlighted the fields! I apologize for my mistake :P

+rep btw

[Leith Ross]

Hello lunatyck,

Welcome to the Forum!

The macro below has been added to a button "Sheet2". When clicked it will create a list of unique suppliers with the highest amounts. Order doesn't matter. Yo can change the source and destination sheet names and starting cells if you need to. The attached workbook has everything added.

Please Login or Register  to view this content.

[lunatyck]

Leith,

It works like a charm! Thank you very much. I tried copying the formula over to my original spreadsheet but I received a 400 error. I will try to figure it out later on, but till then I will just copy my values into your spreadsheet, run the macro, and copy/past the desired results back to my spreadsheets.

However, I am very interested in the code and how it functions. If you have a few minutes to kind of explain how it works, I'd greatly appreciate it.

From what I see, you created a "dictionary" within your code and you basically pulled in the supplier, took note of the highest value and copied the row to the next spreadsheet. Then you had a check against the dictionary to see if the supplier entry exists. If it didn't you added it, or if it did it checked to see if it is the highest amount? I'm not a strong coder but I am intrigued by what code can do and would love to understand it more.


Thank you once again.
-Konrad

Hello lunatyck,

Welcome to the Forum!

The macro below has been added to a button "Sheet2". When clicked it will create a list of unique suppliers with the highest amounts. Order doesn't matter. Yo can change the source and destination sheet names and starting cells if you need to. The attached workbook has everything added.

Please Login or Register  to view this content.

[Leith Ross]

Hello lunatyck ,

That is exactly what I did. At the end, the entries in the dictionary are retrieved and the second element of Data (subscript 1) is used to copy the entire row from the source worksheet (SrcWks) to the destination range (DstRng).

[lunatyck]

Thanks once again!

[Leith Ross]

Hello lunatyck,

The link below will introduce you to the Dictionary object. It is similar to a Collection object but has more features. It is one of the most useful programming tools in VBA. If you have questions about it, let me know and I will answer them.

Dictionary Object

[Pichingualas]

Well sorry for the delay. Anyway, I see Leith Ross got you covered, but I guess I'll post my version anyway.

Please Login or Register  to view this content.

It seems to work too. I hope that helps someone, and thanks for the rep.

[lunatyck]

It is still very much appreciated and although I'm getting an error 400 code on my side ( I set Ws and Ws2 to Sheets("Test") and Sheets("Results"). It doesn't seem to compile. However, I think it might be something on my side if it is working for you but I still want to thank you for your effort. It might help someone else though, you never know!

Well sorry for the delay. Anyway, I see Leith Ross got you covered, but I guess I'll post my version anyway.

Please Login or Register  to view this content.

It seems to work too. I hope that helps someone, and thanks for the rep.

[Pichingualas]

Well sorry, I honestly don't know why it doesn't work for you, goes fine here.