Joining values in multiple columns into one formatted string of text & numbers

**shorty123** · 08-20-2011, 09:29 PM

Hello,

I have a table with 6 columns (A, B, C, D, E, F).
Column A is where I am plugging my formula to get my return value. Currently, the formula is as follows:
=CONCATENATE("T"&TEXT(B1,"000")&"R"&TEXT(C1,"00")&"W"&D1&"S"&TEXT(E1,"00")). This is my end result (return value) T037R09W4S05A

I'm having trouble with the end return value (A). Basically finishing off this formula so that it will look in column F, find certain specific numbers and if those numbers are there it would return the following letters at the end of this return value. The data in column F are random numbers/sequences from 1 to 36, each separated by a comma if there are multiple numbers.

If cell contains ALL the numbers from 1 to 36 then return value A
If cell contains the numbers 1, 2, 7 and 8 (in any order) then return value SE
If cell contains the numbers 3, 4, 5, 6 (in any order) then return value SW
If cell contains the numbers 9, 10, 15, 16 (in any order) then return value NE
If cell contains the numbers 11, 12, 13, 14 (in any order) then return value NW
If cell contains the numbers 1, 2, 7, 8, 9, 10, 15, 16 (in any order) then return value E
If cell contains the numbers 3, 4, 5, 6, 11, 12, 13, 14 (in any order) then return value W
If cell contains the numbers 9, 10, 11, 12, 13, 14, 15, 16 (in any order) then return value N
If cell contains the numbers 1, 2, 3, 4, 5, 6, 7, 8 (in any order) then return value S
If cell only contains ONE number by itself then return value L with that number as a two digit (ie L07 or L12).

I'm praying someone can please help? Having a hard time and fighting a deadline.

With kind regards,
thank you

**jeffreybrown** · 08-20-2011, 09:34 PM

Hello & Welcome to the Board,

Can you post a sample workbook so this can be tested?

**shorty123** · 08-20-2011, 09:49 PM

Hi Jeff,

Thank you for your response!

I'm attempting to attach a sample workbook, hope it works.

**JBeaucaire** · 08-20-2011, 10:03 PM

The "in any order" part makes this horribly complex. No way you can control the input to insure it's in the chronological order?

Also, your examples are fearfully short. Are there any other possible combinations of numbers? Perhaps 9,15,16,10,5? That has all the numbers for NE but also has an extra number... can that happen?

**jeffreybrown** · 08-20-2011, 10:15 PM

Here is my suggestion.

First, in column F I would control what the user can enter so this is done through data validation. The list is H1:H8

Second, I created a lookup table in H1:I7

Finally, applied a concatenate formula in column A with an if statement and the lookup formula and rolled into one.

Might need some tweaking but I think it is close to what you want if you control the input in column F

Can you work with this?

**JBeaucaire** · 08-21-2011, 03:16 AM

Jeff, I like it. But a couple of things.

IF(LEN(F5)=1 should be changed to IF(LEN(F5)<3 since individual numbers go up through 36, most of those are two digits.

In your data validation, you need to either:
a) Uncheck the [x] Show error alert message on the Error Alert tab to allow manual entry of those individual numbers, or
b) Add those individual numbers to the DV list.

If you choose B, then you can add the column I values and the whole formula gets a bit shorter, too.

="T"&TEXT($B7,"000")&"R"&TEXT($C7,"00")&"W"&$D7&"S"&TEXT($E7,"00")&VLOOKUP(F7,$H$1:$I$44,2,0)

**jeffreybrown** · 08-21-2011, 08:57 AM

Thank you Jerry with those great points.

**shorty123** · 08-21-2011, 01:06 PM

Thank you Jeff & Jerry.

I'm reattaching the sample file. To clarify, I have to create a pull down menu in column H (data validation) and also create a lookup table?
My apologies, the numbers are only from 1 to 16, in various combinations as per lookup table, and single digits ranges from 1 to 16, not 1 to 36 as I thought before.

I've added one more possible combo in the lookup table (the West quadrant, which is 3,4,5,6,11,12,13,14 = W).
I'm getting an error when I inputted single digit ie 11 in column H.

Thoughts?

thanks again!

**shorty123** · 08-21-2011, 01:21 PM

Thank you Jeff & Jerry.

I'm reattaching the sample file. To clarify, I have to create a pull down menu in column H (data validation) and also create a lookup table?
My apologies, the numbers are only from 1 to 16, in various combinations as per lookup table, and single digits ranges from 1 to 16, not 1 to 36 as I thought before.

I've added one more possible combo in the lookup table (the West quadrant, which is 3,4,5,6,11,12,13,14 = W).
I'm getting an error when I inputted single digit ie 11 in column H.

Thoughts?

thanks again!

**jeffreybrown** · 08-21-2011, 01:52 PM

Please try this....

**shorty123** · 08-21-2011, 02:10 PM

Thanks Jeff, I tweaked it a bit, and replaced 1 through 16, with 1,2,3,4 etc to 16.

What is the significance of the 12 in IF(LEN(F16)=12?

Works great!

**jeffreybrown** · 08-21-2011, 02:19 PM

If you notice in the drop down, one of the choices is 1-through-16 and the length is 12. No other choice has a length of 12 so therefore we know the user has selected 1-through-16.

**shorty123** · 08-21-2011, 02:49 PM

So if I replace the choice 1-through-16, with 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 do I replace the length of 12 to be the length of 31?

thanks

**jeffreybrown** · 08-21-2011, 03:05 PM

You could do that, but what I would do is on the lookup table replace 1-through-16 with 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 and the second column of the lookup table place an A then update the formula to:

="T"&TEXT($B7,"000")&"R"&TEXT($C7,"00")&"W"&$D7&"S"&TEXT($E7,"00")&IF(LEN(F7) < 3,"L"&TEXT(F7,"00"),VLOOKUP(F7,$H$1:$I$9,2,0))

BTW: the length of 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 is 38

**JBeaucaire** · 08-21-2011, 09:02 PM

Why aren't we just using a VLOOKUP table with all the options in it? No need to fiddle with extra evaluations that way.

**jeffreybrown** · 08-21-2011, 09:58 PM

Hi Jerry,

From post # 10, what do you see could be different?

**JBeaucaire** · 08-21-2011, 11:08 PM

The H:I table is incomplete. Complete it, then use the example formula I gave before for a straightforward VLOOKUP().

="T"&TEXT($B1,"000")&"R"&TEXT($C1,"00")&"W"&$D1&"S"&TEXT($E1,"00") & VLOOKUP(F1,H:I,2,0)

Joining values in multiple columns into one formatted string of text & numbers

LinkBack

Thread Tools

Rate This Thread

Display

Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Re: Joining values in multiple columns into one formatted string of text & numbers

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions