Excel =Large() Function

[amc1794]

Hi,

I am trying to insert a column which for each row of data will examine the values in 4 other columns and will return the name of the column containing the highest value (Primary) and then repeat this inserting columns for the name of the column containing the second highest value (Secondary) and third highest (Tertiary). I was able to do something similar in Excel using the =Large() function however this was limited to returning the value itself rather than the name of the column.

All help is greatly appreciated.

Aaron

[BenMoss · Accepted solution]

Lets say I have the dataset

Record	ColA	ColB	ColC
1	45	656	23
2	34	4	36

Transpose your dataset, using the ID as a key field (use the record ID tool before the transpose), and the columns you want to compare against will be your data fields. This will give you something link...

Record	Name	Value
1	ColA	45
1	ColB	656
1	ColC	23
2	ColA	34
2	ColB	4
2	ColC	36

You can then use a sort tool to place the values in the order you want to create a 'rank' with. Use the sort tool, sorting by your RecordID field and then the value, either descending or descending depending on what you are about.

You can then use the multi-row formula tool to create a nested rank which restarts for each group.

Use the option 'create new column' and lets call this rank.

Your formula should be 

[Row-1:Rank]+1

Making sure you use your RecordID field as a 'Group By' in the list box.

You can then use the cross-tab tool to bring your data into a format looking like:

Record	ColA	ColB	ColC	1	2	3
1	45	656	23	ColB	ColA	ColC
2	34	4	36	ColC	ColA	ColB

I've attached the example workflow of how this can be achieved.

Ben

[LordNeilLord · Accepted solution]

Hey @amc1794

Do you mean something like this?