Converting rows to columns

[mahutchinson]

Hi all, our IT department has been stumped by this - we need to convert a report from multiple rows for each client representing different services to one row for each client with the services in columns as per the attached. Any ideas ?

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[JohnTopley]

Please update your Excel version/release [64 is not one!!!] as answers do depend on knowing this information

[etaf]

Edit - answers given while i was creating the example spreadsheet

EDIT - JohnTopley - correction in later post - also updated the spreadsheet

how about using
=IF(COUNTIFS($A$2:$A$15,$E2,$B$2:$B$15,F$1),F$1,"")

Not sure which version of excel you have - but this uses transpose & unique
otherwise the table headings can just be setup manually

[JohnTopley]

Minor correction

=IF(COUNTIFS($A$2:$A$15,$E2,$B$2:$B$15,F$1),F$1,"")

[etaf]

thanks JohnTopley
I have updated the post and spreadsheet to reflect your correction
thanks again

[Glenn Kennedy]

If you have the latest version of O365, you can do the lot in a single cell, using:

=LET(A,'Current data'!A2:A15,B,'Current data'!B2:B15,C,UNIQUE(A),D,TRANSPOSE(UNIQUE('Current data'!B2:B15,FALSE)),E,DROP(REDUCE("",D,LAMBDA(y,z,HSTACK(y,BYROW(C,LAMBDA(x,IFERROR(IF(MATCH(1,(A=x)*(B=z),0)>0,z,""),"")))))),,1),VSTACK(HSTACK("",D),HSTACK(C,E)))

[sandy666]

with Power Query

Client	Service		Client	a	b	c	d	e	f	g
Client 1	Service a		Client 1	Service a	Service b	Service c	Service d	Service e
Client 1	Service b		Client 2	Service a		Service c	Service d		Service f	Service g
Client 1	Service c		Client 3			Service c
Client 1	Service d		Client 4			Service c	Service d		Service f
Client 1	Service e
Client 2	Service a
Client 2	Service c
Client 2	Service d
Client 2	Service f
Client 2	Service g
Client 3	Service c
Client 4	Service c
Client 4	Service d
Client 4	Service f

you have redundant spaces in the source table

[mahutchinson]

Sorry - it's MS365 and version 2205.

[mahutchinson]

with Power Query

Client	Service		Client	a	b	c	d	e	f	g
Client 1	Service a		Client 1	Service a	Service b	Service c	Service d	Service e
Client 1	Service b		Client 2	Service a		Service c	Service d		Service f	Service g
Client 1	Service c		Client 3			Service c
Client 1	Service d		Client 4			Service c	Service d		Service f
Client 1	Service e
Client 2	Service a
Client 2	Service c
Client 2	Service d
Client 2	Service f
Client 2	Service g
Client 3	Service c
Client 4	Service c
Client 4	Service d
Client 4	Service f

you have redundant spaces in the source table

What we actually wanted was a "Yes" or tick rather than the actual service description for each client (and the service description just at the top of the relevant column) but I assumed we would need to go with the data that was already there and have the service description written out in each cell as it was originally.

[etaf]

What we actually wanted was a "Yes" or tick rather than the actual service description for each client

Thats not what your "required data" showed

=IF(COUNTIFS($A$2:$A$15,$E2,$B$2:$B$15,F$1),"Yes","")

many years ago, I made an attendance tracker and used marlett font with an lowercase "a" to get a tick
=IF(COUNTIFS($A$2:$A$15,$E2,$B$2:$B$15,F$1),"a","")

[sandy666]

What we actually wanted was a "Yes" or tick rather than the actual service description for each client (and the service description just at the top of the relevant column) but I assumed we would need to go with the data that was already there and have the service description written out in each cell as it was originally.

solution was tailored to your example, I didn't see there any requirement different than I posted

[Glenn Kennedy]

Please upload a sample sheet showing what you DO want... not what you DO NOT want.

[sandy666]

... and DON'T QUOTE WHOLE POST but required part of text only

[sandy666]

What we actually wanted was a "Yes" or tick rather than the actual service description for each client (and the service description just at the top of the relevant column)

tick.png

unfortunately the forum doesn't recognize the tick (square root) symbol and that's why there is a picture

[Glenn Kennedy]

Formulae (3) to produce column and row headers and result.