Link together different cells in specific order?

Hi all,

I have a list of terms to put together and what I have is a master list of 6 concatenate functions and I need to link them to all the different words in my list.
The first word in collumn A needs to be with the function in D1, the second word (A2) needs to be with D2, then A3 with D1, and A4 also with D1, while A5 with D6, etc.
It looks something like this:
for your info...I have put numbers in the adjacent cell corresponding to which of the 6 concatenate functions need to go into the cell in collumn C

So in reality all I need is a function that would rearrange my list of 6 functions from Collumn D into collumn C based on the numbers 1-6 I have in collumn B

Workbook.txt

(attached is an example to better see what im talking about. Disregard that the concatenate functions are not working...it doesnt matter right now.)



I hope that makes sense it is a bit hard to explain but let me know if you don't quite understand.

As I read over what I posted I cringed at how hard it is to explain. I thought that the linked attachment would help. It is the outcome. In collumn A are sample terms that I have. In collumn D are the 6 functions. Collumn B are the numbers 1-6 that correspond to which I want.... 1 meaning D1, 2 meaning D2, 3 meaning D3, and so on.

Basically once again I am trying to find a function that may be an if then. If the number 5 is in B1 then D5 needs to show up in C1. If the number 1 is in B26 then what is in D1 needs to show up in C26. If the number 3 is in B27 then what is in D3 needs to show up in C27...is that clearer?

Workbook.xls.zip

I hope that the attachment works this time...i dont know why i put it in .txt the first time.

Can't open your attachment.

Is it a 2007 xl file

VBA Noob

Well it is supposedly a default excel file format....hmmm its not 2007 either.

Lets try once more

Workbook 2.htm.zip

Lol i did because you couldnt see what I had posted last time....gees I was trying to find some common ground just so you could see what I was talking about not get the excel user all in a huff cause I used another format. Ill try the excel sheet again but if it doesn't work there isn't much else I can do then some other more universal format.

Workbook 2.xls.zip

I could open the htm file but not the mac excel file

Amended
So what do you mean by function ??

A B C D
Barbecue & Grill 2 Function 1
Fences & Gates 2 Function 2
Firewood Sales 3 Function 3
Garden Ponds 2 Function 4
Gardening 5 Function 5
Landscaping 3 Function 6
Lawn & Garden Equipment 2
Lawn & Garden Retailers 6
Nurseries & Greenhouses 6
Other Lawn & Garden Services 6
Playground Equipment 2





A B C D
Barbecue & Grill 2 Function 2 Function 1
Fences & Gates 2 Function 2 Function 2
Firewood Sales 3 Function 3 Function 3
Garden Ponds 2 Function 2 Function 4
Gardening 5 Function 5 Function 5
Landscaping 3 Function 3 Function 6
Lawn & Garden Equipment 2 Function 2
Lawn & Garden Retailers 6 Function 6
Nurseries & Greenhouses 6 Function 6
Other Lawn & Garden Services 6 Function 6
Playground Equipment 2 Function 2

VBA Noob

What i am looking for has to be easy to do for you guys I just cant explain it very well with words.

forget about the attachment if it doesnt work and ill try and explain it once more as simply as possible cause it really is something simple i think.

I want what is in cell D1 to show up in collumn E (lets just say) every time, and in the same row, that a number 1 shows up in collumn C. This is the same for the number 2, every time it shows up in collumn C, I want D2 to show up in the same row in collumn E.

PHP Code: 

  C             D                E  
2                              Func 1    
3                              Func 2
2                              Func 3
5                              Func 4
1                              Func 5
6                              Func 6
6
2
4



  C             D                   E  
2            Func 2              Func 1    
3            Func 3              Func 2
2            Func 2              Func 3
5            Func 5              Func 4
1            Func 1              Func 5
6            Func 6              Func 6
6            Func 6
2            Func 2
4            Func 4 


Something like that........?
i know this isnt what that wrap is for but im trying whatever I can..

thanks guys

lol

The exact information isnt what im interested in nor important but just the concept.... but here it is anyways

Workbook 2.xls.zip

Think I've figured out what you want

try this in C2 and drag down

=OFFSET($D$1,B2,0)

Amended

or

=OFFSET($D$1,B2,0)&" "&A2

VBA Noob

perfect...
now my question is, is there a way to conserve the concatenate function when sending it over using an offset? Becuase at the moment it comes over as a value of an offset function and no longer acts like a concatenate function which is what i need....I didnt put that in the spreadsheet I sent and I apologize but those 6 terms are actually concatenate functions that once placed according like you have already shown need to then "splice" if you will, with the A collumn.

The 6 are as follows:
(this is what they will be once in collumn C)

=CONCATENATE(A1,"for Hire Near Tampa Bay")
=CONCATENATE(A2,"for Sale Near Tampa Bay")
=CONCATENATE("Places that Offer"," ",A3,"Near Tampa Bay")
=CONCATENATE("Find Companies that Offer"," ",A4,"Near Tampa Bay")
=CONCATENATE(A5,"Sales & Services Near Tampa Bay")
=CONCATENATE("Find"," ",A6,"Near Tampa Bay")

You've lost me again.

Can you post a proper example with end results

VBA Noob

I changed it a bit to work better but this is what im looking for
=OFFSET($F$1,B1,0)
is what i used....but it turns the functions into just text and doesn't do what I want here.

The attached has the after how I want it with the actual concatenate function still intact and working.


Workbook 2.xls.zip

P.S. I moved the functions over just so it wouldnt pick up anything from 2 collumns over since that is what it is set to do.....

Try

=A1&" "&OFFSET($F$1,B1,0)

VBA Noob

Thank you but I just realized that the problem is still evident. If you notice that the second is "Places that Offer _______ Near Tampa Bay".
So it can't just be added to the beginning, it must be plugged into that specific place of the phrase which is how I have the concatenate set up.
Maybe this is more a job for a macro and not a function.....hmmmm

Please read forum rules and message to cross posters below and then add the link to the cross post

VBA Noob

See if this helps

=IF(B1=3,LEFT(OFFSET($F$1,B1,0),17)&" "&A1&MID(OFFSET($F$1,B1,0),18,999),IF(B1=6,LEFT(OFFSET($F$1,B1,0),4)&" "&A1&" "&MID(OFFSET($F$1,B1,0),6,999),A1&""&OFFSET($F$1,B1,0)))

or

=IF(B1=3,"Places that Offer "&A1&" Near Tampa Bay",IF(B1=6,"Find "&A1&" Near Tampa Bay",A1&""&OFFSET($F$1,B1,0)))

VBA Noob

great.

That is it. This also helped because now I understand IF functions a little more now. Just so you know I made some corrections because my 4th term was also like 3 and 6 but no worries I was able to figure it all out and make it work beautifully thanks to you.

Happy New Year.

Hi

See if the results on sheet2 get you there.

=grouper(A1,B1,$F$2:$F$7)

Please Login or Register  to view this content.

rylo

You just need to extend aprevious answer, putting the text in 2 columns, one for the text you want inserting before before and the second for the text after. Hopefully the attached helps. The VBA solution above is another way but depends if you want to go down the vba route

Regards

Dav