Group rows based on a condition
What's on this page
1. Group rows based on a condition
This section explains how to merge values row by row based on a condition in column A using an array formula.
Mike asks:
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
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
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
This steps identifies cells in cell range B2:F5 that contains 1.
$B$2:$F$5=1
returns {TRUE, FALSE, ... , TRUE}
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)
returns {1, 0, ... , 0}
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.
2. Merge matching rows
I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there prizes daily (1 week). take note that in col. A fruits name may randomly repeated in col A. What I need is put in Sheet 2 col A all fruit name but not repeated and put to column B to H, I to N, O to U there prizes .see sample below. hope u understand.
A B C .... I
1 apple 10 11 .... 8
2 orange 9 9 ..... 10
3 apple 11 11 ..... 12
4 apple 14 10 ..... 10
5 grapes 15 15 ..... 14In sheet 2 answer should be like this.
A B C ..... H I J.....N O P.... U
1 apple 10 11 8 11 11 12 14 10 10
2 orange 9 9 10
3 grapes 15 15 14
Answer:
Array formula in cell A2:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell A2 and paste down as far as needed. See this blog post for an explanation: How to extract a unique distinct list from a column
Array formula in cell B2:
Copy cell B2 and paste B2:K4. Read more about relative and absolute cell references.
The formula above in cell B2 works only with numerical values, if cell range B2:D6 contains text values you need the following formula:
This Excel 365 dynamic array formula is explained here: REDUCE function It creates a unique list and groups adjacent values based on the values in the unique list.
Explaining array formula in cell B2
Step 1 - Filter values in matching rows
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF($A2=Sheet1!$A$2:$A$6, Sheet1!$B$2:$D$6, "")
returns
{10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}
Step 2 - Return the k-th smallest value
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
SMALL({10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}, COLUMN(A1))
returns 8.
This merging partially works for me, but instead of ordering the values on sheet 2 from smallest to largest, I need to keep the original order for sheet 1, for example instead of:
apple: 8 10 10 10 11 11 11 12 14
keep the original order from sheet 1 like this:
apple: 10 11 8 11 11 12 14 10 10
how can I change the formula(s) to do it?
Thanks!!
Answer
Function MergeMatchingRows(SearchValue As Range, SearchRange As Range) Dim r, c, ic As Single Dim temp() As Variant ReDim temp(0) For r = 1 To SearchRange.Rows.Count If SearchRange.Cells(r, 1) = SearchValue Then For c = 2 To SearchRange.Columns.Count If SearchRange.Cells(r, c) <> "" Then temp(UBound(temp)) = SearchRange.Cells(r, c) ReDim Preserve temp(UBound(temp) + 1) End If Next c End If Next r ReDim Preserve temp(UBound(temp) - 1) ic = Range(Application.Caller.Address).Columns.Count For c = UBound(temp) To ic ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next c MergeMatchingRows = temp End Function
Combine merge category
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Excel categories
9 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
This merging partially works for me, but instead of ordering the values on sheet 2 from smallest to largest, I need to keep the original order for sheet 1, for example instead of:
apple: 8 10 10 10 11 11 11 12 14
keep the original order from sheet 1 like this:
apple: 10 11 8 11 11 12 14 10 10
how can I change the formula(s) to do it?
Thanks!!
shomyx,
Great question! I can´t do it with array formulas, really complicated.
Instead I created a user defined function. See new content above!
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!
Please help me with this problem. I am trying to make a sheet of premier leauge points and try to get the result of winners for each week. And I came across with a problem that two people have equal points and excell won't work for two values. Can anybody help me? Thank you for your kindness.
my table
roll SUBJECT Th Pr Tot
1 BIOS 65 30 95
1 CHEM 65 30 95
1 PHYS 65 30 95
1 MATH 74 20 94
2 BIOS 63 30 93
2 CHEM 63 30 93
2 PHYS 63 30 93
3 BIOS 59 30 89
3 CHEM 59 30 89
3 NUTN 59 30 89
3 GEGR 59 30 89
I want result as like below to make student marksheet
roll s1 s1 th s1 pr s1 tot s2 s2 th s2 pr s2 tot s3 s3 th s3 pr s3 tot s4 s4 th s4 pr s4 tot
1 BIOS 65 30 95 CHEM 65 30 95 PHYS 65 30 95 MATH 74 20 94
2 BIOS 63 30 93 CHEM 63 30 93 PHYS 63 30 93
3 BIOS 59 30 89 CHEM 59 30 89 NUTN 59 30 89 GEGR 59 30 89