Excel support needed

[buah]

I'd appreciate it a lot if someone could help me.

I have two columns (A and B) with values and I want to get a column C with expected results as following:

A | B                   | C

1 | English          | English, French, Spanish
1 | French           | (empty)
1 | French           | (empty)
1 | Spanish         | (empty)
2 | English          | English
3 | Spanish          | Spanish, Russian
3 | Russian          | (empty)

Please note that, even if there are two "French" values in column B for a A column values=1, I need only one results - no duplicates.

I can get result for C3,4,5=(empty) and for C2=English, French, French, Spanish, but don't know how to get rid of duplicate "French, " at least afterward.

Thank you in advance.

[rick.ca]

It appears you have a list where each instance of an available language in each movie is included. In other words, for each movie there's one line for each language. What you want is one line per movie, each including one field which is a list of all available languages. If I've misunderstood, hopefully you can still make use of the technique. There are more elegant ways to do this, but the following is simple and it works.

It's a bit difficult to describe, so I've attached a worksheet to illustrate. Briefly...

A1: Movie #	B1: Language	C1: Previous + Current	D1: Result
data	data	C2: =IF(A2=A1,C1&", "&B2,B2)	D2: =IF(A2=A3,"",C2)

The sheet must be sorted by Movie #, then Language. The formulae in columns C & D are copied relatively to all following cells. They don't make any sense at row 2, but will produce the desired result for the last row of each movie—wherever that occurs. Just filter out the blank results in column D to get one row per movie. Cut & paste this into another sheet to convert formulae to values and eliminate the hidden rows. Delete any unnecessary columns and save this sheet separately so it can be imported to PVD.

[attachment deleted by admin]

[buah]

Rick,

Thank you for your help. One thing I'm not sure I was understood. Instead of "English, French, French, Spanish" expected result should be "English, French, Spanish" - concatenating only unique records from column B for duplicate values in column A.

Btw, your tip is much more simple than mine for getting the same result!

[rick.ca]

Sorry—I forgot about the duplication problem. I would just delete all duplicates as a preliminary step. Concatenate A & B and sort the result (if it isn't already). Add a column to test that for duplicates (i.e., cell = cell above). Turn that into values, sort, then delete the rows where the value is "true." Then continue, as described before.

[buah]

I would just delete all duplicates as a preliminary step.

Of course! :slap:
And, there is such a nice big icon in Excel 2007's toolbar named "Remove duplicate rows"...
It all worked like a charm, Rick. Importing done, too, with the option "update" chosen as a method. Thus, only ID and Subtitle values were overwritten in PVD, while Title and Year fields were intact (for others: don't use "overwrite" method, it'll overwrite Title and Year with empties if you have a table like I had above).

Thanks a lot!