Thread: Search and concatenate

Hi,

I am looking for help with some formulas as I am new to Excel 2007, I am wonderning if someone can help me.

I have 2 worksheets:

1. Product; and
2. Buyers.

1. In the Products worksheet, every column has data about the books' description like:

A | B | C | D | E
Serial | Desc. | Author | Pubd. | year

Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...

Columns B,C,D and E, have other details of the books.

2. In the Buyers worksheet, there are two columns;

A | B
Serial | Buyer Info
For Ex.
1 | Harry - Newcastle
1 | Peter - Sydney
1 | Anne - Sydney
2 | Bryon - Sydney
3 | Vickey - Perth
3 | hamish - Perth
4 | Amish - Brisbane
4 | Nicole - Darwin
4 | Steve - Los Angeles
5 | Ali - Melbourne
6 | Harry - Newcastle
6 | Peter - Sydney
6 | Anne - Sydney
6 | Brad - Adelaide
6 | Bryon - Sydney
7 | Vickey - Perth
8 | hamish - Perth
8 | Amish - Brisbane
9 | Nicole - Darwin
9 | Steve - Los Angeles
and so on..........

These are the details people who bought the particular book order by the serial no. of the book purchased.

OBJECTIVE

I want to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.

Example considering the above examples:

A | B | C | D | E | F
Serial | Desc. | Author | Pubd. | year | Buyer Info

1 | Accounting | Ramsay | ABC Pub. | 2008 | (Harry - Newcastle, Peter - Sydney, Anne - Sydney)

2 | Physics | Peter R. | ABC Pub. | 2007 | (Brad - Adelaide, Bryon - Sydney)

3 | Chemistry | Chopra D. | Nimbus | 2002 | (Vickey - Perth, hamish - Perth)

4 | Maths | Sharma R. | Willey | 2007 | (Amish - Brisbane, Nicole - Darwin, Steve - Los Angeles)

5 | Computers | Behl P. | PBI | 2010 | (Ali - Melbourne)

6 | Science | Aman D. | INC | 2008 | ( Harry - Newcastle, Peter - Sydney ,Anne - Sydney, Brad - Adelaide, Bryon - Sydney)

7 | History | Pande P. | ABC | 2010 | (Vickey - Perth)

8 | Social S. | Smith J. | Nimbus | 2009 | (hamish - Perth, Amish - Brisbane)

9 | Astronomy | Jain P. | PBI | 2009 | ( Nicole - Darwin, Steve - Los Angeles)


The above is what I am trying to do but keep failing...

Any help shall be highly appreciated.

Looking forward to all the sugestions.

Thanks a lot in advance for your time.

Praney

Added sample workbook

Hi Praney. Welcome to the forum.

This is doable, but will most likely involve adding some VBA to your workbook. You OK with us using macros to solve this? This isn't the programming forum, so I thought I should ask before spending any time on it.

If so, click GO ADVANCED and use the paperclip icon to post up your workbook. Make sure the sample workbook has a few sample "results" so we can clearly see your goal.

Also, is this a one-off need? Or do you need something that is operating all the time in real-time to keep these concatenated fields filled in?

No, we can solve it here, don't start another thread.