Extracting different quantities from multiple columns

[eleee3]

Hi all,

I'm trying to extract quantities from a number of columns. An example of my dataset is as follows:

Item	Segment Start (km)	Segment End (km)	Length (m)	Width (m)	Area (m2)	Volume (m3)	Client Qty	Client Unit
Main St	35	42	0	0	0	0
Electrical Line 1	0	0	25	0	0	0
Pedestrian Bridge 3	0	0	0	12	0	0
Playground	0	0	0	0	400	0
Swimming Pool	0	0	0	0	0	12000

And I would like my dataset to look like the following:

Item	Segment Start (km)	Segment End (km)	Length (m)	Width (m)	Area (m2)	Volume (m3)	Client Qty	Client Unit
Main St	35	42	0	0	0	0	7	km
Electrical Line 1	0	0	25	0	0	0	25	m
Pedestrian Bridge 3	0	0	0	12	0	0	12	m
Playground	0	0	0	0	400	0	400	m2
Swimming Pool	0	0	0	0	0	12000	12000	m3

I've tried using a number of IF statements in formulae to achieve this - for example:

IF IsEmpty([Client Qty]) && !IsEmpty([Length]) THEN [Length] ELSE [Client Qty] ENDIF

But the outputs into the Client Qty column are always 0.

Could someone please help me with this? Note that I cannot upload anything due to my company's restrictions.

Thanks in advance!

[mceleavey · Accepted solution]

Hi @eleee3 ,

I've achieved this using a couple of methods assumed from your numbers, given the required output does not correspond to the formula you provided (which would only return one value of 25 in one row, even allowing for the fixed syntax).

I used a simple formula to determine the Client Quantity, which was (Segment End - Segment Start) plus the sum of all other columns.

if IsEmpty([Client Qty]) then ([Segment End (km)]-[Segment Start (km)])+[Length (m)]+[Width (m)]+[Area (m2)]+[Volume (m3)] else [Client Qty] endif

I then pivoted the data to pull the unit from the column names using Regex and joined it back.

This gives the following:

Workflow attached.

I hope this helps,

M.

[eleee3]

That's great, thanks @mceleavey !

[mceleavey]