Numberic Indicies converted to Names

[skeety]

Okay brain trust...I KNOW there's got to be an easier way to do this, other than brute force.

So here's what I'm trying to do. Let's say I have a table where things are listed but with an indices indicating indent level

(NOTE: I know this example isn't really logically correct..but hopefully it conveys the problem)

1	Month
1.1	January
1.2	February
2	WeekDay
2.1	Monday
2.2	Tuesday
2.2.1	22nd

Now say I want to convert 1.1 to Month.January
and convert 2.2 to Weekday.Tuesday
and convert 2.2.1 to Weekday.Tuesday.22nd

anyone have an ideas?

I had a couple ideas...but they start getting tricky the lower down in levels I got.

Idea #1: I was thinking of using the "." delimiter to break out the numberical into various columns. meaning, instead of one column going down that has

1

1.1

2

2.1

I'd break it out like this:

1
1	1
2
2	1

Then I could just replace all 1's in the first column with "Month" And all all 2's in the first column with "Weekday". Then similarly in the 2nd column. The problem is...in the 2nd column, if it's a 1....is it a 1 from 1.1 or from 2.1. So now we have to lookup the 1st column to properly convert the 2nd column. This gets particularly messy when I have 5 or 6 levels down (2.1.4.3.13.2)

Idea #2: write a macro that looks at the number and counts the number of "." then add's 1. And in a new table, puts the text (i.e. "Tuesday") in that column. so if 2.1.4.3.13.2 has a value of "milli-seconds"...then "milli-seconds" would be put in the 6th column. If all of these are in order, I believe we'd end up with a table that has rows ike this:

Month

Weekday

Day

Hour

Second

Milli-Second

Then, once that table's complete, I just spit it out to text with my "." delimiter. So I'd ultimately end up with:

"Month.Weekday.Day.Hour.Second.Milli-Second"

but while I can figure out how to count the "." in the original number...not sure how to write a macro that would copy text into certain columns on a new page.

Or maybe there's even an easier way. Any help would be appreciated.

[dryphi]

What I'd suggest is using Text to Columns with a "." as the delimiter. This will break all the numbers into separate columns.
Then you can use multiple IF formulas to get what you want. You can either do these in separate columns and concatenate, or use one big formula with &"."& in between IF functions.

Another way is if you already have the "legend" (i.e. a range of all possible #.#.# values and the result) then you can just use VLOOKUP.

A third way is using the MID function to extract the first character, third, fifth, etc.

[skeety]

Yeah...my initial thought was VLOOKUP as well (see idea #1). But then realized I would end up with nested conditional VLOOKUPS. Almost 3-dimensional VLOOKUPs. and I don't think excel can handle that, can it?

[ben_hensel]

Three dimensional lookups are possible with the INDEX(CHOOSE, MATCH, MATCH) construction or similar. It requires a "cubic" data set up though.

But I think that might be the wrong direction.

Text-to-columns would work, and then you'd use the array construction of {INDEX(output, MATCH(input1&input2, range1&range2, 0))} to grab a one-and-only-one match... but you might need to daisy-chain a bunch of them together with concatenation, and wrapped in IFERROR(do, "") to accomplish what you need, I think.

Also don't forget,
Excel stores the date (day-month-year) as an integer, counting starting form zero on Jan 1st, 1900;
and time is stored as a float, with zero at midnight, noon at 0.5, and wrapping around to 0.99 at 11:59 PM or such; and time can include decimals of a second (eg, milliseconds).

So you can store all that data as a single value in the cell, and then display that however you want with formatting (I think custom format mmm.ddd.dd hh:mm:ss.000 is about what you want?). I dunno if that's what you want to do, but that can be an end-goal you work towards. I might be a little leery about that because I'd worry about floating-point arithmetic errors though, I'd probably do some testing.

[skeety]

that helps a little.

Can you give me an example of the approach your recommending, using my dates example?

Just as an FYI...I was using dates here as an example. This is actually a massive system requirements table. I didn't want to confuse the question with all that. hehe

[dryphi]

Seems like you have a limited set of values for each parameter, correct?
For instance Month can only be 12 different values, Weekday only 7, Hour 24, Second 60, and millisecond also 60. Maybe you can use six VLOOKUPS with the MID function and concatenate the result.
Can you post the Excel file?

[shg]

How about ...

	A	B	C	D	E
1	Level	WBS	Item
2	1	1	Month	Month	D3: =IF(A3 = 1, "", LOOKUP(1, 1/(A$1:A2 < A3), D$1:D2) & ".") & C3
3	2	1.1	January	Month.January
4	2	1.2	February	Month.February
5	1	2	WeekDay	WeekDay
6	2	2.1	Monday	WeekDay.Monday
7	2	2.2	Tuesday	WeekDay.Tuesday
8	3	2.2.1	22nd	WeekDay.Tuesday.22nd

You can use a formula to calculate level from WBS if need be.

[skeety]

Unfortunately, I can not. It's proprietary system requirements.

[skeety]

Did your formula get cut-off?

D3: =IF(A3 = 1, "", LOOKUP(1, 1/(A$1:A2 < A3), D$1:D2) & ".") & C3

[shg]

Did your formula get cut-off?

Nope, that's all there is.

[skeety]

I get that formula to give me D2...but is the formula similar for D3?

I'm not getting it to work past level 1...

[shg]

Paste the formula in the formula bar sans curly braces, press and hold the Ctrl and Shift keys, then press Enter.

Then copy down.

[shg]

Oops -- never mind, it doesn't need to be array-entered. It works exactly as shown.

[skeety]

shg,

Think I just thought of the easiest way...but it's dependent upon them being IN ORDER:

=IF(A3 > A2, CONCATENATE(C2,".", C3), C3)

[skeety]

D'oh...wait...this won't handle situations where the levels are equal. ie.

Level
1
2
2 <- Will break here...

hmmm...getting closer though.

[shg]

... deleted ...

[skeety]

shg,

Yes. hehe...I know how to copy the formula out...but it's fills out the level 1's fine...but all the rest end up "0"

[shg]

Did you look at the attached workbook?

[dryphi]

How about =IF(LEN(A3)=3,C2&"."&C3,C3)
You could do multiple nested IF statements for the condition where the length of the string in colum A is 1, 3, 5, 7, 9 and 11 characters long.

Also, just FYI using the & symbol is easier than typing the word "concatenate"

[skeety]

I ended up getting shg's formula working. When I converted it to the cell #'s in my table, I was off by one.

HUGE help! THanks.

[skeety]

AAAAkkk.

ALMOST working. It's messing up when looking up nested levels.

it works for 1.2.1

but later, when it finds 3.2.1, it's pulling in the x.2.x value from 1.2.1

meaning

Section1
--Subsection1_FOO
----extraSubsection1
Section2
--Subsection1_WOOT
----extraSubsection1
Section3
--Subsection1_BOOP
----extraSubsection1


Instead of getting Section3.Subsection1_BOOP.extraSubsection1 for 3.1.1
I'm getting Section3.Subsection1_FOO.extraSubsection1

[Phuocam]

Or try this:

=LOOKUP(10^10,$A$2:A2,$G$2:G2)&IF(COUNT(B2:F2),"."&LOOKUP(10^10,$B$2:B2,$G$2:G2),"")
&IF(COUNT(C2:F2),"."&LOOKUP(10^10,$C$2:C2,$G$2:G2),"")&IF(COUNT(D2:F2),"."&LOOKUP(10^10,$D$2:D2,$G$2:G2),"")
&IF(COUNT(E2:F2),"."&LOOKUP(10^10,$E$2:E2,$G$2:G2),"")&IF(COUNT(F2),"."&G2,"")

Please see the attached.

[dryphi]

Mostly works except row 4 says "Month.January.February"

EDIT: That was actually a data entry mistake in column 2. There wouldn't be a case where a column is skipped afaik.

[shg]

Sorry, there was a mistake in my formula, corrected below.

	A	B	C	D	E
1	Level	WBS	Item
2	1	1	Section1	Section1	D3: =IFERROR(LOOKUP(2, 1/(A$1:A2 < A3), D$1:D2) & ".", "") & C3
3	2	1.1	Subsection1_FOO	Section1.Subsection1_FOO
4	3	1.1.1	extraSubsection1	Section1.Subsection1_FOO.extraSubsection1
5	1	2	Section2	Section2
6	2	2.1	Subsection2_WOOT	Section2.Subsection2_WOOT
7	3	2.1.1	extraSubsection1	Section2.Subsection2_WOOT.extraSubsection1
8	1	3	Section3	Section3
9	2	3.1	Subsection1_BOOP	Section3.Subsection1_BOOP
10	3	3.1.1	extraSubsection1	Section3.Subsection1_BOOP.extraSubsection1

[skeety]

BOOM goes the Dynomite!!

Thank you SOOOOO much shg, and everyone else!

Saved me tons of time...as there were over 550 of these....

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.