SOLVED

Problem with getting CONCATENATE text combined with an IF/THEN and nested VLOOKUP to display...

Brass Contributor

I am including the workbook where I am having two challenges with detailed notes on each problem.

Problem #1 is on sheet "03 - eABC Responses"

Problem #2 is on sheet "01 - v2.22"

 

Problem #1 is my primary issue but the other is in need of a solution that is WAY over my head as well and it will eventually need to be addressed.

 

Thanks in advance for your help on these!

 

Douglas

47 Replies

@Douglas997t 

 

I've been hoping that somebody else might venture into this hornet's nest of a spreadsheet and I could learn from their solution. But with over 50 views and no reply, I think all who've looked at it have despaired, as I did.

 

A suggestion, then: don't try to do all of this Issue #1 in one unintelligible formula. Create some "helper columns" to do parts of the task, and then incorporate the results.

 

But the reality is, this is probably a spreadsheet in need of fundamental re-design, or maybe conversion to some other software (Access?)....

@mathetes 

I was hoping for your friendly post. Now I can get to the not-so-friendly post. :unamused:

 

This is a *** workbook. 21 MB. Several hundred notes and textboxes.

The person who created this workbook shall be cursed forever.

 

I wonder if anyone really voluntarily works with this spreadsheet.

I wasn't able to delete columns/rows to make the file smaller. Not enough memory.

 

And I agree with your conclusion: Rebuild from scratch.

 

Hey not so friendly poster, nice to meet you. I am the guy that designed it for use by...my private equity group. We buy commercial property nationwide and use this as a mock CRM to not only run multiple fairly complicated analytics but act as more of an App than a simple spreadsheet. The way it lays out, the conditional formatting and notes are made so that a VA or other lower end personnel could work through data entry and have answers to simple questions at their finger tips.

I would have preferred to have an actual App developed but the problem with going down that road is the frequent need to modify strategies and the calculations that become necessary due to them. Also, we need to be able to track thousands of properties as they work through our acquisition pipeline. I have tested and/or played with numerous CRMs and from Sales Force to Goldmine to...you name it. Nothing does what we need. Excel provides a flexible platform for our needs. It may not fit your vision of cutting edge and contemporary Excel formula usage but it does get the job done. My post wasn't to get into a conversation about, "do you guys like my work or not". It was to answer a somewhat simple formula problem I couldn't figure out on my own. It is fine if you choose not to do what I posted about and instead look at things that are absolutely irrelevant to my needs. If you can help, that would be great...there are two issues that I outlined within two inserted notes. You can pass this rather lengthy explanation to the other gentleman as well...

You guys make a good case for only giving as much information as you can handle rather than being open about the broader picture. Live and learn...yah?

@Douglas997t 

You can pass this rather lengthy explanation to the other gentleman as well...

 

I guess that I'm "the other gentleman"--your post is open to everybody to read even if you were responding primarily to my on-line colleague @Detlef Lewin. So I'll venture to reply.

 

First, FWIW, I did offer a possible solution to that presenting problem: break up that multi-leveled nested formula into several "helper columns," with each doing its own thing. Then pull those results together with a simpler, more intelligible, formula. The point being that trying to create a single formula, multi-layered-nesting and all, can be satisfying when it works, but is not considered good practice, if only because it is very difficult to debug, almost impossible to maintain, and that's even if you were (as you are) the original creator.

 

