How to group all data from 1 column into several columns (Redo)

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
|
 
D

Don Guillett

Assumes col F with cos, col G with events and col H with unique list of cos.
Another macro could make this unique list for you if you don't have it.

Sub thisway()
mc = 7 'col g
lr = Cells(Rows.Count, mc).End(xlUp).Row
On Error Resume Next
For Each c In Range(Cells(2, mc), _
Cells(lr, mc)) 'Range("g2:g12")
co = Application.Match(c, Rows(1), 0)
r = Application.Match(c.Offset(, -1), Columns(mc + 1), 0)
Cells(r, co) = "X"
Next c
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top