Add rows with different suffices - Multi row formula

[G_SAND]

Hi

I have a set of data where there are some fields I want to add together but they have different suffices:

Material	Batch	Value
A1	X	1
A1	X-2	3
A2	Y	2
A2	Y-10	6
A3	Z	7
A3	Z-6	4

This is what I want the output to look like:

Material	Batch	Value
A1	X	4
A1	X-2	3
A2	Y	8
A2	Y-10	6
A3	Z	11
A3	Z-6	4

I can't remove the suffix from the data set as there are other batches that need the suffix there, its only when there is a duplicate in the core batch number for the same material. The second batch can stay in the data set as I am summarizing by first batch further in the workflow.

I can use a multi row formula to do the calculation for a specific suffix:

If [Row+1:Batch] = [Batch]+ "-2" then [value] + [Row+1:value] else [value] endif

So essentially I need the calculation to see any batch with "-" and then any possible character after that - like -* or -x

Thanks

[MarqueeCrew]

Try this:

Use the multi-row formula with configuration of:

Update the value field
Group on material

[value] + [Row+1:value]

Let’s see if it does what you want with your real data.

[Kenda]

Hey @G_SAND!

See if the attached v11.0 workflow accomplishes what you're looking for. I used two Multi-Row Formula tools followed by a Summarize and then a Join. Basically, this is creating a counter for ever row that has the same first character in the Batch column. It then sums those up that are in the same group and only keeps the maximum value. The Join at the end is to bring all the data back together. Hope this helps!

[G_SAND]

I fear I haven't been detailed enough in my first post. Here is some more appropriate data:

Current Data

Material	Batch	Stock
A1	15493-4SLE	0.380
A1	15493-5SLE	22.766
B1	19171	34,317.000
C1	20313	844.770
D1	EU20342	17.824
D1	EU20343	398.500
E1	13266	8.211
E1	13266-2	2.100

The formula needs to identify the same core batch number with a "-" and any suffix afterwards. So it sees 15493-4SLE and 15493-5SLE as the same and 13266 and 13266-2 as the same. Not all the materials will have multiple batches and if the do, not all will have a "-" suffix.

This will be the output:

Material	Batch	Stock
A1	15493-4SLE	23.146
A1	15493-5SLE	22.766
B1	19171	34,317.000
C1	20313	844.770
D1	EU20342	17.824
D1	EU20343	398.500
E1	13266	10.311
E1	13266-2	2.100

The only ones to be added together are A1 and E1.

Further in the workflow I only keep the first batch so I end up with this:

Material	Batch	Stock
A1	15493-4SLE	23.146
B1	19171	34,317.000
C1	20313	844.770
D1	EU20342	17.824
E1	13266	10.311

I can't focus on the first 5 characters as that would mean the values for D1 would also be combined.

Hope this clarifies.

[Kenda]

@G_SAND In my previously attached workflow, change the first Multi-Row Formula tool to say this:

iif(REGEX_Replace([Row-1:Batch], "(.*?)\-.*", "$1")=REGEX_Replace([Batch], "(.*?)\-.*", "$1"),[Row-1:NewField],[Row-1:NewField]+1)

Then, in the second Multi-Row Formula tool, make sure NewField2 is a double since you have decimals (and the field you're adding is a double as well, here 'Stock'). The rest should be fine as it was. Hopefully this solves your predicament. 

[mohd_syarifhidayat]

hey dude,

Here I attached my workflow. Let me describe little bit about it.

First, I create some formula to decide which material contain '-' values.

After I know which material has same materials, then I total up the stock based on the materials

Take a look at the workflow. Hope it solved your problem.

[danrh · Accepted solution]

You should be able to do it with the below:

The Multi-Row Formula tool checks if everything before the "-" is the same as the previous row.  If it is, it changes the Batch to equal the previous Batch.  Then a simple sum and presto!

[G_SAND]

Thanks @danrh

I'm sure the others work as well but I am going for this one.

Thanks