Hi brilliant forum people
I'm looking for ideas on how to solve a GV output problem.
I have 6 columns from I/C that I am exporting to .cfv to import into my website.
THese columns represent an item's "Size","Colour" or "Weight" (e.g. XL & Black for Jacket)
Some items will only be listed with colour or weight, some might use 2 variants.
The websites requirements are that the columns are aligned to the leftmost column.

If I concatenate the 6 columns together is there a way to then split back out to first word, second word, third word, etc?
I can do this in excel and google sheets but not sure how to extract the nth word in Gridview.

e.g. The outputted columns look like this.
SKU #___| Size_|Size Value | Colour | Colour Value| Weight | Weight Value |
item 1324 |_____|_________| Colour | Black______|_______|____________|
item 1325 |_____|_________| Colour | Red_______|_______|____________|
item 1326 |_____|_________| Colour | Green______|_______|____________|

I need to have calculated columns look like this.
SKU #___| opt#1 _| opt1 Value__| opt#2__| opt#2 Value_| opt#3__| opt#3 Value__|
item 1324 | colour_| Black______|_______|___________|_______|____________|
item 1325 | colour_| Red_______|_______|___________|_______|____________|
item 1326 | colour_| Green_____|_______|___________|_______|____________|

Is there an easier way to re-align these values to the leftmost columns? Any Ideas?

Thank you in advance.
Adrian

i have managed to concatenate the columns with spaces between each word and then extract the first word out with "LEFT" function
formula is:

left({concatenate},find(" ",{concatenate},1)-1)

and also the second word with "MID" function
formula is:

MID({concatenate},FIND(" ",{concatenate},1)+1,FIND(" ",{concatenate},FIND(" ",{concatenate},1)+1)-1-FIND(" ",{concatenate},1))

I have no idea how to extract the third, fourth, fifth and last cell without having a "SUBSTITUTE" formula in gridview.

Thanks again
Adrian

Adrian:

That is an unusual file format, though I see the intent is to be able process a variety of product characteristics.

What if you break the problem into 6 smaller problems? Have a calculated column for each of the 6 characteristics with appropriate logic. Then concatenate the SKU plus those 6 columns together to produce the final string. Would that work?

Hi Bruce
I think I need to better explain my dilemma and show what I have created so far as a workaround.

My website requires a .cfv file for SKU import.
I want to add variant dropdown boxes [img]https://ibb.co/hVW8x6G[/img]
I will need to add a cell on that SKU's row titled "Option 1" & "Option 1 Value"
The cell value "Option 1" displays the type of box displayed on the website. e.g. "Size"
The cell value "Option 1 Value" is for the drop down box options, e.g. "LRG,XLRG"
So the requirement for one drop down box on the website is two cells in the .cfv.

Some SKU's will have more than one variant.
If I have a SKU with as many as 3 drop down boxes, I would then require 6 cells.
I/C only has 3 Optional Text fields for output which I have enabled. [img]https://ibb.co/f920KbF[/img]
Filling any of these fields has enabled me to retrieve the info in GridView. [img]https://ibb.co/zV2K4Yp[/img]
"Colour Value","Size Value","Weight Value"

In order to create 6 fields from only 3 I created 3 calculated columns that fill the cells with the words "Colour","Size"and"Weight" [img]https://ibb.co/Rc1ZPkL[/img]
The Header for the .cfv must be titled "Option1,Option1Val,Option2,Option2Val..etc."
At this point I tried to upload to the website but realized that if the Variant was in the Option2 cell and the Option1 is blank the upload would not work. The cells need to be shifted to the leftmost cells to work. (Option1 must be filled for upload to work)

If I create a calculated column with the 6 columns concatenated together, it would create a single group of words that I could extract out the first,second,third,fourth word.etc. to the first,second,third,fourth cell. [img]https://ibb.co/dkRWB2b[/img]

All columns right of and including concatenate will be hidden in the output to .cfv.

Sorry for the confusing post.
I hope I have explained it well.
Any ideas would be greatly appreciated.

Adrian

Adrian:

Thank you for the additional information. It seems to confirm my understanding that you are trying to create a text file where each SKU is a single line of variable length depending on how many product characteristics are passed. My suggestion remains the same. If a problem isn't easily solved, break it up into smaller pieces and conquer them individually. My second suggestion would be that, if all else fails, get your Adagio consultant involved.

Thank you Bruce
I was thinking about joining and splitting in an excel way of thinking,
Gridview does not contain the function substitute which was the key to split the nth word.
Upon staring at the grid I decided to scrap the concatenate method and put together a If / then / and function.

it took some time but I have achieved what I was after.
[img]https://ibb.co/0tLc4Qc[/img]

Thank you
Adrian

I am glad you figured it out, Adrian.