Conditional Grouping Data from Table

[Jkaen]

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

 

[IDS]

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 at

https://newtonexcelbach.wordpress.com/2010/03/01/stringing-more-than-two-words-together/).

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/

 

[SkipVought]

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!}

 

[Jkaen]

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

 

[Jkaen]

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-values-in-excel-using-index-match-or-vlookup/

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

 

[IDS]

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

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Jkaen]

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

 

[SkipVought]

pivot tables adjust how the data is displayed within table A

Not so!


[pre][highlight #FCE94F]MY NEW PivotTable[/highlight]
Group Item
1 Cat
Dog
Fish
2 Cabbage
Tomato
3 Car
Chair
[/pre]

Skip,

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

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

 

[Jkaen]

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

 

[SkipVought]

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!}

 

[EngineeringEric]

Pivot tables 100%!