Help with concatenate

[Sudarshan_1026]

Hi All,

Need you help in concatenate function

i need to concatenate values of a row based on the column header.i cannot use a simple concatenate as the data will be pasted by different individuals and column numbers might change. i am not getting through any logic to do it using the match function as well. the data will be pasted in random columns every time someone inputs it.

Appreciate your help on this.

Sudi

 

[TheCman81]

What are the column header names?
How many concat will there be?
Also can you post some sample data to work with?

Thanks

Colin

 

[Sudarshan_1026]

What are the column header names?
How many concat will there be?
Also can you post some sample data to work with?

Thanks

Colin

There will be three concat.. date, home, and away are the headers. it will have another 10 headers in the page

Sudi

 

[TheCman81]

Assuming your headers are from column A-J and are spelt "Date", "Home", "Away" this should do the trick

=CONCATENATE(TEXT(INDEX(A2:J2,MATCH("Date",$A$1:$J$1,0)),"dd/mm/yyyy")," - ",INDEX(A2:J2,MATCH("Home",$A$1:$J$1,0))," - ",INDEX(A2:J2,MATCH("Away",$A$1:$J$1,0)))

 

[Sudarshan_1026]

Assuming your headers are from column A-J and are spelt "Date", "Home", "Away" this should do the trick

=CONCATENATE(TEXT(INDEX(A2:J2,MATCH("Date",$A$1:$J$1,0)),"dd/mm/yyyy")," - ",INDEX(A2:J2,MATCH("Home",$A$1:$J$1,0))," - ",INDEX(A2:J2,MATCH("Away",$A$1:$J$1,0)))

Excellent, it worked perfect for me.. thanks a ton, made my day.Index was the trick which i was missing..

 

[TheCman81]

You are very welcome, thanks for providing feedback

 

[Sudarshan_1026]

You are very welcome, thanks for providing feedback

Would you be able to help me with formula through which i can look for a specific word in the cell and return a value of my choice..

Ex A1 cell has Operator Module - branding - 10% .. if teh cell A1 contains branding it should return value 1

Sudarshan

 

[TheCman81]

Will branding be the only criteria?

 

[Sudarshan_1026]

Will branding be the only criteria?

I somehow managed to get it going using ISNUMBER(FIND(

i am stuck at a different problem.

I am using a IF statement and doing a vlookup to pull the value from another sheet and further trying to use the OR function to see if the lookup value is equal to any one of the OR values its throwing an error.

Can you help me on this..

Sudi