CODEWITHSUNDEEP
excelexcel-formulamergepivottranspose
rawdata
rawdata

Reputation: 35

Excel Transpose and or Merge cells

I have an excel list of school courses that needs to be transposed/merged, the data looks like this

enter image description here

I want it to look like this

6001100 Elem Education 6002100 English, ESE Support Facilitator, ESE Teacher,Math 6004100 Elem Education

Is this possible without VBA?

Upvotes: 1

Views: 241

Answers (2)

Can.U
Can.U

Reputation: 481

With Excel 365. D2:

=INDEX(UNIQUE(A$2:A$8),ROW(A1))&" "&TEXTJOIN(", ",,FILTER(B:B,A:A=INDEX(UNIQUE(A$2:A$8),ROW(A1))))

enter image description here

or older version: Format Column A using this format text. E2：

=D2&SUBSTITUTE(PHONETIC(OFFSET(A$1,MATCH(D2,A:A,)-1,,COUNTIF(A:A,D2),2)),D2," ")

enter image description here

English is not my native language; please excuse typing errors.This is for your reference only.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

With Office 365 we can use UNIQUE to get a list of unique Row Labels:

=UNIQUE(A2:A9)

With 2019 one would have to get the Unique list with:

=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$9)/(COUNTIF($E$1:E1,$A$2:$A$9)=0),1)),"")

Then use TEXTJOIN to do the concatenation:

=E2&" " &TEXTJOIN(", ",TRUE,FILTER(B:B,A:A=E2,""))

enter image description here

With 2016 use the formula for the unique list from 2019 and then use the method here: Outputting multiple VLookup values in Excel

To use a helper column then use:

 =E2&" "&VLOOKUP(E2,A:C,3,FALSE)

to return the desired value:

enter image description here

Upvotes: 4

Related Questions