Combine two cells in a certain way

Topic Labels: Data Formulas
584 7
cancel
Showing results for
Did you mean:
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
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:

Automation View:

9 - Sun

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.

👍

18 - Pluto

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?

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!

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?