Hello there,
I have the following problem to solve (really hard one), i have a list of row (> then 100) that have this kind of pattern:
N° | Order | Sub_order | Material | Description | Material_2 | Valute to withdraw_quote | Material description | Qty to be picked_quote | Date_withdrawal_quote | Material Doc. | Date_withdrawal | Qty_withdrawn | Delta | Value_withdrawal |
1 | Project1 | 2306394 | G11220AB004 | A | 268W725E01 | 281.183,40 € | Material a | 54 | 02/05/2021 | 4900626134 | 20/04/2021 | -2 | 52 | 10.414,20 € |
2 | Project2 | P1572067 | G11220AB003 | B | 268W725E01 | 281.183,40 € | Material a | 54 | 11/08/2021 | 0 | 54 | - € | ||
3 | Project1 | 2306394 | G11220AB004 | A | 268W725E01 | 281.183,40 € | Material a | 54 | 02/05/2021 | 4900636338 | 05/05/2021 | -53 | 1 | 275.976,30 € |
Now, i need to update this table with the following logic:
1) sort all rows by ascending Date_withdrawal value (this should be a simple sort) then:
2) Check row 1 and compare it with all the sheet:
if you find a row that have both the same values (for example line 3)
the result should be something like:
N° | Order | Sub_order | Material | Description | Material_2 | Valute to withdraw_quote | Material description | Qty to be picked_quote | Date_withdrawal_quote | Material Doc. | Date_withdrawal | Qty_withdrawn | Delta | Value_withdrawal |
1 | Project1 | 2306394 | G11220AB004 | A | 268W725E01 | 281.183,40 € | Material a | 54 | 02/05/2021 | 4900626134 | 20/04/2021 | -2 | 52 | 10.414,20 € |
2 | Project2 | P1572067 | G11220AB003 | B | 268W725E01 | 281.183,40 € | Material a | 54 | 11/08/2021 | 0 | 54 | - € | ||
3 | Project1 | 2306394 | G11220AB004 | A | 268W725E01 | 0€ | Material a | 52 | 02/05/2021 | 4900636338 | 05/05/2021 | -53 | 1 | it will be updated later |
this logic should work also with an hypothetical 4th row. in that case the system should use the same logic but it should consider the fact that 3 withdrawal have been made
can someone help me?
many thanks!
Hello @Guglielmo91
Sounds like you need an iterative macro here! I've created a workflow with your sample data to get you started. Try it out with your full set of data.
If you need additional help with iterative macros, check out these resources:
Hi Kenda, Thanks you so much for your help!! I had no idea of this tool, I've tried to incorporate your code inside of mine but I'm experiencing some problems. These are more certainly linked to the fact that in my data set i have columns that were not present in the example that i posted and the name of them have been changed for "translation" reasons.
I have tried to rename them accordingly to your program but it not seems to work. This is how i have renamed them:
an this is the issue that i'm facing:
I don't want to take advantage of your kindness but I can't find the issue here. Can you help me? maybe i can send you the entire list of data and you can check if there is anything wrong.
thanks in advance
Guglielmo
Hi @Guglielmo91 ,
I've built this using a multi-row formula and sampling the last of the group as specified.
During the sampling I've used a join and taken the left to determine if the last in a group is the same as the first (there's only one per group) and these will be ignored:
I hope this helps,
M.
Hi @Guglielmo91
It looks like when I copied your sample data over, there was a space at the beginning of the "Value to withdraw_quote" field name and that is why you aren't able to find it when you rename the field.
If you right click on the macro, you can open it to see what's going on inside. You can either change the field name that the macro uses in there or you can change the name when you rename it in the Select tool.
Hope that helps!