TEXTJOIN IF returning intermittent #VALUE error

[bluerobot]

Hi all, I'm trying to fix an issue with a formula I did not create.

On tab "Weekly Report", column A contains customer ID numbers. The same customer ID may repeat, as each line references a different invoice number.

On tab "Customer Notes", column A also contains customer ID numbers. Again, it may repeat, as each line references an individual customer note entered into our billing system. Columns D:G show the note title, note text, a blank column, and date. Column H concatenates the note title, text, and date.

Back on the Weekly Report, we want column U to match the customer ID in column A to the same customer ID in column A on the Customer Notes tab, and join all related rows from column H. The goal is for each line on the Weekly Report to show all notes that have been entered for that customer ID.

The formula used in column U of the Weekly Report looks like this: =TEXTJOIN(",",TRUE,IF('Customer Notes'!A:A='Weekly Report'!A2, 'Customer Notes'!H:H,""))

On some lines, it works perfectly. On others, it returns a #VALUE error. I cannot find commonalities between the errors or the successes.

Things I have checked / tried:

• Text limits. None of the resulting strings from Customer Notes H:H exceed the 32k(ish) limit I have read about. Some of them exceed the 255 limit I have also seen mention of, but are still successful.
• A coworker built an XLOOKUP with me but this did not work; it would only bring back the first row from Customer Notes H:H, rather than all rows that have the correct customer ID.
• The number of rows on the Weekly Report tab vs. the number of rows on the Customer Notes tab do not seem to correlate, whether it has a #VALUE error or is successful. There are customer IDs that show up multiple times on the Weekly Report that are successfully pulling in single or multiple lines from the Customer Notes tab. There are customer IDs that only show up once on the Weekly Report that are successfully pulling in single or multiple rows from the Customer Notes tab. And others with the same criteria are giving the #VALUE error.
• Multiple small changes such as using a space as the delimiter instead of a comma.

I am not very knowledgeable in Excel, but I have been told to just figure this out. I've spent 1-2 hours a week on this for a few weeks now without success, though. Any help at all is appreciated!

 

[Fluff]

It sounds as though you may have #value errors on the Customer Notes sheet.
Also it is advisable not to use whole column references, especially with array formulae like that, as it will slow down the entire workbook.

 

[bluerobot]

It sounds as though you may have #value errors on the Customer Notes sheet.
Also it is advisable not to use whole column references, especially with array formulae like that, as it will slow down the entire workbook.

Thank you for replying!

Customer Notes has no #VALUE errors shown.


The wb is pretty slow. I may try editing it to not use the whole columns but may also run into limitations from the boss who created it.

 

[Fluff]

Some of them exceed the 255 limit

Missed this 1st time round, that will cause you to get that error.

 

[bluerobot]

Missed this 1st time round, that will cause you to get that error.

Unfortunately, I don't know if that is causing the error. I may have been unclear, but some of the text strings exceed 255 (according to =LEN) but do not return a value error, they are successfully pulling in the notes.

 

[Fluff]

I will work as long as none of the cells with over 255 characters are on the same row as a value in Col A, that matches A2

 

[bluerobot]

I checked the Customer Notes lines that have >255 against the #VALUE errors on the Weekly Report, tested by shortening any lines that went over, and the errors resolved.

Thank you SO MUCH, I'm so relieved to not be banging my head against a wall anymore!

 

[Fluff]

Glad to help & thanks for the feedback.