## 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

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

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 Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

Merge three columns into one list

The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]

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 […]

Combine cell ranges ignore blank cells

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]

### 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

Paste image link to your comment.

**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!