CODEWITHSUNDEEP
excelexcel-formula
vedar
vedar

Reputation: 523

Transpose column with duplicates to unique columns

I wonder if it's possible to transpose column with grouped duplicate fields, like for example:

+---------+------+
| field_1 | 1    |
| field_2 | 2    |
| field_3 | 3    |
| field_4 | 4    |
| field_1 | 5    |
| field_2 | 6    |
| field_3 | 7    |
| field_4 | 8    |
| field_1 | 9    |
| field_2 | 10   |
| field_3 | 11   |
| field_4 | 12   |
+---------+------+

to unique columns, like this:

+---------+---------+---------+---------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------+
| 1       | 2       | 3       | 4       |
| 5       | 6       | 7       | 8       |
| 9       | 10      | 11      | 12      |
+---------+---------+---------+---------+

Is this possible without scripting?

Upvotes: 0

Views: 275

Answers (3)

Tim Williams
Tim Williams

Reputation: 166885

Enter the formula below in E3 (using Ctrl+Shift+Enter) then fill down and then across.

=OFFSET($C$1,LARGE((ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2),ROW()-2),0) & ""

enter image description here

Values are not in the listed order but are at least in the correct column.

Will try to explain:

  • All of the values we want for each of the distinct "field" values are offset by some "x" number of rows from C1

  • Taking "field_1" as an example (ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2) will return an array of row numbers multiplied by either 1 (TRUE) or zero (FALSE) depending on whether the "B" cell value matches "field_1" (from E2). The -1 is an adjustment because we're starting on row 2. The returned array looks like:

    [1,0,0,0,5,0,0,0,9,0,0,0]

  • Given that array, we need to find some way to get at the non-zero values one by one: we can use LARGE() to do that, incrementing the second argument to LARGE using ROW()-2 (-2 because we want that second argument to begin at 1 and the formula is being entered in row 3). This gives us an ordered array of values:

    [9,5,1,0,0,0,0,0,0,0,0,0]

  • We pass this ordered array as the "row offset" argument to OFFSET(), counting down from C2: all non-zero values give us the value we want from Col C, whereas all the zero values just return C2 (which is empty, and would show as 0 without that last & ""

  • All cell references in the formula use $ where needed so it adjusts as needed on dragging to fill down/across

Upvotes: 5

Forward Ed
Forward Ed

Reputation: 9894

My answer is very similar to Tim's but avoids the CSE part and internalizes it within the AGGREGATE function. Also believe it will preserve the vertical order in which the element in B occurs (not tested though).

In D1 use the following formula and copy right to get your unique list of headers.

=INDEX($A$1:$A$12,MATCH(0,INDEX(COUNTIF($C1:C1,$A$1:$A$12),0,0),0))

Then in D2 use the following to pull your information from column B. It can be copied to the right and down. When an item is not found it will display not found. you can change this to "" if you prefer.

=IFERROR(INDEX($B$1:$B$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($A$1:$A$12=D$1),ROW(1:1))),"Not found")

Proof of Concept

Proof of concept

Note on OFFSET

OFFSET is a volatile function meaning that it will recalculate anytime anything on the worksheet changes, not just the cells it works with. As such if you are only using it a couple of times, no big deal. However if you are using it repeatedly you may experience a slow down with your worksheet.

Upvotes: 0

findwindow
findwindow

Reputation: 3153

This only gets you so far. You still need to remove duplicates. In D2:

=INDEX($B1:$B$12,MATCH(D$1,$A1:$A$12,0))

enter image description here

Upvotes: 1

Related Questions