1

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?

rawdata
  • 35
  • 8
  • yes, but depending on which version of Excel you have will determine if you need helper columns or not. – Scott Craner Aug 02 '21 at 15:19
  • 1
    see here for the helper column route: https://stackoverflow.com/questions/68486496/outputting-multiple-vlookup-values-in-excel/68487015#68487015 – Scott Craner Aug 02 '21 at 15:23
  • @ScottCraner do you know if there is a formula for completing this? I thought TEXTJOIN would work but there are different number of courses tied to the course number, i.e. 3 courses for 6002100 but 1 for the other 2 course codes. – rawdata Aug 02 '21 at 15:24
  • Like I said depending on your version. What version do you have? – Scott Craner Aug 02 '21 at 15:24
  • @ScottCraner 2016 on my laptop, 2019 on my desktop – rawdata Aug 02 '21 at 15:30
  • 2016 does not have TEXTJOIN, so you will need to do the helper column like the link I showed. – Scott Craner Aug 02 '21 at 15:32

2 Answers2

4

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
2

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.

Can.U
  • 461
  • 2
  • 8