Question about Concatenate and IIF
 

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

Question about Concatenate and IIF
 

Is this a one-time-only modification to the workbook? If so, I have a
method to change the structure using a couple of helper columns. I'd
have to do more work to make reusable VBA code for the same purpose.

Question about Concatenate and IIF
 

Yes, this is a one-time modification.

Thank you,
Jack

"Mark Lincoln" wrote:

Is this a one-time-only modification to the workbook? If so, I have a
method to change the structure using a couple of helper columns. I'd
have to do more work to make reusable VBA code for the same purpose.

Question about Concatenate and IIF
 

Okay then, here's what I did.

I'm assuming your customer IDs are in Column A and start in Row 2, with
your responses in Column B and notes in Column C.

First of all, sort by Customer ID.

Then in D2 enter the following formula:

=IF(A2<A3,A2,"")

and drag it down to the end of your customer IDs. This will duplicate
the ID for each customer on its last row.

In E2, enter:

=B2

and in E3, enter:

=IF(A3=A2,E2&","&B3,B3)

and drag it down to the last customer ID row.

In cell F2, enter:

=IF(C2<"",C2,"")

In cell F3 enter:

=IF(A3=A2,CONCATENATE(IF(F2<"",F2,""),IF(AND(F2< "",C3<""),",",""),IF(C3<"",C3,"")),IF(C3<"",C3, ""))

and drag it down to your last row of data. (This big formula keeps
zeros from showing up in your Notes field if there are no notes.)

Select the cells in columns D through F from row 2 to the last row with
data, click Copy, then click on Edit|Paste Special and choose Values
under Paste in the Paste Special dialog.

Clear the data in columns A through C. Sort Columns D through F by
Column D. Now you have a list of customer IDs, each with all their
responses and any notes, plus data below that has already been
dupicated. Select the cells in those columns that don't have Customer
IDs and Clear. Then drag your remaining data over to columns A through
C. Done!

Question about Concatenate and IIF
 

I forgot to mention...

MAKE A BACKUP COPY OF THE WORKBOOK FIRST!

Just in case.... :-)

Question about Concatenate and IIF
 

PERFECT! Thank you very much!

"Mark Lincoln" wrote:

I forgot to mention...

MAKE A BACKUP COPY OF THE WORKBOOK FIRST!

Just in case.... :-)

Question about Concatenate and IIF
 

You're very welcome! Thanks for the feedback.