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

*Article last updated on November 30, 2012*

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

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Merge three columns into one list in excel

Question: How do I merge three columns into one list? Answer: Excel 2007 array formula in D2: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Merge tables based on a condition

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]

Automate excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Combine cell ranges eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Tracking a stock portfolio #2 in excel

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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!