I am currently automating a dashboard creation and I've hit a bit of a roadblock. I need some code that will go through about 7000 rows of data and return the highest value in a certain column for each specific item. The data is copied from a pivot table and so is broken down into row sections, I have attached a mock of what it looks like.

I need the highest value in Column G for each portfolio, and will need to use the portfolio code (e.g. XY12345 - They are always 7 characters) to map that value to the dashboard.

My issue is, each portfolio has a different number of rows for the values, and some have blank cells between them, and therefore I am stumped. I was hoping to use Column J to count the number of rows for each portfolio (as there are no breaks for the portfolios in this column) and then use a loop to loop through each portfolios rows of values, based off the Column J count, and then return the highest row value for each portfolio. Problem is I'm new to VBA and have been teaching myself as I go, and I've yet to use a loop.

Many thanks,

Harry

If I understand correctly, you're looking for the largest value in Column G.

I'm not sure why you think you would need VBA for this.

Get the maximum value of a column

You mentioned that you're concerned about each column not having the same number of cells but that's irrelevant. as SUM ignores blank cells, so just "go long", or - find the maximum of the entire column.

To return the largest number in Column G you could use worksheet formula :

=MAX(G:G)

The only catch is that you can't place that formula anywhere column G or else it would create a circular cell reference (trying to infinitely add a number to itself). let's pit that formula in cell F1 for now (but anywhere besides column G would do fine).

Find the location of a value

Now that you know the largest value, you can determine where it is using a lookup function such as MATCH or VLOOKUP. Like with so many things in Excel, there are several ways to accomplish the same thing. I'll go with MATCH.

Replace the formula from above (in F1) with:

=MATCH(MAX(G:G),G:G,0)

This will return the row number of the first exact match of the maximum value of Column G.

As for the third part of question: returning the code like X12345 where the value exists, will be a little tricky since your data is not organized in a logical tabular style (tabular meaning, "like a table").

Your data is organized for humans to look at, not for machines to easily read and manipulate it. (See: Office Support: Guidelines for organizing and formatting data on a worksheet)

Basically, when organizing data in rows, all relevant information should be on the same row (not a subjective number of rows behind). Also, you have the number combined with other information.

My suggestion for a quick fix:

1. Right-click the heading of Column C and choose Insert to insert a blank column.


2. In C2 enter formula: =IF(B2="",C1,LEFT(B2,7))
   


3. Copy cell C2
   


4. Select cells in column C all the way to the "end" of your data, where ever that is (not the end of the worksheet). For example maybe you would select cells B2:B1000)


5. Paste the copied cell into all those cells.

Now, you can again modify the formula in F1:

=INDEX(C:C,MATCH(MAX(G:G),G:G,0))

This will return the value from Column C in the same row that the maximum value of Column G is located.

This is known as an INDEX/MATCH formula.

Hopefully this works for you in the interim until you can organize your data more logically. There's lots of related information and tutorials online.