Trying to Use Join and Collect in a function, Says Calculating, but blank result

Terri-Lynn Morrison

I am having an issue getting this formula to work for me. This is based off a previous question asked and answered but I can not get it to work for me.

Sheet "1.Master" where I want the consolidated information to go contains [Site No] column (unique identifier/primary column) and a column called "Zoning" among 50 some other columns. [Site No] column is the first column in this sheet.

Sheet "SitePolygons_Zoning" contains [Site No] (not always unique) {SitePolygons_Zoning Range 2}, and [Zoning] {SitePolygons_Zoning Range 1}

Formula based on a prior question/response is:

=JOIN(COLLECT({SitePolygons_Zoning Range 1}, {SitePolygons_Zoning Range 2}, [Site No]1), ",")

When I type in this formula into the column "Zoning" on sheet "1.Master" it says it is calculating, but nothing appears.

Genevieve P.

Hi @Terri-Lynn Morrison,

I answered you on the other post so I'll copy my response here as well:

I tested your formula and it worked on my sheet as expected, so your syntax is correct. I would try logging out/in again to see if that helps, or accessing the sheet from a different browser. Try drag-filling it into a different cell, too, or creating a copy of the sheet and testing there.

If the result is blank, it could be that the formula isn't finding a match for your Site No. Could you post a screen capture of each sheet, showing the Site No column and the Range 2 Column in the other sheet? (But please block out any sensitive data!)

Thanks,

Genevieve

Paul Newcome

If you are getting the "Calculating" then it sounds like you are attempting to process a lot of data and/or you have a lot going on in either one or both of your sheets.

When this happens, formulas can be slow to update correctly. An additional step that I would suggest is that you also clear your cookies and cache after logging out but before you log back in.

I also agree that screenshots would be very helpful.

Terri-Lynn Morrison

Thank you both for your posts. I have cleared Browser Cache/Cookies in Google as that is the browser we can use and I have signed in/out. Nothing changed. I originally had the Site No from SitePolygons with a calculation to add in the leading zeros, but changed it to a number, but that still has not changed anything.

Genevieve P.

Hi @Terri-Lynn Morrison

I notice in your Reference Window, both ranges say "Range 1" as their name.

Can I make sure that you're inserting a new reference for each of your ranges in your formula, versus clicking a reference and selecting edit? The title of each range should update when you click on the column name... one should say "Range 1" (zoning) and one should be "Range 2" (site no).

Terri-Lynn Morrison

Hi. I realized what I did with the screenshots, I thought I could do it quicker if I just highlighted the column I needed to screenshot. I believe I made new references 3 & 4 and changed the calculations. Showed "calculating" briefly then nothing.

Paul Newcome

How exactly is the site number populated in each of your sheets?

Terri-Lynn Morrison

In 1.Master, the site number is manually typed in and when clicked in displayed as '00001 and Column Type is set to Text/Number

In SitePolygon_Zoning, the site number originally was a calculation so the leading zeros would be added. Since the formula wasn't working, I manually typed in the site number and it displays as '00001 and Column Type is set to Text/Number

The Calculation I did have in Site Number ="0000" + Site6

In SitePolygon_Zoning there are Site No missing from the sequence (Site 00049 & 00050) and likely others, Do I need a IF statement combined with this one?

Terri-Lynn Morrison

I just tried making a new sheet with only the numbers typed in and adding the formula to Column6, but I still get no results. To try testing the theory that it would not work with numbers missing from SitePolygon_Zoning, I only selected up to line 8 in my references for both Zoning and Site no which should have given me results for all 6 site numbers.

As a workaround, Is there a way I can consolidate the Zoning into the appropriate lines in the sheet SitePolygon_Zoning, then just use a vlookup or cell reference from this sheet into 1.Master?

Paul Newcome

So they are both being output as text strings. I tested using

="000" + [Column Name]@row

and

'0001

just to see if there was definitely a match, and there was. Using the apostrophe to generate the text string can be matched against the formula driven text string.

So that shouldn't be the problem, but lets try this just to be sure...

Enter the following formula into any text/number cell on your target sheet and see what happens...

=MATCH([Site No]1, {SitePolygons_Zoning Range 2})

It SHOULD produce a number.

Terri-Lynn Morrison

I pasted it into a column at the back of 1.Master on line 19 (Site number 19) and the result was 1

Paul Newcome

Awesome. So it is matching. Is the match for [Site No]1 on row 1 of the other sheet? If so then we are both finding the match and outputting the correct location.

Now enter...

=JOIN({SitePolygons_Zoning Range 1})

What happens with that one?

Terri-Lynn Morrison

Yes, both sheets have Site No 1 on row 1

=JOIN({SitePolygons_Zoning Range 1}) brought in all the results from SitePolygonZoning without any deliminator.

AJAPAPAGDC2AGRF1DC1DC1DC1DC1DC1 etc. I can't tell if it actually grabbed all the results from the zoning column, but I think it did.

Paul Newcome

Ok. So both pieces work on their own, but for whatever reason do not work when combined.

I can't remember if we've tried this or not, but...

Log out. Clear cookies and cache. Log back in. Try it in that order. It seems like the sheet is being buggy.

@Genevieve P I am not sure if you have any other ideas, but at this point I am going to recommend reaching out to support. Since both pieces work on their own, they should be working when put together. The fact that they are not leads me to believe that it may be something on the back-end of things.

Terri-Lynn Morrison

I followed the log out, clear cookies and cache, log back in and nothing shows. I had someone else look at the sheet that is shared with them, but nothing on their end either.

Thank you for taking the time to look into this with me, I really appreciate it. I will likely submit a ticket to support.

Paul Newcome

Sorry I couldn't be of more help, but I am stumped on this one.