The simplest thing to do is just sort the table on column A, then copy that to the new table.

There is no built in way to concatenate a range of cells. It would be quite simple to write a macro or user defined function to do that if you really want to (or have a look athttps://newtonexcelbach.wordpress.com/2010/03/01/s...). But do you really want to? Why do you want all these words in a single cell, but displaying on separate lines?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Hi,

Yes, a Pivot Table. Very simple and easy.

Alternatively, it could be done with Conditional Formatting.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Ok, looks like I have to use Macros then.

Table A is a bid bat with each row being an engineering deliverable which includes deliverable name (as per mock example) and hours for that deliverable (which I was hoping to understand how to deal with based upon this response.

Table B is actually multiple tables and would be on another worksheet and is the CTR form so I was hoping based upon the bid tab I could then automatically extract the deliverable list. I was also hoping to automatically pull over the man hours per grade for each CTR as well.

Instead of concatenate I guess I could have excel insert a new row and have a single deliverable per cell, but I knew that definitely needed a macro which I had hoped to avoid

Stephen

For those following this thread in future, I did sort of find a result. I have had to pre add in rows for the second table, but using arrays as indicated here ended up helping

http://thinketg.com/how-to-return-multiple-match-v...

Note that is your table doesn't start in A1 you need to put brackets round the small function and subtract rownumber-1 from it

Stephen

Jkaen - What is wrong with sorting or using a pivot table?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS,

Maybe I am not understanding your suggestion correctly. To me sorting or pivot tables adjust how the data is displayed within table A. I don't want to alter the arrangement of this table, I want to selectively draw information from this table and display it in another on a different worksheet.

If I have misunderstood your suggestion please can you explain further

Stephen

Quote:

pivot tables adjust how the data is displayed within table A

Not so!

MY NEW PivotTable
Group	Item
1	Cat
	Dog
	Fish
2	Cabbage
	Tomato
3	Car
	Chair
 

Skip,

<sub>...done in less than two minutes.

Just traded in my OLD subtlety...
for a NUance!</sub>

Hmm, seems I do need to get familiar with pivot tables, as it does do what I requested after all.

As it happens the extra functionality I get from the array approach is of value to me, but i have been dismissing using pivot tables in my spreadsheets without really understanding them.

Thanks for the pointers

Stephen

If it were me, however, I'd use Conditional Formatting in Table A, with a simple Data > Validation control to apply the CF or not. So your ONE table can be used for both!

Is there a compelling reason for using a second table?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Pivot tables 100%!