Last Column Value

[BRRLL99]

Hi

I have following data , expected result I'm looking for last value of the column, I'm looking for optimized workflow

SL NO	Column-1	Column-2	Column-3	Column-4	Column-5
100	A	B	C	D	E
101	S	D	G
102	F	R	A	H
103	G	J	W
104	S	G
105	M	G

Expected Output:

SL NO	Colkumn-1	Column-2	Column-3	Column-4	Column-5	Last Value
100	A	B	C	D	E	E
101	S	D	G			G
102	F	R	A	H		H
103	G	J	W			W
104	S	G				G
105	M	G				G

[DataNath]

Hey @BRRLL99, here's one way I'd go about this:

We first Transpose the data to get all values alongside the [SL NO]:

We then make a concatenated list - values Transpose in order and then Summarize in order of appearance:

A simple Formula expression gets us the rightmost value i.e. the last in the concatenated list:

After that we just Join back and sort! Hope this helps.

[BRRLL99]

I have concatenated the data using summarize 

how can i pick the last value dynamically ( without using text to columns tool)

original data has been made few changes

[DataNath · Accepted solution]

@BRRLL99 if they're comma separated like in your example then there's a few ways you can do this, for example - string functions:

Right([List],
FindString(
ReverseString([List]),','))

RegEx:

REGEX_Replace([List], '.+,(.+)', '$1')

Just replace [List] with your [Concat_Value] field.

[RobertOdera]

Hi, @BRRLL99 

Kindly consider the below.

I hope you find it helpful - Cheers!

[flying008 · Accepted solution]

Hi, @BRRLL99 

There is a easy way for you want:

[BRRLL99]

@DataNath @flying008 @RobertOdera 

there has been a update my data, I have 15 columns in similar way, my end goal is to pick last column value

expected output:

SL NO	COLUMN -1	COLUMN -1 Q	COLUMN -2	COLUMN - 2 Q	COLUMN -3	COLUMN - 3 Q	COLUMN -4	COLUMN - 4 Q	COLUMN -5	COLUMN -5 Q	Last column Value
100	A	12	B	56	C	90	D	34	E	12	E
101	S	15	D	4	G	100					G
102	F	99	R	34	A	120	H	56			H
103	G	54	J	77	W	88					W
104	S	34	G	44							G
105	M	19	G	55							G

INPUT:

SL NO	COLUMN -1	COLUMN -1 Q	COLUMN -2	COLUMN - 2 Q	COLUMN -3	COLUMN - 3 Q	COLUMN -4	COLUMN - 4 Q	COLUMN -5	COLUMN -5 Q
100	A	12	B	56	C	90	D	34	E	12
101	S	15	D	4	G	100
102	F	99	R	34	A	120	H	56
103	G	54	J	77	W	88
104	S	34	G	44
105	M	19	G	55

[flying008 · Accepted solution]

Hi, @BRRLL99 

There are 2 ways to get it .

1- You only need change the filter condition from !IsEmpty([Value])  to REGEX_Match([Name], '.+\d$') && !IsEmpty([Value])

2- You only need use Select tool or Dynamic Select tool to filter useless fields before transpose tool.

[BRRLL99 · Accepted solution]

please share the file

[DataNath · Accepted solution]

Hey @BRRLL99, just need to change the Formula expression in my original workflow so it only takes the last letter: