How to extract specific data from a data-set that is located in a different column for each row?

Hello everyone,

I am trying to extract data from the following data-set below that has multiple rows with multiple columns, however each row has not the same amount of columns. I would like to extract the data in the green cells as show in the picture below:

There is a pattern in the data-set. The data I would like to extract is always the third cell from the end of the row.

Since I have no idea how to do this I hope someone has a suggestion for me.

My initial idea was to somehow find a way to merge the last 3 columns into one column. Then copy/paste this data from the merge column in Google Sheets (or with EDT) and then split them again so I would have the data that I want in the first column after splitting. I hope there is actually an easier way, lol.

How would you solve this in EDT? (or in Google Sheets?)

Resources:

  1. The raw data:

43 10 12345-00020 123 Popular Bonsai Tree Design 12345 12.00 120.00
162 10 789-333337 789B Jack Tree Design 789B.123 15.00 150.00
302 10 1515AB-000060 1515AB Black Tree W/Vase - AY 1515AB 81.00 810.00
393 10 954RL Replacements- 10 pcs. - Uniform (= 1 carry box) MOQ 6 boxes 954RL 100.00 600.00

  1. I can’t attach the CSV file with the data-set as a new user, but it is basically the data above, but split in columns.

Looking forward to suggestions to solve this query.
Thank you for helping out!

This is a bit of a tricky one due to the varying number of columns on each row. However you can do it by using Concat Cols to concatenate the data into 1 column (column 19 in this case) and then writing some Javascript to extract the 3rd last value from that column. Make sure the Delimiter you choose for the Concat Cols is not something that can appear in a value.

var a = $(19);
var commaCount = 0;
var newString = "";
for (var i = a.length - 1; i >= 0; i--) 
{
   c = a[i];
   if ( c == ',' )
   {
       commaCount += 1;
   }
   else if ( commaCount == 2 )
   {
       newString = c + newString;
   }
}
return newString;

input.csv (293 Bytes)
third-last.transform (2.4 KB)

It might also be possible to it with Replace and regular expressions. There may also be a more elegant way using other standard transforms, but I couldn’t think of one.

1 Like

One of our customers (Mujeeb) also sent in this Excel solution.

How to extract specific data from a data-set that is located in a different column for each row?

What I did is created 3 helper columns in the start of the sheet

Formula for 1st column = INDEX(D1:U1,1,B1)
Which gets the desired data from the row

Formula for 2nd column = COLUMNS(D1:U1)-C1-2
Which gets the desired column from which to get the data

Formula for 3rd column = COUNTBLANK(D1:U1)
Counts the blank columns in each data row.

All you have to do is set the range for the row data, that is start column and the end column from the row which has to largest row size.

We prefer the Easy Data Transform only solution (we are biased!) but this might also be useful for someone.

Thanks Mujeeb!

1 Like

Here’s your xform. Who do I invoice :wink: ?
Gus.transform (4.6 KB)

2 Likes

Very impressive @DanFeliciano ! I did suspect there was a way to do it without Javascript or RegEx, but couldn’t work it out.

You guys are amazing! Thanks for sharing how to do this in EDT @Admin @DanFeliciano.

I also tried Mujeeb’s Excel solution and works too.

Definitely going to learn more tricks with EDT :laughing:

1 Like