Results 1 to 14 of 14
Thread: Translate X to Name (2003)

20080221, 14:53 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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 AC are fixed but DF 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 AC 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 nonmacro solution would be desirable but I'm not holding out much hope for that.
TIA
Fred

20080221, 15:13 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,303
 Thanks
 3
 Thanked 206 Times in 190 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 addin. 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>
arrayentered.
Adjust ranges as required.
HTHRegards,
Rory
Microsoft MVP  Excel

20080221, 16:26 #3
 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

20080222, 04:22 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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 DF), 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

20080222, 05:55 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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 addin route.
Fred

20080222, 12:13 #6
 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

20080222, 16:15 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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

20080222, 16:33 #8
 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

20080222, 18:04 #9
 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 22Feb08 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.

20080222, 19:28 #10
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Translate X to Name (2003)
Hi Steve,
I really appreciate the megaformula. We are already at 50 columns and it will only keep growing  at least 1015 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

20080222, 20:24 #11
 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 ninedigit 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>1Mar</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>1Mar</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>2Apr</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>22Mar</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>22Mar</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>29Feb</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>29Feb</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

20080222, 23:46 #12
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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 8character names (except for a few that have 10character 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 1215 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 256column limit <img src=/S/barf.gif border=0 alt=barf width=64 height=23> (and about 1015 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

20080223, 13:10 #13
 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

20080223, 22:12 #14
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 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 notsosimple 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