Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?

AlfredDB

New Member
Joined
Nov 7, 2023
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

1..JPG


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))

2..png


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.

3..JPG


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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top