## Group rows based on a condition

This article explains how to merge values row by row based on a condition in column A using an array formula.

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!

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

Formula in cell B9:

You are not required to enter this as an array formula.

### Explaining formula in cell B9

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication. This may sound complicated but it is not, you can build powerful calculations across columns and rows once you understand how arrays work.

**Step 1 - Find matching vendor**

**Step 1 - Find matching vendor**

The equal sign compares the value in cell A9 to each value in cell range A2:A5. It returns TRUE if equal and FALSE if not. It is not, however, a case sensitive comparison.

This means that V1 is equal to v1. TRUE and FALSE are boolean values.

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

becomes

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

and returns

{TRUE; FALSE; TRUE; FALSE}

The first value in the array is TRUE which means that cell A2 is equal to cell value in A9. The second value in the array is FALSE which means that the value in cell A3 is not equal to the value in cell A9.

**Step 2 - Find matching product**

**Step 2 - Find matching product**

The following logical expression compares the value in cell B8 to all values in cell range B1:F1.

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

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

This steps identifies cells in cell range B2:F5 that contains 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 - Multiply arrays**

**Step 4 - Multiply arrays**

When we multiply boolean values their numerical equivalents are returned. TRUE = 1 and FALSE = 0 (zero).

Multiplying values also means that we apply AND logic meaning the boolean values we multiply must all be 1 to return 1. Example, 1*1 = 1 but 1*0 = 0. The parenthes determines the order of operation.

($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 numbers in array**

**Step 5 - Sum numbers in array**

This step adds the numbers in the array and returns a total.

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.

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 rows based on a condition”

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