Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Zorak of Michigan: Jun 10, 2006

Requiem posted:

Hi again, folks. I'm stumped again trying to combine ISBLANK and CONCATENATE. I find I'm smart enough to regurgitate your help (from June, p. 25 of this thread) but not to troubleshoot when I'm altering the context. Sorry...

Here's the formula I currently have:
code:
=CONCATENATE(A2," ",L$1," ",L2," ",M$1," ",M2," ",N$1," ",N2," ",O$1," ",O2," ",P$1," ",P2," ",Q$1," ",Q2," <hr> ")
Here's how it looks from my data:

But I want unanswered questions hidden for each person. I WANT it to look like this:

Can anyone help? Sorry to be so dense...

EDIT: Happy to offer $20 via PayPal to whomever can help...

E:fb.

This is notably filthy and there may be a cleaner way to do it, but the brute force approach looks like this:

code:

=A2&"<br>"&IF(ISBLANK(L2),,(L$1&": "&L2&"<br>"))&IF(ISBLANK(M2),,(M$1&": "&M2&"<br>"))
&IF(ISBLANK(N2),,(N$1&": "&N2&"<br>"))&IF(ISBLANK(O2),,(O$1&": "&O2&"<br>"))
&IF(ISBLANK(P2),,(P$1&": "&P2&"<br>"))&IF(ISBLANK(Q2),,(Q$1&": "&Q2&"<br>"))&"<hr><br>"

It's frustrating that it repeats the basic cell, space, colon, space, cell structure over and over again, but it looks like you'd need to dip into VBA to construct something more sophisticated. In any case this looks to do what you want.

You were clearly on a workable track with ISBLANK. You just need to get the if functions in order.

Zorak of Michigan fucked around with this message at 02:48 on Aug 3, 2013

# ¿ Aug 3, 2013 02:45

Adbot: ADBOT LOVES YOU

# ¿ May 12, 2024 04:16

Zorak of Michigan: Jun 10, 2006

Thanks but there's no need to pay me for that load of mess. I'm just addicted to problem solving. Also RICHUNCLEPENNYBAGS got the explanation out first.

Unless you just don't give a crap, there's no reason to come away not understanding how this works. IF() has three sections- the logical test, the value for the cell if the test evaluates true, and the value if the test evaluates false. You already figured out that the right test for this is ISBLANK(), so you have to start with

code:

=IF(ISBLANK(L2),,)

If L2 is blank, we want to print nothing, so the value if true is nothing. No change required!

code:

=IF(ISBLANK(L2),,)

If L2 is something, then ISBLANK(L2) is false. In that case we want the cell to print the value of L$1, then a colon and a space, then the value of L2, then the HTML linebreak. If we use the & operator to shove all that together, it becomes

code:

L$1&": "&L2&"<br>"

so we stick that in the last bit of our IF function, wrapping it in parentheses because it makes me feel better to do so, and it becomes

code:

=IF(ISBLANK(L2),,(L$1&": "&L2&"<br>"))

When you put it all together it starts to look a little like alphabet soup, but when you break it down, it's simple coding. Since you want to combine it all into one cell, the other IF functions all get concatenated with the first, which I did with the & operator just to keep the function a little shorter.

# ¿ Aug 4, 2013 02:39

Zorak of Michigan: Jun 10, 2006

There are Powershell functions for both Excel and AD, so you could probably write the thing in Powershell. I just don't know what those functions are.

# ¿ Sep 25, 2013 20:59

Zorak of Michigan: Jun 10, 2006

Depending on how you use the data, it might also be worth looking at moving to a relational database. They can handle one-to-many mappings quite well.

# ¿ Nov 23, 2013 16:37

Zorak of Michigan: Jun 10, 2006

Once you do text to columns, it becomes the default for future pastes, which can be a nuisance. You turn it off by doing text to columns, delimited, and then deselecting all delimiters. Or that's how I do it, anyway. I had to do a lot of that this week, since I've been doing reports that required me to paste UNIX df output into Excel.

# ¿ Apr 3, 2014 22:25

Zorak of Michigan: Jun 10, 2006

