Google-sheets – Concatenate Car Parts Data

google sheets

I currently have a list of car parts that I'm trying to get into a specific format so I can import.

Example of Data Set

MAKE      MODEL      YEAR      PART

Make1     Model1     1950      Part1
Make1     Model1     1950      Part2
Make1     Model1     1951      Part1
Make1     Model1     1951      Part2
Make1     Model1     1951      Part3
Make1     Model1     1952      Part1
Make1     Model1     1952      Part2

Make1     Model2     1950      Part1
Make1     Model2     1950      Part2
Make1     Model2     1951      Part1
Make1     Model2     1951      Part2
Make1     Model2     1951      Part3
Make1     Model2     1952      Part1
Make1     Model2     1952      Part2

So for each Make, Model, and Year combination I need to have all the products matching that combination in one cell.

For example, this is the desired table result from the initial data set.

MAKE      MODEL      YEAR     PART

Make1     Model1     1950     Part1,Part2
Make1     Model1     1951     Part1,Part2,Part3
Make1     Model1     1952     Part1,Part2

Make1     Model2     1950     Part1,Part2
Make1     Model2     1951     Part1,Part2,Part3
Make1     Model2     1952     Part1,Part2

The catalog has close to 10,000 variations. Anybody have a solution for this? I've tried playing around with MATCH and VLOOKUP, but had no success. Any assistance would be greatly appreciated.

Best Answer

E2:

=UNIQUE(A2:C)

H2:

=ARRAYFORMULA(TEXTJOIN(",",1,IF((E2=A:A)*(F2=B:B)*(G2=C:C),D:D,)))

Copy/Paste(or Drag Fill) rest of H2:H