Transpose data in six columns into unique and multiple rows based on unique club name

[JimmyB11]

Hi everyone,

Thank you in advance for your help with this.

I require an Excel formula to transpose raw data across six columns into rows, with a unique row output based on each unique "Club" name.

I have attached an example of the raw data (Columns A to F) and the output example I require by row (from Column 'I'). Just FYI The raw data range will be over 1000 rows deep when actually compiled e.g. a range of A2:F1500.

A formula in Cell I2 that can be dragged down and across would be amazing to output singular rows of the data with each row separated by unique club name.


Thanks

Kind regards,

James

[JeteMc]

Hello JimmyB11 and Welcome to Excel Forum.
This proposal employs a helper column (H) which may be moved and/or hidden for aesthetic purposes.
Column H is populated using copy/paste and the Remove Duplicates feature (Data tab)
Also a row (7) of column headers has been added.
The formula used is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[JimmyB11]

Thank you ever so much JeteMc!

Wonderful work here.

Very much appreciated!

Cheers,

James

[soledad]

No helper column needed

Please Login or Register  to view this content.

Array formula, confirmed with Ctrl+SHift+Enter
Drag down and across to columns

[JeteMc]

You're Welcome, JimmyB11. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.