Question about concatenate

[Guest]

Hello,

I have a table that has three fields across (ID#, Q01, Notes). Under the
ID# column I have several customer IDs: 1, 2, 3, etc... And under the Q01
column is the response to a customer question in which they could choose
multiple answers: A, B, C, D, etc...

The problem arises in that if a customer has multiple answers, they are on a
separate line. For example, if ID# 1 answered A, B, and C the first three
rows would be:

1 A
1 B
1 C

I would like to make one row per customer ID#. That way I have one row per
ID#, for example:

1 A,B,C

I believe I need to use an IIF function and Concatenate, but I'm not
certain. Any thoughts?

Thank you in advance,
Jack

 

[Bernie Deitrick]

Jack,

Let's say that your table headers are in row 1, and IDs are in column A, and Q01 responses are in
column B.

Insert a new column C, and in cell C2, enter the formula:

=IF(A2<>A1,B2&IF(A3=A2,","&B3,"")&IF(A4=A2,","&B4,"")&IF(A5=A2,","&B5,"")&IF(A6=A2,","&B6,""),"")

and copy down as far as you need to match your data table. (This formula was written with the
assumption that there can only be up to five answers - it can be expanded quite a bit if need be...)

Anyway, copy all of column C (the column with those formulas), paste special values to remove the
formulas, then sort based on that column and delete any rows with blanks. Then re-sort based on
column A, delete column B, and you're done.

HTH,
Bernie
MS Excel MVP

 

[geo@OZ]

As an addendum to the above, if you have the above formula in column D
rather than C, and have the following formula in each cell of C, this
will give you a list in columns C and D of a single reference to each
customer ID which is in the first row that contains a new customer ID
in columns A, with blank cells in C and D for the rows that contain the
duplicated values in columns A.

If you then apply a filter (filter on NONBLANKS) to the worksheet you
have a compiled listing on each customer ID and the "summed" responses.

Formula for column C is =IF(A2<>A1,A2,"")