I don't think I'm precisely visualizing what you're doing, but this feels like something you could do with a range-based MATCH function + INDEX instead. See https://www.deskbright.com/excel/index-match-multiple-criteria/ .

# ¿ Oct 11, 2016 22:07

Zorak of Michigan: Jun 10, 2006

If your data is either guaranteed to have one row per combination of order #, SKU #, and amount, or if you're OK with just summing the amounts when they match, this looks like a job for a pivot table.

# ¿ Aug 23, 2017 18:54

Zorak of Michigan: Jun 10, 2006

The only problem I see is the repetition of 2233445566 2017-07-01 00:00:00.000 101 SKU 1. I didn't notice it at first in my spreadsheet but it was caused by a space at the end of the second occurence of SKU 1. "SKU 1" is not the same as "SKU 1 " in pivot table logic.

I'm also trying to do this with OpenOffice's crappy pivot tables (I have the real thing on my work PC but I read the forums on my personal machine) so there may be a different issue I'm not seeing.

# ¿ Aug 23, 2017 19:23

Zorak of Michigan: Jun 10, 2006

Glad it worked and happy to help.

# ¿ Aug 23, 2017 21:27

Zorak of Michigan: Jun 10, 2006

This is one of those times I feel the need to ask for a sample of what you're doing. I'm having trouble visualizing it. It sounds like something that could be done with INDEX/MATCH and date functions but I'd need to see it to actually write the functions for you.

# ¿ Aug 24, 2017 19:31

Zorak of Michigan: Jun 10, 2006

I wouldn't use =MAXIFS, I'd use a pivot table, where the string part is my row header, and the value is computed with the max function.

# ¿ Jul 17, 2019 20:35

Zorak of Michigan: Jun 10, 2006

Newer Excel versions have the SWITCH function, which can help tidy up nested IF statements.

# ¿ Dec 1, 2019 17:23

Zorak of Michigan: Jun 10, 2006

This strikes me as one of those times when the kludgey solution is best. Sure, the lookup table lacks elegance, but anyone with journeyman Excel skill could see how it works. TheLastManStanding's solution is very clever but in two years, it's going to be a Vancian magic incantation.

# ¿ Jun 22, 2020 20:11

Zorak of Michigan: Jun 10, 2006

Agrikk posted:

How does one add functionality to a spreadsheet based on the contents of a cell?

For example, I'd like to have a "support enabled? (y/n)" prompt and typing a "Y" into a specific cell would cause additional cells and formulas and functionality to appear.

I know I could just have the additional functionality just be there, but I feel like being clever.

Depending on how complicated things are, you can just wrap functionality in =IF(A7<>"",your content here,""), or you could go outside my skillset and probably write macros to insert or unhide stuff when a user types the correct invocation, probably.

# ¿ Jun 24, 2020 17:21

Zorak of Michigan: Jun 10, 2006

You can't just look at the raw data and use auto filter?

# ¿ Feb 22, 2021 22:42

Zorak of Michigan: Jun 10, 2006

I think you're going to the up needing to add something that can do regular expressions to really zero in on that. You could approximate it with SEARCH for "F???????" and then using the result in SUBSTR, but you would get every eight letter word starting with F, and it wouldn't work if the cell had multiple IDs. Actually the multiple ID problem is beyond my ken anyway.

# ¿ Sep 30, 2022 21:47

Adbot: ADBOT LOVES YOU

# ¿ May 12, 2024 04:16

Zorak of Michigan: Jun 10, 2006

C-Euro posted:

I have a simple Excel sheet to track my spending vs my income, with each month's transactions set up in columns as
code:
Date      Transaction Name      Cost      Money Before      Money After
I want to use these data to start setting up an actual budget, and hoping to automate it to an extent by the fact that transactions use the same names month to month (Mortgage, Groceries, various utilities etc.) or that certain types of transactions have their cells highlighted. I'm picturing something like a VLOOKUP that can look across a user-defined range of multiple sheets in a workbook, and return the average of the associated values. Is that just a cleverly written VLOOKUP or is there another function for that?

If they were all in one sheet, this would be a good use case for a pivot table. Multiple sheets complicate it a lot. Are you wedded to that design?

# ¿ Feb 8, 2024 05:07

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread