## Group a number of rows together by the first column

Oscar,

I'm hoping you can help. I am trying to group a number of rows together by the first column, providing a union of the column values. In the example below, we are looking to Vendors V1-V3 to sell us some subset of products P1-P5.

; P1 ; P2 ; P3 ; P4 ; P5

V1 ; 1 ; ; ; 1 ;

V2 ; ; 1 ; ; 1 ;

V1 ; ; ; 1 ; ;

V3 ; ; ; 1 ; ; 1

Once transformed, I would like to see the following:

; P1 ; P2 ; P3 ; P4 ; P5

V1 ; 1 ; ; 1 ; 1 ;

V2 ; ; 1 ; ; 1 ;

V3 ; ; ; 1 ; ; 1

Thank you for your help!

### Answer

**Extract unique distinct values**

Array formula in cell A9:

Copy and paste array formula in A9 down as far as needed.

Read this post for more details: How to extract a unique distinct list from a column

**Group values**

Array formula in cell B9:

**How to create an array formula**

- Select cell B9
- Click in formula bar

- Copy and paste above array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell B9
- Copy cell (Ctrl + c)
- Select cell range C9:F9
- Paste (Ctrl + v)
- Select cell range B9:F9
- Copy cell range (Ctrl + c)
- Select cell range B10:F11
- Paste (Ctrl + v)

### Explaining array formula in cell B9

**Step 1 - Find matching vendor **

$A9=$A$2:$A$5

becomes

"V1 "={"V1 ";"V2 ";"V1 ";"V3 "}

and returns

{TRUE;FALSE;TRUE;FALSE}

**Step 2 - Find matching product**

B$8=$B$1:$F$1

becomes

" P1 "={" P1 "," P2 "," P3 "," P4 "," P5"}

and returns

{TRUE, FALSE, FALSE, FALSE, FALSE}

**Step 3 - Find values equal to 1**

$B$2:$F$5=1

becomes

{1," "," ",1," ";" ",1," ",1,"";" "," ",1," "," ";" "," ",1," ",1}=1

and returns

{TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, TRUE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, TRUE}

**Step 4 - Return array of values**

($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5=1)

becomes

({TRUE;FALSE;TRUE;FALSE})*({TRUE, FALSE, FALSE, FALSE, FALSE})*{TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, TRUE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, TRUE})

and returns

{1, 0, 0, 0, 0;0, 0, 0, 0, 0;0, 0, 0, 0, 0;0, 0, 0, 0, 0}

**Step 5 - Sum array of values**

SUMPRODUCT({1, 0, 0, 0, 0;0, 0, 0, 0, 0;0, 0, 0, 0, 0;0, 0, 0, 0, 0})

returns 1 in cell B9.

### Download excel *.xlsx file

### 5 Responses to “Group a number of rows together by the first column”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

It works perfectly - Thanks Oscar!

Oscar,

To modify the plan above and use non-binary values (e.g. use a list that includes quantities or prices for the table values (e.g. 100, 45, 12), would we have to move from SUMPRODUCT back to the INDEX model?

Nevermind - got it.

Setting up the B9 construct in the following manner would add up all of the values that correspond to the "V1" (etc) column value and place them in a given spot.

=IF(SUMPRODUCT(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5>0))>0,sum(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5=1)),"")

Mike,

This array formula seems also to work:

=SUMPRODUCT(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*$B$2:$F$5)

Thanks Oscar - that's what I ultimately used to avoid a linearity issue. I just forgot to repost.

Thanks for your help!