Multi field based calculation

[Inactive User]

Hi all,

I want to calculate new column based on existing columns, for example i hv columns

Col1,col2,col3,col4,col5,col6.......

New col calculation criteria matching with col1 value then i need to pick value from col4, same with col2 then col5 and so on, is this possible through multi field tool ?

Can anyone help ?

[OllieClarke]

Hi @Inactive User you could use a normal formula tool for this, but a better approach (I think) is to use a record ID and a transpose tool (using recordID and Col1 as your key fields). You can then filter to where [Value]=[Col1] and join it back onto your data using the RecordID.

Hope that helps,

Ollie

[Inactive User]

Hi @OllieClarke 

Thanks for your revert, i am sharing a sample data set and snapshot, I have 40 column of Morning, Day and want to calculate 40 columns of Afternoon with a formula (can check in afternoon columns), Can you help to resolve this issue ?

[OllieClarke]

Hey @Inactive User 

I've made 2 solutions, a dynamic one which will map out the 3 columns no matter how many types there are (e.g. if you got a day9 and a morning9 it would work)

Or a much simpler static solution - where you will always have exactly 8 of day and morning

Hope that helps,

Ollie

[Inactive User · Accepted solution]

Thanks @OllieClarke this worked for me.