2

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.

3

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

| improve this answer | |
  • Thanks for the assistance. The H2 formula is returning an empty set though. No data being shown in H column when running that formula. – optimus203 May 5 '18 at 4:01
  • 1
    @|'-'| - awesome, your tweak to original response works now. Thanks! – optimus203 May 5 '18 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.