Transpose column data to individual rows per unique name

J

JimmyB11

New Member
Joined
Apr 29, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hi everyone,

Thank you in advance for your help with this.

I require an Excel formula to transpose raw data from the six columns (A to F) into rows, with a unique singular row based on each unique "Club" name. If the club name changes then a new singular row is formed.

I have attached an example of the raw data (Columns A to F) and the output example I require by row (from Column 'I').

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



Transpose Data Column into Single Row based on unq value.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1DateDayClubVenueTimeEventsTRANSPOSED OUTPUT REQUIRED ON A SINGLE ROW PER UNIQUE CLUB NAME (COLUMN 'C') -DRAG DOWN AND ACROSS FORMULA
25/12/2021SunCLUB 1VENUE112:00pm115/12/2021SunCLUB 1VENUE112:00pm11
37/02/2022MonCLUB 2VENUE212:00pm117/02/2022MonCLUB 2VENUE212:00pm1126/08/2021ThuCLUB 2VENUE212:00pm8
426/08/2021ThuCLUB 2VENUE212:00pm816/10/2021SatCLUB 3VENUE312:00pm831/10/2021SunCLUB 3VENUE312:00pm816/12/2021ThuCLUB 3VENUE312:00pm8
516/10/2021SatCLUB 3VENUE312:00pm817/03/2022ThuCLUB 4VENUE412:00pm86/04/2022WedCLUB 4VENUE412:00pm87/07/2022ThuCLUB 4VENUE412:00pm822/08/2021SunCLUB 4VENUE411:30am95/09/2021SunCLUB 4VENUE412:00pm1025/10/2021MonCLUB 4VENUE412:00pm10
631/10/2021SunCLUB 3VENUE312:00pm811/11/2021ThuCLUB 5VENUE52:00pm1123/12/2021ThuCLUB 5VENUE612:00pm1011/01/2022TueCLUB 5VENUE612:00pm1026/02/2022SatCLUB 5VENUE512:00pm1025/04/2022MonCLUB 5VENUE51:00pm815/05/2022SunCLUB 5VENUE512:00pm105/06/2022SunCLUB 5VENUE512:00pm10
716/12/2021ThuCLUB 3VENUE312:00pm8
817/03/2022ThuCLUB 4VENUE412:00pm8
96/04/2022WedCLUB 4VENUE412:00pm8
107/07/2022ThuCLUB 4VENUE412:00pm8
1122/08/2021SunCLUB 4VENUE411:30am9
125/09/2021SunCLUB 4VENUE412:00pm10
1325/10/2021MonCLUB 4VENUE412:00pm10
1411/11/2021ThuCLUB 5VENUE52:00pm11
1523/12/2021ThuCLUB 5VENUE612:00pm10
1611/01/2022TueCLUB 5VENUE612:00pm10
1726/02/2022SatCLUB 5VENUE512:00pm10
1825/04/2022MonCLUB 5VENUE51:00pm8
1915/05/2022SunCLUB 5VENUE512:00pm10
205/06/2022SunCLUB 5VENUE512:00pm10
21
Sheet1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I not so sure you will find a formula to do what you are wanting. It could be done with VBA but not sure about formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
78
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
356
kevin9999
K
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
554
juanam
J
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
371
archibell
A
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
379
felixstraube
F

Forum statistics

Threads
1,214,386
Messages
6,119,212
Members
448,874
Latest member
b1step2far

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top