TEXTJOIN IF returning intermittent #VALUE error

bluerobot

New Member
Joined
Nov 23, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,073
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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.
 

bluerobot

New Member
Joined
Nov 23, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,073
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

bluerobot

New Member
Joined
Nov 23, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,073
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,045
Messages
5,767,810
Members
425,437
Latest member
blaix

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
Top