0

In Excel 2016, I have a "Brands" column, each of its rows containing a string. The set of possible string values is limited, and they may appear more than once.

There is related data in another column, "Models". Each model value is always different.

How can I get Excel to generate a column for each existing brand and populate it with the corresponding Models in an automatic way?

My problem is that I can't do it manually because for each analysis, the amount of Brands is different and I don't know it beforehand.

This would be the input:

enter image description here

And this is the expected output:

enter image description here

Any ideas?

Thank you in advance!

jarmanso7
  • 117
  • 1
  • 1
  • 8

1 Answers1

1

Try using an array formula. First, put the distinct brand values on row 1, starting in column D. You can use column C if you want, but I like to have an empty column between the data and the desired results.

D1 = Apple, E1 = Nokia, F1 = Samsung, G1 = Xiaomi

Then, just below Apple in cell D2, paste this formula:

=IFERROR(INDEX($B$2:$B$9999, SMALL(IF(D$1=$A$2:$A$9999, ROW($A$2:$A$9999)-ROW($A$2)+1), ROW(1:1))),"")

If you have more than 9999 rows, then adjust as needed in the formula.

With the cursor still in the formula, make it an array by simultaneously pressing CTRL-SHIFT-ENTER.

Copy the formula across to cells E2, F2 and G2. You may need to repeat the array trick (CTRL-SHIFT-ENTER) for each of those again if things look wrong. So, your excel will look like this:

enter image description here

Now, drag the formulas down as far as you need. The iferror part of the formula will ensure that cells look clean if no more models are found.

enter image description here

---EDIT AFTER RECENT COMMENT---

I cannot determine how to automatically pick distinct values from column A and automatically convert them to a row. It's easy to keep it in a column, but the transpose to row is troubling.

At any rate, here's the ugly update. Cell D1 would simply state "Brands". In cell D2, make this an array formula (CTRL-SHIFT-ENTER).

=IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$9), 0)), "")

So, row 2 will be your brands. Drag the formula across, repeat the array trick.

Now, in cell E1, type formula =D2. Drag across.

Place the formula suggestion from the original answer, starting in row 3. End result looks like this below. It should be "automated" now, but it's not appealing. Minor edits will help (making row 1 nearly invisible, for example).

enter image description here

Isolated
  • 5,169
  • 1
  • 6
  • 18
  • Thank you very much for your effort, but... where is the automation in "First, put the distinct brand values on row 1, starting in column D"? This is precisely what I want to achieve automatically, if posible. My problem is "**generating columns automatically**", but in your answer you tell me to create them manually. – jarmanso7 Jul 30 '20 at 22:00
  • Yeah, that would be manual, sorry. Found plenty of reference to a formula to automatically get distinct values, but they were all in column format (whereas you want to automatically transpose to a row). I've hacked the format to row, but it's not pretty and still needs work. I'll EDIT original answer, but again it's ugly. – Isolated Jul 31 '20 at 21:13
  • I finally achieved it using Visual Basic for Applications. I will post an answer once it's completed – jarmanso7 Aug 01 '20 at 07:56