Another couple of suggestions, if I might (based on reading professional Excel resources; I'm just an amateur myself), having looked at and tried to understand your workbook:

  1. work to separate input, and the basic data tables that contain that input, from areas that process the raw data and definitely from the output -- mixing all those three essential but distinct components also interferes with clarity and can interfere with Excel's power. This might mean, in your case, having separate workbooks that call on one another for essential data. "Might mean"--I'm not saying it must mean. But it definitely would mean separate sheets for input, processing and output. As @Detlef Lewin has pointed out, that current workbook is so very large that it stretches the limits of most computers. Separating the components could make it both faster and less resource demanding.
  2. hold off on elaborate formatting--in particular things like merged cells, those fancy items across the top--because all of those bells and whistles can interfere, in various ways, with smooth functioning. Add them after it's all functioning, by all means. But here's where that first point comes into play--you can add formatting to the output sheets for the sake of clarity, pop, impact (whatever), but keep the input and processing areas clean and clear. Use something like a yellow background in cells for data entry, but you don't need to make input and processing pages all that colorful; they're meant to be in the background.

 

@Douglas997t 

 

This attached document is somewhat dated--newer Dynamic Array functions, among other things, will have rendered some of the advice obsolete. And on page two there are two "principles" in a row where each could be read as contradicting the other, and, as it happens, they deal with my point #1 above. In the case of your workbook, I'd stick with the first principle, using separate tabs for those distinct components of input, logic (aka processing) and output.

 

For the most part, however dated some of its points, the document remains worth reading and considering in the design of a workbook.

Good morning Mathetes and thanks for your response. I didn't mean to intimate any negative connotations upon you by referencing you as "the other gentleman". Only that you had commented and he connected some of his reply to yours. Responding to his less friendly reply came to mind first.

 

It's hard to take a glance at something like this without understanding the rationale behind a certain layout/design methodology.

 

That said, I do understand what you are saying in terms of breaking it up into multiple sheet and have to some degree done that here. I pull data from v2.22 into eOpener and eABC Response. But, v2.22 is the central landing zone for all data. And needs to be that way for practical use purposes. It is a live running track for data gathering amd trickle timing between the user, in most cases that is me, and my counterpart (the seller, commercial broker, lawyer or other family rep.). This is used a track to not only gather info required to map negotiations, due diligence requirements, acquisition Capital stack to stabilize/optimize the asset then conversion to long term financing and eventually proforma (projected) performance of the asset over time.

 

I will see how I can add helper columns and other ways to lessen the load including spreading the work between different workbooks. It is just far more convenient to have everything in one spot. Database management is best done in one single database and given that the main data entry point must function as a real time communications tool I don't know how it would function if it was spread out amongst more than one sheet much less across multiple workbooks. I can play with it and see if I can arrive at a practical solution.

 

I appreciate your input on this...

Ok, I will read through it. Thanks for your assistance...

@Douglas997t 

When I open a workbook I expect NOT to be confronted with a notes-cluttered screen.

 

A major reason for you big file size is that the UsedRange in sheet "01 - v2.22" is XE1048576. And the cells are mostly empty. You should delete those rows/columns. The same issue maybe with the other sheets.

Then there are at least two circular references in sheet "01 - v2.22" and in sheet "03 - eABC Responses". I don't know if this is on purpose or an error.

And I accidentally noticed that one cell has NO formula but an input text. That is CA26 in sheet "01 - v2.22".

 

The only way to "work" with the spreadsheet was to get rid of all sheets expcept the two mentioned above. And then copy the data and formulas in a new workbook. It was down to 1 MB.

 

Then I tried to understand your problem. But it is not clear what the desired result is.

 

For problem #1 you should try @mathetes tip with helper cells. That is a faster way to identify errors or problems.

 

Hello Detlef. I appreciate your response this morning.

As to your entry sentence...
A...the notes contained on sheet 01 - v2.22 and 03 - eABC response are only there for those who would see this post as initial guidance into the problem I was having. They will disappear once solutions are provided. Also it is important to note, this project is an internal document we will use to breakdown the viability of assets we are in negotiations on. It functions as a real time negotiating and tracking platform to be used when in phone conversations with potential sellers of commercial property. I have exhaustively scouted the CRM marketplace including Goldmine CRM, one that I developed and used successfully while the owner of both a real estate and mortgage brokerage in L.A. I wanted to find an out of the box solution to these types of issues but nothing even got close. Not even something as simple as,

1. Send outgoing email...action: move thread to a long term trickle stage until the recipient responds.

2. Upon response, move thread to a pause stage while we make the seller wait up to 3 days depending on where in the negotiating process we are.

3. Once we take off the pause and send our reply, they go back into the 30 day trickle stage and that timing rolls on and on until we buy. If the seller and I never align the trickle continues on and on or until the property is pulled from market.

Every CRM from free to $1,000s/mo. We're incapable of this. They could automate mail merges and the like, but incapable of what we need. I am attempting a manual version in Excel. We'll see if works out or needs some tweaks or, is destined to fail. I have no idea where this ends up. And most importantly to complete my response in this area, this is not for public consumption. Maybe an assistant, virtual or otherwise might be plugged intl to duplicate volume...we'll see about that...

I saw your comment about a large range taking up a lot of file size along with a notation "XE1048576". I am not understanding what that sequence is referencing.

It might reference a set aside range of rows beginning at A12:UU1500? Within these rows are an extension of all formulas used to drive future data entries that span the range A:UU in this sheet. Each property and owner's information for each property, calculations, etc. encompass 1 row. When this goes live I will add around 50-75 rows of data per day. Some will have mostly complete data entry and some where only a couple of cells contain values.

I have used a similar system for different purposes except that it started small and 20 or 30 rows were added as needed. In that case, the addition of rows might have been 5 rows a month so I could do that manually without worry about mis-handling the copy/paste. I believe pre-adding a larger chunk will be easier and more consistently performing than smaller increments despite the increased file size. The more often manual intervention is introduced the greater the likelihood of mistakes happening.

You said you were not clear on my problem. I will try to break it down differently than in the inserted note boxes.

I am responding from my mobile now. Will send this portion forward and work on better explaining the problem(s) I am attempting to solve from my home computer. I'll send something over a bit later.

Sorry for the elongated response...so much easier on the phone!

@Douglas997t 


I saw your comment about a large range taking up a lot of file size along with a notation "XE1048576". I am not understanding what that sequence is referencing.***

If you press CTRL+END you will be at XE1048576. Which is the endpoint of UsedRange.

But it should be VP1501.

 

I pressed CTRL + End on sheet "01 - v2.22" and the cursor did zip down to XE1048576 as you mentioned. I have no idea why this sheet thinks there is a range that has been at some point defined at all much less why it would end at that row. I have not knowingly defined any ranges in this sheet nor any other, I don't know how to do that nor what I might use it for. And, not all all sure where VP1501 comes into play. The last column in the data input section of this sheet is in column is currently column UU with formulas and possible inputs down to the row 1500. I have an temporary end column marker "x" in row 1501 to more quickly keyboard-navigate up and down when needed (CTRL + Upward/Downward Arrow). I apologize for the lack of understanding on my behalf. I'll need additional explanation or guidance on this one.

P.S. If what you are saying is true and correctable I can see why the size of this sheet ballooned like this. So, thanks for finding it in advance.
Sry to jump in at the middle here but thought I would mention if you have access to online version of Excel that it has a feature that will help find and reduce excess memory consumption like those ranges of blanks that aren't used. Might be helpful to try that out.
I am using Excel 365 so I assume that would contain that tool. Maybe you can direct me to where that tool is found in Excel's menu system? I have never been exposed to it before this...

Thanks for your suggestion

Below is what my main menu bar looks like when the "Review" menu item is selected. I don't see the "Check Performance" menu item. It must be hidden or not allows to be seen through a system setting or the like.

 

Douglas997t_0-1692648044081.png

 

Douglas997t_0-1692654415967.png

 

 

I'm pretty sure those images are from desktop Excel not online Excel. You might think everything that online offers is also in desktop but you would be wrong. There isn't much but this feature is one feature that is in the online version and not yet in the desktop version.
Also, here is a link to the blog article specifically about this feature:
https://techcommunity.microsoft.com/t5/excel-blog/do-you-have-slow-workbooks-due-to-size-bloat-give-...
That all said, I checked 4 of my accounts (don't ask) and notice that I have it in 2, but the other 2 I am more like a guest in rather than having a full sub in so that might be why i didn't see it in those 2.

So you need to have or copy the file into your OneDrive/SharePoint and then go to it in a web browser and then select that file and view in browser.

@Douglas997t  Back to your original question the answer is that T12 has a space and therefore is not blank.  But more importantly let me mention a few very valuable 'tools' to help debug issues.

a) open the formula bar to see more of the code

b) highlight the portion of the formula you want to check and you will see a 'pop-up' for the value

