Copy Multiple Column Headers to single cell if conditions met

[eadamquinn]

I have a large list of sales info that tabulates who bought which books. The book titles are listed as headers, with a "1" in the corresponding row to show which email purchased which book. I'd like to take the book titles and combine into one cell, separated by commas, like so: book1, book2, book3. This way, there will be one column summarizing all the books someone bought in one cell.

Here is small table to represent what I'm trying to do. Please keep in mind that I have over 30 columns and 16,000 rows so a simple IF and Concatenate function will not work. I'd like to have the values appear in the Book Summary column.

Thanks for all your help!

What I have currently:

Name	Book Summary	Book Title 1	Book Title 2	Book Title 3
[email protected]		1	1
[email protected]			1
[email protected]			1	1

What I Seek:

Name	Book Summary	Book Title 1	Book Title 2	Book Title 3
[email protected]	book1, book2	1	1
[email protected]	book2		1
[email protected]	book2, book3		1	1

[Tony Valko]

You can do this with a VBA function. Are you OK with that?

[GC Excel]

Hello,

Try this macro.
Assuming data starts in cell A1 :

Please Login or Register  to view this content.

[GC Excel]

Since you have 16000 lines, better use this version, otherwise seems to have an error with the Application.Index limit.

Please Login or Register  to view this content.

[eadamquinn]

I've never messed with VBA but am game to try.

[Tony Valko]

Try this...

Copy the code at the link below and paste it in a general module.

https://www.excelforum.com/showthread.php?p=3096647

Then, assuming this is your data...

Data Range

	A	B	C	D	E	F
1	Name	Summary	Book1	Book2	Book3	Book4
2	Name1	Book2, Book4		1		1
3	Name2	Book1, Book3	1		1
4	Name3	Book1	1
5	Name4	Book4				1

Enter this array formula** in B2 and copy down as needed:

=concatall(IF(C2:F2=1,C$1:F$1,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You'll have to save the file as a macro enabled file in the *.xlsm format.

[GC Excel]

I've never messed with VBA but am game to try.

Here's how to do it :
1. with you file opened, press ALT+F11 to open the VBA editor
2. The, ALT, I, M to insert a new module
3. Copy / Paste the code in this module
4. Press F5 to run the code

[eadamquinn]

Try this...

=concatall(IF(C2:F2=1,C$1:F$1,""),", ")

Thank you for referencing the UDF concatAll. I was able to get the formula to concat an entire row, leaving out the blanks. However, the conditional IF that you added returns a #Name error. Any insight as to why this may be happening?

Thanks again!

[Tony Valko]

Are you sure you put the code in the right place?

Here's a sample file that demonstrates this.

[eadamquinn]

Thanks Tony Valko, that function helped organize all the data.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.