Create a median of data with a corresponding tag?

[Philip041]

Hello,

Thanks in advance for the help. I would like some help with a macro to help with this if anyone could I would be very grateful. I receive 9 columns of data from my manager, the 9th being a code associated to a fund which is named in column A. I sort the data by this code, with two blank lines left between each data set.

He wants a median of the data from column C of each set to be placed in column J in the blank row just above the data set. The number of rows in each set is variable.

I would also like to place in column A, on the same row (in line with the median) a vlookup of the code, I have the codes and their names on another sheet called Strategies in columns A and B.

Sorry if this is badly explained. Here is how I want it to look:

Investment 'Median of column C'
Data from Investment
Data from Investment
Data from Investment
Data from Investment

Pension 'Median of column C'
Data from Pension
Data from Pension

Savings 'Median of column C'
Data from Savings
Data from Savings
Data from Savings
Data from Savings
Data from Savings


And so on....


Cheers

 

[p45cal]

Try this on an unsorted list, assumes headers are present in row 1:

Sub blah()
Dim RowsInSets()
lr = Cells(Rows.Count, 1).End(xlUp).row
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:I" & lr)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
RowsInSet = 1
For rw = lr To 2 Step -1
    If Cells(rw, 1).Value <> Cells(rw - 1, 1).Value Then
        Rows(rw).Resize(2).Insert
        Cells(rw + 1, "J").Formula = "=MEDIAN(" & Cells(rw + 2, "C").Resize(RowsInSet).Address(0, 0) & ")"
        Cells(rw + 1, "A").FormulaR1C1 = "=VLOOKUP(R[1]C[8],Strategies!R1C1:R[COLOR=Red]20[/COLOR]C2,2,FALSE)" 'assumes lookup table is A1:B[COLOR=Red]20[/COLOR]
        RowsInSet = 1
    Else
        RowsInSet = RowsInSet + 1
    End If
Next rw
End Sub

 

[Philip041]

Cheers!

It seems to enter a name and median on every line though... I will have a play around and see if I can sort it. Many thanks for your help.

 

[p45cal]

Try changing
If Cells(rw, 1).Value <> Cells(rw - 1, 1).Value Then
to
If Cells(rw, 9).Value <> Cells(rw - 1, 9).Value Then

 

[Philip041]

Hi,

I've not got anywhere with making it work. I have thought that it would make more sense if fact to not bow to my manager's every formatting whim and perhaps do something like:

Search through column I for matching codes and 'build' an array of values from the corresponding C column to then calculate a median with. As the data is in order it just needs to compare a row's value with the one below to decide whether it belongs in the median. Then if it doesn't:

a) assign a name to the median just created based on the value of the code in column I

b) start a new median array

At the end of the loop output the medians next to their names in a new sheet?

I tried to think of the most logical thing. Also the medians will be need to be placed in other places everywhere so I thought it might make sense to store them as a variable first.

Sorry for the long message, I'm totally lost with this.

If this process is possible it would be really useful as it isn't only the median he wants, bu loads of things.

 

[Philip041]

Ah, I didn't see your reply! It works! Cheers.

I will prob end up coming back and asking for more help with this but for the mean time thanks loads, this has bought me at least half an hour more lunch with my book for the next week or so.