c) if b) is hard to see or what not then hit F9 to have Excel EVALUATE that portion of the equation (I usually hit undo or the X on the formula bar to NOT save that evaluation.

Here is an image of me using a) and b)

 

mtarler_0-1692656035193.png

You can see the TRUE hovering over that section of formula I highlighted.

Then going down to the sub conditional IFs I found which one was TRUE and it wasn't the one you thought should be and then I highlighted $T$12 and saw is was " " instead of ""

 

BTW you also get a presumably unwanted FALSE so you need to fix the next to last line as shown here:

mtarler_0-1692656405693.png

actually there are a number of location you need to fix the FALSE condition for.  It is easy to use that pop up context bubble to help:

mtarler_1-1692656798201.png

In the above image you can see the pop up help context showing the IF statement is presently looking/indexed to the IF_TRUE portion so if the cursor is right before a close parentheses )  then there is no IF_FALSE output and hence excel will return with "FALSE".  So just move your cursor before each of those close ) and see if it indicates the TRUE condition and if so add the ,"" to make sure it returns blank instead of "FALSE" 

 

As for the 2nd problem I'm not sure I understand what you need/want but think you want the drop down list in A10 to change based on some condition.  In order to do that you will have to do the original suggestion by @mathetes  and use a helper column.  It is simple find a place you can use (e.g. column WA) and put the conditional formula in WA1 like 

