Combine two cells in a certain way

[HaraldPalma]

I tried many workarounds, but none did actually work. What I am trying to achieve: 

• cell one has orders (700, 1000, 3000, 400)
• cell two has sizes (M, XL, L, S)
• cell three "Joint" should display 700M, 1000XL, 3000L 450S

I tried many formulas and scripts, the closed I came to was: ARRAYJOIN(size & orders)

but this only displays MXLLS700;1000;3000;400

Maybe somebody has an idea - thank you very much!

[azizgurkan]

You can use a formula field for this task, but ARRAYJOIN alone will not be sufficient. The formula below will give you what you want, but it won't be an efficient solution:

MID({Orders}, 1, FIND(",", {Orders}) - 1) & MID({Sizes}, 1, FIND(",", {Sizes}) - 1)

& ", " &

MID({Orders}, FIND(",", {Orders}) + 2, FIND(",", {Orders}, FIND(",", {Orders}) + 1) - FIND(",", {Orders}) - 2) & MID({Sizes}, FIND(",", {Sizes}) + 2, FIND(",", {Sizes}, FIND(",", {Sizes}) + 1) - FIND(",", {Sizes}) - 2)

& ", " &

MID({Orders}, FIND(",", {Orders}, FIND(",", {Orders}) + 1) + 2, FIND(",", {Orders}, FIND(",", {Orders}, FIND(",", {Orders}) + 1) + 1) - FIND(",", {Orders}, FIND(",", {Orders}) + 1) - 2) & MID({Sizes}, FIND(",", {Sizes}, FIND(",", {Sizes}) + 1) + 2, FIND(",", {Sizes}, FIND(",", {Sizes}, FIND(",", {Sizes}) + 1) + 1) - FIND(",", {Sizes}, FIND(",", {Sizes}) + 1) - 2)

& ", " &

RIGHT({Orders}, LEN({Orders}) - FIND(",", {Orders}, FIND(",", {Orders}, FIND(",", {Orders}) + 1) + 1) - 1) & RIGHT({Sizes}, LEN({Sizes}) - FIND(",", {Sizes}, FIND(",", {Sizes}, FIND(",", {Sizes}) + 1) + 1) - 1)

I think the best solution would be to use a scripting automation. You can use the code below:

let table = base.getTable("Table 3");
let query = await table.selectRecordsAsync(table);

for (let record of query.records) {
    let orders = record.getCellValue("Orders");
    let sizes = record.getCellValue("Sizes");

    if (orders && sizes) {
        let ordersArray = orders.split(", ");
        let sizesArray = sizes.split(", ");
        let combinedArray = ordersArray.map((order, index) => `${order}${sizesArray[index]}`);
        let combinedString = combinedArray.join(", ");

        
  await table.updateRecordAsync(record.id, {
            "Combined": combinedString
        });
    }
}

Data View:

Automation View:

[dilipborad]

Hello @HaraldPalma 
@azizgurkan gives the best possible solution using formulas and Automation scripts.

But there is one small concern that I think you need to understand.
Right now as per the Data view the Sizes(M, XL, L, S) of Orders(700, 1000, 3000, 400) are on the same orders as per example.
But if there are no sizes field available and in case the order of values is not right as per Orders value then you need to predefine those values and identify them in the script. The update other field.

👍

[TheTimeSavingCo]

Hmm, I assume this is data imported from somewhere else?  Not too sure what your business logic is, but is there any chance you could export it so that each order is a single row instead maybe?

[HaraldPalma]

Good point @TheTimeSavingCo, but in this case, I don't know a solution :-).

I built an internal ordering system and have items of different sizes. The sizes are displayed as "single select". When an order is created, I collect my data summed in the product table (amount and size, in one row) and the ordering table individual; product - amount - size. I can group the table (per size, item, user). If I do size, I get the correct result. But I would like to create a "summ it all up" table that displays product - orders (summ amount) and size in one row. Maybe my thinking is too complicated, happy with every suggestion! 

[TheTimeSavingCo]

Ah, what if you used a formula field to combine it together and then used a lookup field to display that in your "Sum it all up" table?

Link to base

[HaraldPalma]

Thank you very much! This is actually a very clever solution. But if you have 3 orders of the same size, it still displays 300S, 200S, 100S and not 600S - what I would want. Like in this PIC, 8920M and 3568M

 

[TheTimeSavingCo]

Ah, I think you'd need a third table for that to sum stuff up then

Link to base