Question about concatenate

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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,"")
 
Back
Top