Joining values in multiple columns into one formatted string of text & numbers
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
Hello & Welcome to the Board,
Can you post a sample workbook so this can be tested?
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Hi Jeff,
Thank you for your response!
I'm attempting to attach a sample workbook, hope it works.
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?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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?
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
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)
Last edited by JBeaucaire; 08-21-2011 at 03:21 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you Jerry with those great points.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
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!
Last edited by shorty123; 08-21-2011 at 01:18 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!
Please try this....
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
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!
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.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
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
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
Last edited by jeffreybrown; 08-21-2011 at 03:08 PM.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Why aren't we just using a VLOOKUP table with all the options in it? No need to fiddle with extra evaluations that way.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks