×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Conditional Grouping Data from Table

## Conditional Grouping Data from Table

(OP)
Hi all,

I am trying to group a bunch of text from column B of a table based upon an numerical value in column A of the table. I want the various qualifying text then all grouped (CONCATENATE I assume) in a second table

I have attached a mock example of what I am after with random text in to help explain better.

Does anybody know how to produce the result typed manually in B13?

In the real application table A could be hundreds of rows deep, so nested if statements definitely don't work. Also if possible I would like to keep this macro free if at all possible.

If it matters I see the number of rows in table B hitting maybe 50

Thanks
Stephen

### RE: Conditional Grouping Data from Table

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/

### RE: Conditional Grouping Data from Table

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!

### RE: Conditional Grouping Data from Table

(OP)
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

### RE: Conditional Grouping Data from Table

(OP)
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

### RE: Conditional Grouping Data from Table

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

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

### RE: Conditional Grouping Data from Table

(OP)
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.

Stephen

### RE: Conditional Grouping Data from Table

#### 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,

...done in less than two minutes.

Just traded in my OLD subtlety...
for a NUance!

### RE: Conditional Grouping Data from Table

(OP)
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

### RE: Conditional Grouping Data from Table

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!

### RE: Conditional Grouping Data from Table

Pivot tables 100%!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!