Forum Discussion
Problem with getting CONCATENATE text combined with an IF/THEN and nested VLOOKUP to display...
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.
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 " "?
Douglas997t Actually I do get what you are trying to do and I think it is me that is not explaining the problem clearly. The problem is that you have IF() and CONCATENATE() statements that overlap each other and it reaches that first IF(AND($B$12="Cat. 3",$C$12="N/A") which is FALSE and then everything else is skipped and eventually reaches the bottom )))))) and finds no FALSE condition so it returns FALSE. Here is sort of how I SEE the groupings of that formula. Notice how after that first IF with Cat. 3 all the rest is indented even more. I could take a GUESS at the places I THINK you want to end the groupings but afraid it may just make it look like it works for now until later you find another error because I don't know what you really want. So instead I hope this shows you how you NEED to critically inspect all those groupings and why the above members complained and warned about this sort of style, it get very hard very quick and very very very easy to make a mistake. That all said I suspect the location of my first "here" (line 46), should actually be "here")),""), to also end the CONCATENATE and that prior IF and then a latter "and here")) would get deleted.
=IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="SWAG"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="SWAG"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="Stair Step"),
CONCATENATE(VLOOKUP($G$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="Stair Step"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 2",$C$12="N/A"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($H$12,$W$107:$X$116,2)&CHAR(10)&CHAR(10),
VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 2",$C$12="B-Alert!"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$123:$X$132,2)&CHAR(10)&CHAR(10),
VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)
),
IF(AND($B$12="Cat. 3",$C$12="N/A"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($H$12,$W$156:$X$165,2)&CHAR(10)&CHAR(10),
IF(S12>0,
VLOOKUP($S$12,$W$304:$X$313,2),
""
)&CHAR(10)&CHAR(10),
VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
IF(U12>0,
CONCATENATE("P.S., ",VLOOKUP($U$12,$W$290:$X$299,2)),
"here"),
IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
CONCATENATE(
VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
IF(S12>0,VLOOKUP($S$12,$W$304:$X$313,2),""),
VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
IF(U12>0,
CONCATENATE("P.S., ",
VLOOKUP($U$12,$W$290:$X$299,2),""),
"and here")
),
"and here2")
),
"and here3")
)
)
)
)
)
)
If I were to re-design the mechanics of how I get this accomplished, might it makes sense to move each grouping off sheet or way off to the right for instance to where each grouping goes into its own merged cell grouping that becomes a named range. Then I could go back to B17:M43 and create a series of IF/AND statements using the values in B12 & C12 such as this ex. IF(AND(B12="Cat. 3",C12="N/A),"Named Range1")),""),
IF(AND(B12="Cat. 3",C12="B-Alert!),"Named Range2")),""))
And so on and so on...Would this be a potential and more stable and easier solution to troubleshoot?
Or, do you have another solution that might be better?
- Interesting...
Excel definitely has some eccentricities I don't understand and in the overall scheme of my Excel needs, guess I really don't need to learn. In the end, your fix has made a huge difference in speed and the many instances of hanging up when doing something radical like...selecting an empty cell.
I am just happy it is back to normal!
Thanks again!!! - It is very likely that it wasn't anything more than a space entered on row 1048576 or possible not even that but for some reason Excel set your sheet Used Range to include all those extra rows and I believe it saves blanks for all those cell references. Even if nothing is in those cells excel still has to keep reference info for each of them. So basically the 10MB that was reduced was a huge matrix of blanks. That said, I did a quick 'test' and failed completely so maybe I have no clue what I'm talking about. I created 3 files: Blank-2d, Blank-2sheet, Blank-2col
In each I used 2 formulas:
=SEQUENCE(1E6)
=SEQUENCE(,1E4)
in the Blank-2d I put them both on 1 sheet in cells A1 & B1 making a 2-d sheet
in the Blank-2sheet I put them on 2 different sheets but same cells (i.e. each sheet was a very long narrow array
in the Blank-2col I put them in cell A1 & B2 and changed the 2nd to be a column instead of a row output so a single sheet 2 columns wide.
I expected Blank-2d to be significantly larger but in fact all 3 were about 8MB according to the OS and oddly inside of Excel-Info it said they were each 16MB
One last tip, if you save the file as .xlsb (the b for binary) it will typically also be smaller and faster. - Sorry about the miscue. I thought I remembered you making that comment and our thread had become long enough that I just went straight from what I thought I remembered.
I am glad you saw what I was describing though. I did delete all rows below 1503 from A to the end of the far right column. In my case, I didn't have the same "delete" command as you mentioned but highlighted the whole batch, right clicked selected "Delete" and as you said, it did take some time to complete the task. This file (with recent changes) sat at 21MB and has come down to 11.7MB by taking this action. The size difference is huge! And thus far, it does seems to be quite a bit snappier.
I hate to pass up an opportunity to take advantage of a good lesson here. What could possibly have been in that cell range that was taking up so much space? All formulas and conditional formatting stopped at row 1500 with a note or two in 1501 & 1502. I would love to better understand how this range ended up some type of data, formatting, etc. so as to avoid it in the future! If/when you have a thought on this matter and the time to address it.
Either way, thanks again for your assistance on this! - so that actually wasn't me but I know/see what you're talking about. It is actually pressing ctrl-end and that takes you to cell VS1048576. My understanding and I may be completely wrong is that excel believes you have some data as far to the right as column VS (not necessarily in that last row) and as far down as row 1048576 (again not necessarily in column VS). That said it appears you don't have anything below row 1502 (A1502 has a NOTE) but you do have a formula in column VS (VS25:VS99). So Simply clicking on row 1503 (the number on the left to highlight the whole row) and clicking ctrl-down will highlight all rows from 1503 to the end and then I go to Delete and select Delete Sheet Rows. That will then warn you it will take a while and it did and sort of freeze up while it did that work but in the end that 1 thing took the file size from 21M to 11M.
1. I just remembered an earlier issue you had noticed that I didn't understand how to correct that should greatly decrease this workbook's size and I would like to enact the change.
It had to do with you having noticed a range that went all the way to the last bottom row of the 01 - v2.22 sheet. You had suggested that by using CTRL + "down arrow" I would arrive at the last cell in that range. You were right, the cursor did end up in that last cell. I don't know how to delete or clear or "un-rangeify" the undesired cells. Can you describe the process for me when you have a moment?Thank you again for your insights. You have been a tremendous help!
- Ahhhh. I thought the other fix replaced the use of the ISNUMBER() formula. I inserted it there and another place with the same issue and it worked great!
Thanks for another great fix! Douglas997t As mentioned above replace U12>0 with ISNUMBER(U12) as shown on lines 44 & 53 below :
=IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="SWAG"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="SWAG"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10), VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="Stair Step"), CONCATENATE(VLOOKUP($G$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="Stair Step"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10), VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 2",$C$12="N/A"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($H$12,$W$107:$X$116,2)&CHAR(10)&CHAR(10), VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 2",$C$12="B-Alert!"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($I$12,$W$123:$X$132,2)&CHAR(10)&CHAR(10), VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2) ), IF(AND($B$12="Cat. 3",$C$12="N/A"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($H$12,$W$156:$X$165,2)&CHAR(10)&CHAR(10), IF(S12>0, VLOOKUP($S$12,$W$304:$X$313,2), "" )&CHAR(10)&CHAR(10), VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10), IF(ISNUMBER(U12), CONCATENATE("P.S., ",VLOOKUP($U$12,$W$290:$X$299,2)), "")), IF(AND($B$12="Cat. 3",$C$12="B-Alert!"), CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10), VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10), IF(S12>0,VLOOKUP($S$12,$W$304:$X$313,2),""), VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10), VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10), IF(ISNUMBER(U12),CONCATENATE("P.S., ", VLOOKUP($U$12,$W$290:$X$299,2)), "") ), "") ) ) ) ) ) ) )To clarify my last question I have included (2) screen captures.
Screen Capture #1: eABCResponse-b_PS-01_Image1of2_2023-08-24.jpg
Notes...Screen Capture #1 reflects when U12 contains a value. Rather than describe what the boxes and arrows should pretty well illustrate, check out the image(s) instead and let me know if you have any additional clarification.
Screen Capture #2: eABCResponse-b_PS-02_Image2of2_2023-08-24.jpg
Notes...Screen Capture #2 reflects when U12 is empty
You will see that whether U12 contains a value or not the P.S. remains. As mentioned in my little text box notes in both images, the P.S. should disappear when U12 is empty. And both the P.S. and the short sentence should appear together when U12 contains a value. I can't figure out how to get it to do this. And, if it can't so be it, I will have to delete the P.S. when pasting to an email body text. It would be faster to use if it did what I have in mind. I am pretty sure it is an order of operation issue but a number of attempts at fixing have yielded no gain.
As usual, I appreciate your guidance on this!
- glad it is working and glad to here you will try to make improvements.
As for that final question I don't understand. Are you looking for the PS to not show but the comment/phrase afterwards to show or none of it? oh wait I bet you get the P.S. followed by a blank because U12>0 is always true (either it is a number 1,2,... or it is " ") . As i note/suggested somewhere in this thread you could use ISNUMBER(U12) instead of U12>0 The addition of the closing parens worked perfectly. I really appreciate your endless diligence and willingness to work through this with me.
I will keep working through a sample sheet using the other solution you provided with a migration in mind in the near future. At least this gets this functionality up and running for now.One aside here. Can you think of a way to get the "P.S., " to disappear when U12 is not selected? It has become a static feature and I can live with deleting it as needed but would love to find a way to have it disappear when no needed. I can see it is caught within a CONCATENATE and probably why is doesn't adhere to the If/Then " " ending command. Any ideas?
Enjoy the upcoming weekend!
DouglasDouglas997t I think a much better solution would be to have 1 Table (let's format as a table and call it Responses) and then you just use FILTER or lookup on that single table. I imagine something like:
=FILTER(Responses[text], (Responses[Category]=$B$12)*(Responses[Sub-Cat]=$C$12)*(Responses[SWAG]=$D$12)*(Responses[Choice]=$T$12),"")
now it might still need a couple tables or more logic in that filter but I think that could help.
as for the problem here I was mistaken slightly and I think you need only another ) after that first "here" to close the CONCATENATE. I got rid of the "here" notes and just left the formula I think you want in the attached.
