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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

L
  • Solved
Help Please
2 3
Replies
21
Views
513
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
165
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
396
dshtesy
D
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
117
vancher19
V
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
273
Raj1979
R

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

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