Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Translate X to Name (2003)

    Hi All,

    I have a workbook like the attached sanitized version. It has numerous tasks and more get added over time (yes, we do delete some tasks also). The sheet "Input" is what it currently looks like. Cols A-C are fixed but D-F are not. In fact, we keep adding more F(number) cols. Each task, in addition to a due date or owner (either could be blank), also needs 0 or more (usually 1 or more) items from the F(number) cols as indicated by an x in the corresponding column. The problem is that the number of F(number) columns keeps growing.

    What I'd like to do is create a sheet that looks like "Output" while maintaining the "Input" sheet to serve as a data base for the "Output" sheet. In "Output", cols A-C are maintained for all rows. But rather than an ever expanding number of columns, I'd like one column that has the concatenated headings from row 1 of the "Input" sheet where that heading has an x for that task.

    Hopefully this is clear.

    If possible, a non-macro solution would be desirable but I'm not holding out much hope for that.

    TIA

    Fred
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Translate X to Name (2003)

    I think the only way you could do that without VBA would be using the MCONCAT function from the morefunc.xll add-in. Otherwise, if you use:
    <pre>Function MyConcat(varData, strSeparator) As String
    Dim varitem, strOut As String
    For Each varitem In varData
    If Len(varitem) > 0 Then strOut = strOut & strSeparator & varitem
    Next varitem
    MyConcat = Mid$(strOut, 2)
    End Function
    </pre>


    then your cell formula can be:
    <code>=myconcat(IF(F19:K19="x",$F$17:$K$17,""),"," )</code>

    array-entered.
    Adjust ranges as required.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    You could use a formula in D2 like:

    =MID(IF(ISBLANK(Input!D2),"",", "&Input!D$1)&IF(ISBLANK(Input!E2),"",", "&Input!E$1)&IF(ISBLANK(Input!F2),"",", "&Input!F$1),3,25)

    and copy it down the column.

    Add more if parts within the MID. The mid just gets rid of the initial comma space (", "). The 25 is arbitrary and is big enough to contain all the text that is concatenated together

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    Hi Steve,

    Thanks for the solution. However, as I add more columns as I mentioned (the real workbook is now up to about 50 columns corresponding to what was in D-F), this seems like it will become unwieldy. I know you're not nesting IF statements here. But for each col with a name, the formula adds 38 characters if I counted correctly. Isn't there a limit on the number of characters I can have in a formula? The current condition would give a formula with >1900 characters.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    Thanks Rory. I didn't think one could do this in Excel without resorting to VBA, which is preferable to going the add-in route.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    The max length of a formula is 1024 chars. It can also be done with several intermeidate formulas which can then be grouped together.

    But either the megaformula or even the several intermediate formulas do become unwieldy. But that unwieldiness must be balance against the Macro restriction you posed. If it is requirement (absolutely no VBA) then you can work around the unwieldiness. If it is only a wish, I would go with a macro/function

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    Hi Steve,

    A macro solution was what I expected but was hoping for a formula. So macro away. Rory came up with one that works so don't knock yourself out.

    By the way, I was playing with some formula approaches. For example, I created a string of repeating characters of the needed length based on the number of x's and then tried to do a REPLACE of the F1, F2... into that string. Problem with that approach is that REPLACE does not do what I want in an Array formula. I was also toying with creating a string that was the concatenation of all the names (ie, "F1F2F3") outside of a formula and then substitute a null for a name that did NOT have an x. Nothing seems to be working as I want.

    Fred

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    The problem is with concantenation formula (a peeve of mine). It is analagous to SUM in all ways but the most imporant one:

    =A1+A2+A3 =Sum(A1,A2,A3) = Sum(A1:A3)

    But:
    =A1&A2&A3 =CONCATENATE(A1,A2,A3) <> CONCATENATE(A1:A3)

    The only way to concatenate with a formula is to list the items separately

    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 22-Feb-08 12:04. Added PS, PPS)</P>If you only have 5 columns you can do it with a formula:
    ="F"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUMPROD UCT((Input!$D2:$H2="x")*{1000000000000,2000000000, 3000000,4000,5}),"#,##0"),",",", F"),", F000",""),"0","")

    It fails starting at 6 due to rounding errors/limitations on significant figures.

    Steve
    This one will go up to 9 and then has problems due to being 2 digits starting with 10...:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT( (Input!$D2:$L2="x")*{100000000,20000000,3000000,40 0000,50000,6000,700,80,9}),"F0xF0xF0xF0xF0xF0xF0xF 0xF0"),"F0x",""),"x", ", "),", F0","")

    PPS
    If you add in Output!E1:M1 the numbers (respectively)
    100000000,20000000,3000000,400000,50000,6000,700,8 0,9

    You can use the formula for 50 concatenations:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT( (Input!$D2:$L2="x")*Output!E1:M1),"F0xF0xF0xF0xF0x F0xF0xF0xF0")&IF(M2="x","xF10x","xF0x")&SUBSTITUTE (SUBSTITUTE(TEXT(SUMPRODUCT((Input!$N2:$V2="x")*Ou tput!E1:M1),"aF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0") ,"aF", "F1"),"F10","")&IF(W2="x","xF20x","xF0x")&SUBSTITU TE(SUBSTITUTE(TEXT(SUMPRODUCT((Input!$X2:$AF2="x") *Output!E1:M1),"aF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0xaF 0"),"aF", "F2"),"F20","")&IF(AG2="x","xF30x","xF0x")&SUBSTIT UTE(SUBSTITUTE(TEXT(SUMPRODUCT((Input!$AH2:$AP2="x ")*Output!E1:M1),"aF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0x aF0"),"aF", "F3"),"F30","")&IF(AQ2="x","xF40x","xF0x")&SUBSTIT UTE(SUBSTITUTE(TEXT(SUMPRODUCT((Input!$AR2:$AZ2="x ")*Output!E1:M1),"aF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0x aF0"),"aF", "F4"),"F40","")&IF(BA2="x","xF50x","xF0x"),"F0x"," "),"xF",", F"),"x","")

    It could be shortened by putting the string:
    aF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0xaF0

    in a cell and referencing that.

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    Hi Steve,

    I really appreciate the mega-formula. We are already at 50 columns and it will only keep growing - at least 10-15 columns per year over the next few years. The problem is that this is a VERY sparse matrix. A few x's for almost every row - probably not more than 2 or 3 but 1 or 2 rows might have x's in almost every column.

    As far as your other post on sum Not Equal to Concatenate, I realized that what I need is a CONCATENATEIF formula, like SUMIF. I tried "adding" text columns but it didn't work.

    Fred

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    The concantenate if is what I am doing with the sumproduct by placing each group of nine entries into the "matrix" based on a nine-digit number.

    Instead of a "VERY sparse matrix" I would recommend a redesign
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center valign=bottom>1</td><td valign=bottom>Task</td><td valign=bottom>Due Date</td><td valign=bottom>Owner</td><td valign=bottom>"F"</td><td align=center valign=bottom>2</td><td valign=bottom>Task1</td><td align=right valign=bottom>1-Mar</td><td valign=bottom>OW</td><td valign=bottom>F1</td><td align=center valign=bottom>3</td><td valign=bottom>Task1</td><td align=right valign=bottom>1-Mar</td><td valign=bottom>OW</td><td valign=bottom>F2</td><td align=center valign=bottom>4</td><td valign=bottom>Task2</td><td align=right valign=bottom>2-Apr</td><td align=right valign=bottom>*</td><td valign=bottom>F2</td><td align=center valign=bottom>5</td><td valign=bottom>Task3</td><td align=right valign=bottom>22-Mar</td><td valign=bottom>GB</td><td valign=bottom>F1</td><td align=center valign=bottom>6</td><td valign=bottom>Task3</td><td align=right valign=bottom>22-Mar</td><td valign=bottom>GB</td><td valign=bottom>F3</td><td align=center valign=bottom>7</td><td valign=bottom>Task4</td><td align=right valign=bottom>29-Feb</td><td align=right valign=bottom>*</td><td valign=bottom>F1</td><td align=center valign=bottom>8</td><td valign=bottom>Task4</td><td align=right valign=bottom>29-Feb</td><td align=right valign=bottom>*</td><td valign=bottom>F2</td></table>

    There is a lot less "blank space", it is more "compact" (approaching the "output" desired), and can be used with pivot tables and filtering so much easier. The wide columnar design can be created (if desired) directly from this setup using a pivottable.

    Steve

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    Hi Steve,

    Let me mention more about the project.

    First, the attachment I created was a sanitized version of what I use. Hopefully I didn't simplify it too much to make the suggested solutions not applicable.

    Second, the F1, F2,.. are actually routers with 8-character names (except for a few that have 10-character names) representing routers in our network

    The rows are projects that require changes to be made to 1 or more routers - as mentioned, most projects affect only a few routers. There are some "cleanup" projects requiring changes to almost every router.

    When I inherited this spreadsheet, the design of a column for each router was already there with the idea of using an x in a cell if the project (row) required a change to the router (column). At that time, we had about 20 columns (routers) that represented about 12-15 months of network growth. In the 6 months since, we've added about 30 columns (routers) and the growth will continue.

    So I'm looking ahead when I get to the 256-column limit <img src=/S/barf.gif border=0 alt=barf width=64 height=23> (and about 10-15 columns are already used for "fixed" things like project description, milestone dates, etc.). I doubt that we'll be switching to 2007 soon. Seriously, though, it's hard to read as is because you have to read up the column to make sure you know which router the project applies to. Naturally our columns are pretty thin and it has happened that people cross to the adjacent column and apply the project changes to the wrong router (I've tried alternating stripes but that looks very ugly).

    So my choices are to:
    - maintain the current "DB" design (which people are used to although the printout, for those who print out the sheet, requires taping)

    - maintain the current "DB" design going forward for my record keeping but create an output report that's more readable (list the "fixed" items followed by the name of the routers). That's what I've been trying to do in this thread and my preference for the short run given the limited time I have to work on this.

    - go with a design like you've suggested for both input and output; this would be unacceptable since the output now has multiple rows per project.

    - go with a design like you've suggested for my record keeping (ie, more of a traditional DB design) but create an output report that's more readable - like the 2nd choice.

    - migrate the existing "DB" design to look something like the desired output and use that from then on; I know I should separate input and output. That is, maybe do once what you or Rory suggested, and then maintain the input and output in that form.

    I quickly tried a pivot table with a DB as you suggested. Not sure that I'm getting what I want. That is, 1 row per project with a listing of names (routers) affected by that project. I can get a count of the #routers but I'd have to double click the count to get a display on another sheet of the routers (ie, those records or the original DB pertaining to the number I clicked on). I'm also getting "Total" rows in my pivot table even tho I uncheck that in my options. Maybe I'm missing something.

    Thanks again. Any other ideas are appreciated.

    Fred

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translate X to Name (2003)

    If the items are not Fx1 to Fx9 then my formula will not work

    If you do use the simpler "input design" and still require the "short form output" similar to what you have you would have to use code. You could use a function rather than a subroutine to make it more automatic.

    The UDFs with a VlookupAll could do this directly from the data which I created in <post:=395,235>post 395,235</post:>.

    Personally I find the multiline input easier to manage than the ones with so many columns.

    Steve

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Translate X to Name (2003)

    H Steve,

    I actually had your VLOOKUPALL code from a few years ago. For now, I think I have to live with the not-so-simple input - one row per project regardless of how many routers are affected. But I'll see what I can do to get the list of routers actually touched into something less than a bunch of x'd columns - maybe using Rory's code. Using the simple input of 1 row per project per router does actually have a small advantage but too small. It's downside is the few cases where a project will touch (almost) all routers; I know people don't want to be looking at 40 or more rows.

    Thanks again.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •