Hello everyone! I just registered at this site to post this question, so please go easy on me.
I'm trying to prepare a spreadsheet for my personal workload management. I've constructed a ridiculously lengthy CONCATENATE formula filled with IFs and IF(AND)s and other CONCATENATEs to make a nice single-cell list of items I need, along with the date(s) when I ordered them. I use logic functions to put "Item Names" together with "Order Dates" whenever "Order date" is populated and "Received Date" is not. This "summary" forumla will sit in a header cell above tables stored on multiple worksheets. A summary worksheet will refer to these summary cells so that I can print a single worksheet and know what is missing on each list, and also know how long I've been waiting.
The problem is that I want it to be presentable and easily understood, and I don't want to have to handle questions about that pesky dangling comma at the end of the list. The trick here is that I never know how long the list will be. I'm going to build the list to be 20 items long and assume I'm safe. My typical list is three to six items. Very rarely does this list break 10. In this example, I'll use a list of that's only five rows long, but I'll only be after four items.
I need Items One through Four, so those text strings go in cells A1-A4. Order dates go in cells B1-B4 and Received dates go in cells C1-C4. The formula that I've written works well logically, but I can't fix "the comma problem" because the formula also considers the contents of cells A5-C5. In this example, if I've received Item One and Item Three, my Missing Items summary formula produces either this string:
...which ends with comma space, or this string:Item Two (Ordered 11/1/10), Item Four (Ordered 11/1/10),
...which begins with comma space., Item Two (Ordered 11/1/10), Item Four (Ordered 11/1/10)
Does anybody know a quick and easy way to get rid of this silly comma? I've only had one idea: I could use the first output string (ending in comma space) and delete the last two characters. I would use two functions for this: The first function would count up the characters in the string and produce value X. The second function would duplicate the first X-minus-two characters of the string, and the comma should be gone. I forget the names of these functions but I have read about them so I know they're there.
Am I missing a more elegant solution here? I would like a formula that filters away the items that have Received Dates, and then concatenates Item Names and Order dates for the remainder of the list. I just don't know if this is possible without writing a macro, which is a whole different ball of wax obviously. Thoughts?
Bookmarks