INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

If I have misunderstood your suggestion please can you explain further

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.

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close