Help

Combine two cells in a certain way

Topic Labels: Data Formulas
584 7
cancel
Showing results for 
Search instead for 
Did you mean: 
HaraldPalma
5 - Automation Enthusiast
5 - Automation Enthusiast
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!
7 Replies 7
azizgurkan
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

azizgurkan_1-1720191272069.png


Automation View:

azizgurkan_2-1720191306853.png

 

 

 




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.

👍

 

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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! 

Bildschirmfoto 2024-07-06 um 10.41.54.png

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?

Screenshot 2024-07-06 at 9.58.31 PM.png

Screenshot 2024-07-06 at 9.59.41 PM.png

Link to base

HaraldPalma
5 - Automation Enthusiast
5 - Automation Enthusiast

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

image.png

 

Ah, I think you'd need a third table for that to sum stuff up then
Screenshot 2024-07-07 at 4.30.53 PM.png

Screenshot 2024-07-07 at 4.30.55 PM.png

Screenshot 2024-07-07 at 4.30.57 PM.png

Link to base