Hi,
I'm hoping someone can help me. I apologise in advance as it might take me a bit to explain what I need.
I'm trying to dynamically/automatically add new rows underneath the existing row that contains a FILTER formula that is working across multiple sheets.
I will try my best to explain below.
I have a table named SHTABLE in a range of $A$1:$F$37916 on a sheet named SHTableSheet.
The table contains automotive part numbers with each being mapped per vehicle to a unique identifier in column F.
Below is an image of a section of the table. I have hidden Column E as it contains sensitive URL endpoints.
I have another sheet named Entry Sheet-Part Number.
On this sheet cells $C$5:$C$315 contain data validation dropdown lists of =SHTableSheet!$B:$B.
Once I select a value from the dropdown the cell in that row in column F updates with the identifiers that match that part number.
I have the following formula in cell F5 =IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = C5)),"NO IDENTIFIER FOUND").
This formula is repeated in all of column F with the = C5 value changing per row all the way down to = C315))
The final sheet is called UploadSheet and has data in a range of $A$4:$BI$315.
Most of this data is compiled via CONCAT and VLOOKUP formulas from other sheets in the workbook.
I'm trying to find a way of entering in the identifier data from column F in the previous sheet (Entry Sheet-Part Number).
The Identifier1 column in cell T5 features this formula =IFERROR(FILTER(SHTable[Identifier], SHTable[Part Number] = 'Entry Sheet-Part Number'!C5),"NO EPID FOUND")
Unfortunately, this doesn't work because the formula causes the #SPILL! error and it overwrites the next rows and their variation of that same formula.
Identifier2 cell U5 contains the same formula =IFERROR(FILTER(SHTable[Identifier], SHTable[Part Number] = 'Entry Sheet-Part Number'!C5),"NO EPID FOUND")
I have left the formulas out of the other U cells to give a visual representation of what is happening.
However, this then means row 8 is now a spilled filtered result of U5 which in turn equals C5 from the sheet (Entry Sheet-Part Number).
I need each row to remain with the same row value in column F from the Entry Sheet-Part Number sheet.
Identifier3 cells contain this formula =IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = 'Entry Sheet-Part Number'!C5)),"NO EPID FOUND")
With the = 'Entry Sheet-Part Number'!C5 value changing per row down to C315.
QUESTION
Is there some formula tweaking or VBA that can do the following;
Analyse how many identifier filtered results are in each cell of column V from cell $V$5:$BI$315 by looking at the ", " delimiter?
Then add new rows between the next V cell that contains a formula, in this example of cell V5 the next formula would be
=IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = 'Entry Sheet-Part Number'!C6)),"NO EPID FOUND")
In this example the VBA would see the 4 results in cell V5 and add 3 new rows below row 5 and place the filtered results into these new rows.
So cells V5,V6,V7 and V8 would display the Entry Sheet-Part Number C5 results whilst row V9 would now return the Entry Sheet-Part Number C6 results.
Obviously this VBA would need to be highly dynamic and I'm not sure of the limitations of the logic and whether this is even possible.
I do hope this somewhat makes sense.
The reason I need these identifiers to stay attached to the original row is all of the data is the other cells of the same row is SKU specific and used in an ecommerce setting.
Thanks so much if you've taken the time to read all of this.
By all means if you have a solution let me know.
I'm hoping someone can help me. I apologise in advance as it might take me a bit to explain what I need.
I'm trying to dynamically/automatically add new rows underneath the existing row that contains a FILTER formula that is working across multiple sheets.
I will try my best to explain below.
I have a table named SHTABLE in a range of $A$1:$F$37916 on a sheet named SHTableSheet.
The table contains automotive part numbers with each being mapped per vehicle to a unique identifier in column F.
Below is an image of a section of the table. I have hidden Column E as it contains sensitive URL endpoints.
I have another sheet named Entry Sheet-Part Number.
On this sheet cells $C$5:$C$315 contain data validation dropdown lists of =SHTableSheet!$B:$B.
Once I select a value from the dropdown the cell in that row in column F updates with the identifiers that match that part number.
I have the following formula in cell F5 =IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = C5)),"NO IDENTIFIER FOUND").
This formula is repeated in all of column F with the = C5 value changing per row all the way down to = C315))
The final sheet is called UploadSheet and has data in a range of $A$4:$BI$315.
Most of this data is compiled via CONCAT and VLOOKUP formulas from other sheets in the workbook.
I'm trying to find a way of entering in the identifier data from column F in the previous sheet (Entry Sheet-Part Number).
The Identifier1 column in cell T5 features this formula =IFERROR(FILTER(SHTable[Identifier], SHTable[Part Number] = 'Entry Sheet-Part Number'!C5),"NO EPID FOUND")
Unfortunately, this doesn't work because the formula causes the #SPILL! error and it overwrites the next rows and their variation of that same formula.
Identifier2 cell U5 contains the same formula =IFERROR(FILTER(SHTable[Identifier], SHTable[Part Number] = 'Entry Sheet-Part Number'!C5),"NO EPID FOUND")
I have left the formulas out of the other U cells to give a visual representation of what is happening.
However, this then means row 8 is now a spilled filtered result of U5 which in turn equals C5 from the sheet (Entry Sheet-Part Number).
I need each row to remain with the same row value in column F from the Entry Sheet-Part Number sheet.
Identifier3 cells contain this formula =IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = 'Entry Sheet-Part Number'!C5)),"NO EPID FOUND")
With the = 'Entry Sheet-Part Number'!C5 value changing per row down to C315.
QUESTION
Is there some formula tweaking or VBA that can do the following;
Analyse how many identifier filtered results are in each cell of column V from cell $V$5:$BI$315 by looking at the ", " delimiter?
Then add new rows between the next V cell that contains a formula, in this example of cell V5 the next formula would be
=IFERROR(TEXTJOIN(", ",TRUE,FILTER(SHTable[Identifier],SHTable[Part Number] = 'Entry Sheet-Part Number'!C6)),"NO EPID FOUND")
In this example the VBA would see the 4 results in cell V5 and add 3 new rows below row 5 and place the filtered results into these new rows.
So cells V5,V6,V7 and V8 would display the Entry Sheet-Part Number C5 results whilst row V9 would now return the Entry Sheet-Part Number C6 results.
Obviously this VBA would need to be highly dynamic and I'm not sure of the limitations of the logic and whether this is even possible.
I do hope this somewhat makes sense.
The reason I need these identifiers to stay attached to the original row is all of the data is the other cells of the same row is SKU specific and used in an ecommerce setting.
Thanks so much if you've taken the time to read all of this.
By all means if you have a solution let me know.
