Is there a way to move row data into specified columns?

Question

Hi everyone,

I have 20 columns all with specific headers. Then another 6 columns (Field of Study 1-6) with data that matches 1 or more of those headers and an additional 6 columns (Credits for Field of Study 1-6) with a numerical value associated with that header.

At this juncture I'm basically brute forcing it and it isn't a very good solution (nor is it actually working). What I've done is use individual formulas to move things into the columns by doing "IF [Field of Study 1] = "Accounting" THEN [Credits for Field of Study 1] ELSE NULL() ENDIF" etc. with each header. But once I move on to the Field of Study 2 it nulls out the original value for obvious reasons.

I was thinking transpose and some kind of formula then crosstab but I'm not 100% on how that would work. Any ideas?

3rd picture at the bottom is the end result I'm looking for. Thanks!

SPetrie · Accepted Answer

Are all of these columns in one sheet or are these in two different branches and you are trying to merge them?

Im guessing its one sheet, but not entirely certain. Im going to work on that assumption though.

If you are using multiple if statements in your brute force, only the first if statement can use the 'else null()' which is why the originals get nulled out. Your subsequent if statements need to look like "IF [Field of Study 2] = "Accounting" THEN [Credits for Field of Study 2] ELSE [Accounting] ENDIF" so that it keeps whatever value may have been assigned by the previous if statement.

As with most things like there, there are probably multiple ways to solve this.

One alternative method I can think of is a multi-field formula to 'enhance' the brute force.

if [_CurrentFieldName_] = [Field 1] then [Credits 1] elseif [_CurrentFieldName_] = [Field 2] then [Credits 2] elseif [_CurrentFieldName_] = [Field 3] then [Credits 3] else [_CurrentField_] endif

But that is still cumbersome and requires effort to maintain if you have new field names etc.

A flashier and more dynamic method is a dynamic replace.

First I split it out into the columns that have the data we need.

Select

Then, I transpose it and use a multi row formula to get the names of the target column, the name of the field that is holding the target column, and the name of the field with our target value all on one row.

Once that is done, I create the final formulas I want applied and use the dynamic replace tool to apply them.

Again, this is all on the assumption that all the columns are on one sheet. If not, it can be altered accordingly, but it would be best to send along a file with sample input\output so we can better assist.

Hope this helps!