Transfer values from row to a new column if other columns are consecutives duplicates

[Galuppo]

Hi guys!

I have a list of meetings with different investors. It includes when they happened and which employee was present. Like so:

Investor	Date	Employee	Location
A.Inc	March 21st	Andy	Restaurant
A.Inc	March 21st	Paul	Restaurant
A.Inc	March 21st	Matthew	Restaurant
B.Corp	April 2nd	Luke	Park
A.Corp	April 10th	Matthew	HQ

I would like to know if I could turn the first three rows into one single row if the investor name, dates and location are the same. Like this

Investor	Date	Employee	Employee	Employee	Location
A.Inc	March 21st	Andy	Paul	Matthew	Restaurant
B.Corp	April 2nd	Luke			Park
A.Inc	April 10th	Luke			HQ

There are upwards of 4 employees per event. Would it be hard to be done? Thank you so much!

[jeffreybrown]

Ciao Galuppo,

You could make pretty quick work of this with a pivot table!

[Galuppo]

Ciao Jeffrey!

Grazie for trying to help, I really appreciate it!

The only problem would be that employees are from the investors themselves, not our company (their company since I'm only a summer intern, haha).

Because of that, there are approximately 300 different employees. Would there be another way to do it?

Thank you again!

[abduljaleel.mca]

Try this macro.

[JeteMc]

Here is a formula based solution where a unique list of 'Investors' is placed in column F using the array entered formula*:

Please Login or Register  to view this content.

The dates and locations are populated with formulas similar to:

Please Login or Register  to view this content.

The employees are populated with the array entered formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

[Galuppo]

Try this macro.

You Excel Wizard, you! Thank you so much! That worked perfectly.

Here is a formula based solution where a unique list of 'Investors' is placed in column F using the array entered formula*:

Please Login or Register  to view this content.

The dates and locations are populated with formulas similar to:

Please Login or Register  to view this content.

The employees are populated with the array entered formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

Jete, Abdul's macro worked for me, so I didn't try your method! Needless to say, I don't even know how you guys could muster all of that knowledge for answering my question! One day hopefully I will be able to match 1/10th of your skills!

Overall guys, I've repped all of you and will mark this question as solved. I don't know what I would do without you!

Do you guys do it for fun? Haha!

Thanks again, Excelforum community!

[abduljaleel.mca]

Galuppo,
Thanks for the big feedback and rep .