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
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
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
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
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
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
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Conditional Grouping Data from Table
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
Not so!
MY NEW PivotTable Group Item 1 Cat Dog Fish 2 Cabbage Tomato 3 Car Chair
Skip,
...done in less than two minutes.
for a NUance!
RE: Conditional Grouping Data from Table
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
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