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 V1V3 to sell us some subset of products P1P5.
; 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 Ranges is an addin for Excel that lets you easily merge multiple ranges into one master sheet. The Master [โฆ]
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
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 nonbinary 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!