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

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