=FILTER(A12:A1500, T12:T1500=B6, "")

That will spill the list of Seller #s

Then in the DATA VALIDATION menu use =WA1# for the list (# means the entire spill range)

Hope that helps

I always thought that Office 365 WAS the online version. Your is clearly different from mine. Is your also defined as Office 365? Or, does it carry an altogether different name?

Back to the problem at hand. I did as you suggested and see how it evaluates the end formula to see if it bringing forth what I have in mind. The VLOOKUP segments are working as intended, it is the CONCATENATE portion that doesn't connect. The syntax sure looks right. If you look right of the big dark blue circle with the 2 inside and the large bunch of merged cells where the paragraph text is being compiled and the content there being controlled by the dropdown choices made in row 8 between columns E:U, you will see (3) cells in column R rows 17, 19 & 20. In cell R17 is a broken out complete formula that contains, =IF(AND(U12>0,T12=""),CONCATENATE("P.S., ",VLOOKUP($U$12,W290:X299,2))).

In cell R19, is the first half of that formula, =IF(U12>0,CONCATENATE("P.S., ",VLOOKUP($U$12,W290:X299,2,FALSE)),"").

And lastly, in cell R20 is the 2nd portion of that formula, =IF(T12>0,CONCATENATE("P.S.s., ",VLOOKUP($T$12,W304:X313,2,FALSE)),"").

Cells R19 and R20 perform the CONCATENATION of the text string "P.S., " + the results of the VLOOKUP exactly as intended. When put together as in cell R17 something goes south. It seems it has something to do with the U12 reference but for the life of me I cannot see what that might be. This is the crux of my previous Question #1. It seems as though the IF/AND in cell R17 as written is problematic.

 

Once solved, I can resolve the other places where this is also happening. But starring at this for hours on end, moving elements around in different orders has come to nothing. Maybe with this explanation, you will have better luck?

Either way, thanks for your efforts!

As I mentioned above, I believe it is because of the value in T12.
In R17 you compare T12=""
In R20 you compare T12>0
In the case of T12=" " NOTICE the space (" " vs ""), then R17 is FALSE while R20 is TRUE
Simply click on T12 and hit DELETE then ENTER (BTW this is just to make the point / show you what I mean)

I don't understand the formulas on row 12 that use a LOOKUP(... , ... ,  1) which basically just returns the value it is looking up (in this case a space " ".  Regardless just knowing that you are getting a space should let you fix it by fixing on the input side or fixing the formula side.

1 best response

Accepted Solutions
best response confirmed by Douglas997t (Brass Contributor)
Solution

So as I mentioned above the line:
IF(AND(U12>0,T12>0),CONCATENATE(VLOOKUP($U$12,W290:X299,2)," ",VLOOKUP($T$12,W304:X313,2)),""))&CHAR(10)&CHAR(10),
is also TRUE because text values are > numbers so AND(U12>0,T12>0) resolves to be TRUE (again highlight that part and you will see TRUE in the 'bubble') and that line does NOT concatenate the "P.S., " before the VLOOKUP result and is why you weren't getting that P.S. part before. Now you are getting the correct line T12=" " AND the incorrect line T12>0.
You can fix it by adding the check or if you never have 0 just replacing it with ISNUMBER(T12).
As for the difference between "" and " " let's think about a balloon that you take out of the bag that isn't inflated and the balloon after you inflate it. I would call both a balloon but they certainly aren't the same.

mtarler_0-1692666615932.png

 


Another way to look at it is that don't you think

"     " is different than " "

because the former has a lot of spaces while the latter is only 1 space so why wouldn't you think "" is different than " "?

View solution in original post