identifying the max value across columns

[VeronicaS]

Hi there

I am analyzing survey data and the respondents were asked to allocate 100 points across 7 attributes. The way the data is displayed, is that each attribute is a separate row, with the respondents' scores for each attribute in separate columns:

ID	QB2R1	QB2R2	QB2R3	QB2R4	QB2R5	QB2R6	QB2R7	QB2R97	QB2RSCT	NEW_COLUMN
01	0	20	20	0	10	40	10	0	100	QB2R6
02	5	5	10	20	30	20	10	0	100	QB2R5
03	10	0	10	20	10	10	40	0	100	QB2R7

I want to define a new column that returns the column name of the column with the highest score across all of the columns (see example "NEW_COLUMN" above). Is there a way to do this?

If a participant has entered the same score for the multiple top columns, then I just want the formula to return one column name (doesn't matter which one).

Please help!

[CharlieS · Accepted solution]

I have attached one possible solution to your problem. I transposed your data, sorted, and took the first record name to append to the original data.

[john_miller9 · Accepted solution]

The attached workflow seems to do the trick

[tom_montpool · Accepted solution]

Rather than transposing your entire dataset, I took a bit of a different approach with a really neat formula function. 

[PNau]

Thank you Charlie, I ended up using the Sample Tool in lieu of using a Max function for the following scenario.

Column A was my Key Field with duplicate results (e.g., State). 

Column B was a unique value (e.g., City) but in every State field there were Cities with Null values.

Column C was the change in population sometimes negative sometimes positive.

I grouped by Column State and City, so there was only one null value per state.  It may be negative of positive.

I wanted to populate the Null value with the max/min for each state.  If AZ Null was negative, I wanted to pull AZ City that had the largest positive increase and say the two offset each other to lower the swing.  If AR was positive, I wanted to pull the AR City that had the largest negative increase (or smallest positive) and say the two offset.

Using Sample Tool with Filters to do a left / right side approach worked.  I was going to try split the reduction from the min max and say could not be greater than for the total nulls for the state and pull the difference from the next value, but for another day.

Attached is my sample data.  I did not have a clean workflow to share but can if anyone ever needs.  I will update one day, if I learn how to do the same with formulas.

This was useful with SAP journal entries where a trader code was not populated for each line item, only the first time until the trader changed and I had to prepopulate the missing trader.