I am currently separating a comma-separated cell and splitting it into separate rows. This cell is a function and pulls data from another sheet, so it cannot really be changed.
Main Table & snip of function:
{=TEXTJOIN(", ",TRUE,IFERROR(IF(MATCH('Net Data'!G2:G500,IF(A$1='Net Data'!D2:D500,'Net Data'!G2:G500,""),0)=MATCH(ROW('Net Data'!G2:G500),ROW('Net Data'!G2:G500)),'Net Data'!G2:G500,""),""))}
While trying to separate and split, Power Query states "Cannot convert to number"
Power Query & Error Message
This new separated data is the reference for the next calculations, which reference other sheets and match it to "Associated Tractors". "Associated Tractors" must stay the exact same because that is how the data is presented in the other tabs. As you can see the blank cells are causing 0's in the other cells because it has nothing to search for.
Final Table, with missing data
It would have to search for "350 ERG" in order to pull proper data from other tabs. Also, the values in "Associated Tractors" is dynamic (hence the function), so sometimes it will be all numbers or a mix of numbers and text.
I really appreciate the input! I've messed around with number formatting but I can only get it to accept specific text or just numbers.
Data Type format
All I am really trying to do is split the "Associated Tractors" cell into rows so that the new table can reference each individual tractor model (ie: 212).
Thanks a bunch!