Log In
Register
Home
February 2, 2013
Offline
Hi Hi,
Can some one help me to update the Cust Art no details in summary sheet in (U4 - cell) based on the item code, this data i have updated in JAN TO APR (G column)
Moderators
January 31, 2022
Offline
I've formatted the data table as a structured Excel table so that you can use table/column references.
Then, a formula like this in U4 on the Summary sheet will do what you ask for:
=TEXTJOIN("/",,FILTER(Table1[Cust Art No],Table1[item Code]=B4))
File attached.
February 2, 2013
Offline
Hello Riny van
Thanks for your support its not working in office 2019, possible to share other formula.
Trusted Members
Moderators
November 1, 2018
Offline
For 2019 I think you can just remove the FILTER function from Riny's formula and replace it with IF:
=TEXTJOIN("/",,IF(Table1[item Code]=B4,Table1[Cust Art No],""))
February 2, 2013
Offline
Hi Velouria
Thanks for your help but its working only for few data's only other showing empty.
Moderators
January 31, 2022
Offline
I can't open the xlsx file. Please try uploading it again.
February 2, 2013
Offline
i have attached again.
Trusted Members
Moderators
November 1, 2018
Offline
Enter it with Ctrl+Shift+Enter then copy down.
February 2, 2013
Offline
Thanks Velouria its working fine, but small changes i need, there is a 2 record in (JAN TO APR) 87187 item code,
| itemcode | GroupName | Name | UOM1 | Norms | RequiredQty | CustArtNo | Order | Month |
| 87187 | Cut Components | 3 02 94 400 Print M1343ORA Orange Size 39-40(I) | Prs | 1 | 20.00 | 52425-Normal | 589/1 | Feb-24 |
| 87187 | Cut Components | 3 02 94 400 Print M1343ORA Orange Size 39-40(I) | Prs | 1 | 30.00 | 52425-Normal | 589/2 | Apr-24 |
Possible to show remove duplicate item which is showing double time in below Cust Art No cell.
| GroupName | Item Code | Name | UOM1 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | Total | Cust Art No |
| Cut Components | 87187 | 3 02 94 400 Print M1343ORA Orange Size 39-40(I) | Prs | - | - | - | - | 20.00 | - | 30.00 | 50.00 | 52425-Normal/52425-Normal |
Trusted Members
Moderators
November 1, 2018
Offline
In 2019 that would definitely not be a small change! I can't think of any way offhand without a lot of helper columns and/or terrible performance.
Does it have to be a formula rather than something like PowerQuery? Is VBA an option?
1 Guest(s)
