Create a sorted list of similar items from a table

[Tarver]

I'm working with a large list of departments and divisions, and need to extract only the items that have a matching prefix. For instance, my list looks like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]D01 001 Testing Equipment[/TD]
[/TR]
[TR]
[TD]D02B 001 Maintenance Supplies[/TD]
[/TR]
[TR]
[TD]D02B 003 Hand Tools[/TD]
[/TR]
[TR]
[TD]D02B 004 Power Tools[/TD]
[/TR]
[TR]
[TD]D02B 010 Power Generators[/TD]
[/TR]
[TR]
[TD]D09A 001 Gardening Equipment[/TD]
[/TR]
[TR]
[TD]D10C 002 Widgets[/TD]
[/TR]
</tbody>[/TABLE]

I'd like to match the prefix of D02B and extract all of (but only) the items with the D02B prefix, and sort them in order. (Assume that my prefix is in Cell A1, and my list in cells B1:B7.)

Can I do this without VBA, using a formula (or series of formulas) or an array formula?

Thanks in advance!

 

[Eric W]

When you say "Sort them in order", do you mean by the number after the D02B, or by the character description?

Here's a version that just extracts the matching prefix, and the list comes out sorted by number, because it starts out that way:

	A	B	C	D
D02B	D02B 001 Maintenance Supplies
D02B 003 Hand Tools
D02B 004 Power Tools
D02B 010 Power Generators

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FAFAFA"]D01 001 Testing Equipment[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 001 Maintenance Supplies[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 003 Hand Tools[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 004 Power Tools[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 010 Power Generators[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D09A 001 Gardening Equipment[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D10C 002 Widgets[/TD]
[TD="align: right"][/TD]

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

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

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

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$1:$B$10,SMALL(IF(LEFT($B$1:$B$10,LEN($A$1))=$A$1,ROW($B$1:$B$10)),ROWS($D$1:$D1))),"")}[/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]



Now if you want the list sorted by the character string, try:

	A	B	C	D	E
D02B	Testing Equipment	D02B 003 Hand Tools
Maintenance Supplies	D02B 001 Maintenance Supplies
Hand Tools	D02B 010 Power Generators
Power Tools	D02B 004 Power Tools
Power Generators
Gardening Equipment
Widgets

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FAFAFA"]D01 001 Testing Equipment[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 001 Maintenance Supplies[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 003 Hand Tools[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 004 Power Tools[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D02B 010 Power Generators[/TD]

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

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D09A 001 Gardening Equipment[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]D10C 002 Widgets[/TD]

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

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

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

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

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",300)),600,10000))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$1:$B$10,MOD(SMALL(IF(LEFT($B$1:$B$10,LEN($A$1))=$A$1,COUNTIFS($B$1:$B$10,$A$1&"*",$C$1:$C$10,"<"&$C$1:$C$10)+ROW($C$1:$C$10)/1000),ROWS($E$1:$E1)),1)*1000),"")}[/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]



This requires the formula in C to extract the string only, then the formula in D extracts and sorts the list.

If you want it sorted by the second number, and the full list doesn't have that in order, then a minor change to the second formula would work for you.

Let me know if this helps!