Group Multiple Rows Based on Numerous Conditions

[dmcgetti]

I am sorry, I have a project that know exaclty what I want it to do, but might have a little problem explaining...

Spreadsheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]INVOICE[/TD]
[TD]CUSTOMER[/TD]
[TD]PART[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]2416817[/TD]
[TD]Z202535[/TD]
[TD]ORANGES[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2418964[/TD]
[TD]Z202535[/TD]
[TD]APPLES[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2418988[/TD]
[TD]Z202535[/TD]
[TD]APPLES[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2400505[/TD]
[TD]Z2235217[/TD]
[TD]APPLES[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2400505[/TD]
[TD]Z2235217[/TD]
[TD]ORANGES[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2400505[/TD]
[TD]Z2235217[/TD]
[TD]BANANAS[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2443123[/TD]
[TD]Z2544701[/TD]
[TD]BANANAS[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2443987[/TD]
[TD]Z2544701[/TD]
[TD]APPLES[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2443856[/TD]
[TD]Z2544701[/TD]
[TD]BANANAS[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Essentially I want to take all of this info and put it into 3 rows, one row for each customer.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]ORANGE INVOICES[/TD]
[TD]ORANGES QTY[/TD]
[TD]APPLE INVOICES[/TD]
[TD]APPLE QTY[/TD]
[TD]BANANAS INVOICES[/TD]
[TD]BANANAS QTY[/TD]
[/TR]
[TR]
[TD]Z202535[/TD]
[TD]2416817[/TD]
[TD]2[/TD]
[TD]2418964, 2418988[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z2235217[/TD]
[TD]2400505[/TD]
[TD]6[/TD]
[TD]2400505[/TD]
[TD]4[/TD]
[TD]240050[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Z2544701[/TD]
[TD][/TD]
[TD][/TD]
[TD]2443987[/TD]
[TD]4[/TD]
[TD]244123, 2443856[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense, Let me know if I need to clarify anything!

Thank as always!

Darren

 

[DRSteele]

I think this might do it.

Step 1 Determine the number of unique customers. I include two methods, one is apparently slow on large data sets (but is simple) and the other is fast on large data sets (but is complex).

Step 2 Extract unique list of customers. Copy formulas in A17:G17 down many rows as there are customers.

Step 3 Ensure the headings in Row 16 match exactly the headings in the PART field of your data table.

Step 4 Use function TEXTJOIN (which is new in Excel 2016) to create a one-cell list of INVOICES numbers per PART. (If you don't have Excel 2106, let us know and we'll investigate ways of concatenating the text fields.)

references: https://www.youtube.com/watch?v=3u8VHTvSNE4

	A	B	C	D	E	F	G
Z202535	ORANGES
Z202535	APPLES
Z202535	APPLES
Z2235217	APPLES
Z2235217	ORANGES
Z2235217	BANANAS
Z2544701	BANANAS
Z2544701	APPLES
Z2544701	BANANAS
Customers	using SUMIFS; slow
Customers	using FREQUENCY; fast

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]INVOICE[/TD]
[TD="bgcolor: #FFF2CC"]CUSTOMER[/TD]
[TD="bgcolor: #FFF2CC"]PART[/TD]
[TD="bgcolor: #FFF2CC"]QUANTITY[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2416817[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2418964[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2418988[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2400505[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2400505[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2400505[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2443123[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2443987[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2443856[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #E2EFDA, align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]INVOICES[/TD]
[TD="bgcolor: #FFF2CC"]QTY[/TD]
[TD="bgcolor: #FFF2CC"]INVOICES[/TD]
[TD="bgcolor: #FFF2CC"]QTY[/TD]
[TD="bgcolor: #FFF2CC"]INVOICES[/TD]
[TD="bgcolor: #FFF2CC"]QTY[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFF2CC"]CUSTOMER[/TD]
[TD="bgcolor: #FFF2CC"]ORANGES[/TD]
[TD="bgcolor: #FFF2CC"]ORANGES[/TD]
[TD="bgcolor: #FFF2CC"]APPLES[/TD]
[TD="bgcolor: #FFF2CC"]APPLES[/TD]
[TD="bgcolor: #FFF2CC"]BANANAS[/TD]
[TD="bgcolor: #FFF2CC"]BANANAS[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #C6E0B4"]Z202535[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2416817[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2[/TD]
[TD="bgcolor: #C6E0B4"]2418964, 2418988[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #C6E0B4"]Z2235217[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2400505[/TD]
[TD="bgcolor: #C6E0B4, align: right"]6[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2400505[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2400505[/TD]
[TD="bgcolor: #C6E0B4, align: right"]9[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #C6E0B4"]Z2544701[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2443987[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="bgcolor: #C6E0B4"]2443123, 2443856[/TD]
[TD="bgcolor: #C6E0B4, align: right"]14[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]=SUMPRODUCT(1/COUNTIFS(B2:B10,B2:B10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C17[/TH]
[TD="align: left"]=IF(A17="","",SUMIFS($D$2:$D$10,$C$2:$C$10,C$16,$B$2:$B$10,$A17))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E17[/TH]
[TD="align: left"]=IF(C17="","",SUMIFS($D$2:$D$10,$C$2:$C$10,E$16,$B$2:$B$10,$A17))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G17[/TH]
[TD="align: left"]=IF(E17="","",SUMIFS($D$2:$D$10,$C$2:$C$10,G$16,$B$2:$B$10,$A17))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($B$2:$B$10<>"",MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),1))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A17[/TH]
[TD="align: left"]{=IF(ROWS(A$17:A17)>$B$13,"",INDEX($B$2:$B$10,SMALL(IF(FREQUENCY(IF($B$2:$B$10<>"",MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS(A$17:A17))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(($B$2:$B$10=$A17)*($C$2:$C$10=B$16),$A$2:$A$10,""))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D17[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(($B$2:$B$10=$A17)*($C$2:$C$10=D$16),$A$2:$A$10,""))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F17[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(($B$2:$B$10=$A17)*($C$2:$C$10=F$16),$A$2:$A$10,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[dmcgetti]

I think this might do it.

Step 1 Determine the number of unique customers. I include two methods, one is apparently slow on large data sets (but is simple) and the other is fast on large data sets (but is complex).

Step 2 Extract unique list of customers. Copy formulas in A17:G17 down many rows as there are customers.

Step 3 Ensure the headings in Row 16 match exactly the headings in the PART field of your data table.

Step 4 Use function TEXTJOIN (which is new in Excel 2016) to create a one-cell list of INVOICES numbers per PART. (If you don't have Excel 2106, let us know and we'll investigate ways of concatenating the text fields.)

references: https://www.youtube.com/watch?v=3u8VHTvSNE4

If I understand this correctly, I believe it will work, but I have to add something. I though I would be able to adapt the formula easily, but it is a little more complicated than I thought...

My list of "PARTS" are auto part numbers. They are in this format "AAAA*BBBB*CC", and what I need to search is the "BBBB", the "base" portion of the part number. And to make it more interesting, I need to group 3-4 "base" numbers together. I was thinking of putting the 3-4 "base" part numbers in a named range to reference.

Thanks again for the help so far!!