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 " "?
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!
- 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!