How to group all data from 1 column into several columns

  • Thread starter Thread starter Kyle
  • Start date Start date
K

Kyle

I have 2 columns in my spreadsheet, Company Names and Event. The Event column
values are Event 1, Event 2, Event 3, Event 4 and the company names repeat
for each of the events that they attended. So sometimes I might have a
company listed 4 times with each Event corresponding to it in the next
column. How can I consolidate all of the company names so that there is 1 row
for each company and all events are on the same row but in different columns
marked with an x (the column labels will be Company name, Event 1, Event 2,
Event 3, Event 4).

This is what I have...

Company Name | Event |
------------------------------
Company A | Event 1 |
Company A | Event 2 |
Company A | Event 4 |
Company B | Event 2 |
Company B | Event 3 |
Company B | Event 4 |
Company C | Event 1 |
Company C | Event 4 |

This is what I want...

Company Name | Event 1 | Event 2 | Event 3 | Event 4 |
-------------------------------------------------------------------
Company A | x | x |
| x |
Company B | | x | x
| x |
Company C | x | |
| x |
 
In sheet2 Col A arrange the company names and row1 array the events..(make
sure they spell exactly same as the one in sheet1 where your data is placed)

In cell B2 enter the below formula and copy down/across..as required

=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*
(Sheet1!$B$1:$B$1000=B$1)),"x","")

If this post helps click Yes
 
Back
Top