Rearranging rows based on sales and text

[SomeGuyHere]

Hello! I'm trying to use VBA to rearrange rows in the data I have. I want to Make sure all the variations of E and F are grouped together below the highest E or F value. Essentially rearranging rows from the original data in columns A and B to the final arrangement in columns D and E. Is this possible to do? Thank you!

 

[JEC]

Select range in A2:B14

Sort on Item column, that should be enough

 

[SomeGuyHere]

Select range in A2:B14

Sort on Item column, that should be enough

Sorry. It wasn't meant to be that easy.
Attached image is the correct scenario of the data I'm working with.

 

[JEC]

how about a formula (Office 365)

Book1
	A	B	C	D	E
1	item	sales		Item	Sales
2	Z	100		Z	100
3	X	99		X	99
4	Y	87		Y	87
5	M	81		M	81
6	E	80		E	80
7	F	64		EE	53
8	EE	53		EEE	22
9	FF	49		EEEE	5
10	G	44		F	64
11	O	30		FF	49
12	EEE	22		FFF	15
13	FFF	15		G	44
14	EEEE	5		O	30
Sheet1

Cell Formulas
Range	Formula
D2:E14	D2	=LET(z,A2:B14,y,INDEX(z,,1),SORTBY(z,XLOOKUP(LEFT(y),y,INDEX(z,,2)),-1))
Dynamic array formulas.

 

[SomeGuyHere]

how about a formula (Office 365)

Book1
	A	B	C	D	E
1	item	sales		Item	Sales
2	Z	100		Z	100
3	X	99		X	99
4	Y	87		Y	87
5	M	81		M	81
6	E	80		E	80
7	F	64		EE	53
8	EE	53		EEE	22
9	FF	49		EEEE	5
10	G	44		F	64
11	O	30		FF	49
12	EEE	22		FFF	15
13	FFF	15		G	44
14	EEEE	5		O	30
Sheet1

Cell Formulas
Range	Formula
D2:E14	D2	=LET(z,A2:B14,y,INDEX(z,,1),SORTBY(z,XLOOKUP(LEFT(y),y,INDEX(z,,2)),-1))
Dynamic array formulas.

Oh that's amazing. Thanks!

How do I go about expanding that array? My actual data set is much larger than the 14 rows. When I expand the box, it only sorts the original 14 rows. Same with columns. My data is about 5 columns wide and 20 rows down.

 

[JEC]

This macro puts the output a few cells to the left to show the result. You could also override your existing data

Sub jec()
 Dim dic, ar, a, j As Long
 ar = Cells(1).CurrentRegion
 Set dic = CreateObject("scripting.dictionary")
 
 For j = 2 To UBound(ar)
   If dic.exists(Left(ar(j, 1), 1)) Then
     a = dic(Left(ar(j, 1), 1))
     a(0) = ar(j, 1)
     a(1) = ar(j, 2)
     dic(ar(j, 1)) = a
   Else
     dic(Left(ar(j, 1), 1)) = Array(ar(j, 1), ar(j, 2), ar(j, 2))
   End If
 Next
   
 With Cells(1, 10).Resize(dic.Count, 3)
   .Value = Application.Index(dic.items, 0, 0)
   .Sort .Cells(1, 3), 2
   .Columns(3).ClearContents
 End With
 
End Sub

 

[JEC]

This is all based on just one the 2 columns. If you have more columns with items and values, the solutions looks completely different

 

[SomeGuyHere]

This is all based on just one the 2 columns. If you have more columns with items and values, the solutions looks completely different

Can xlookup be used to populate the remaining columns? Only the first 2 columns are used as a reference of order. Not sure how to go about doing that, as well as expanding the formula to more rows.

 

[JEC]

Ok can you give more sample data to make everything clearer?

 

[SomeGuyHere]

Ok can you give more sample data to make everything clearer?

Absolutely. Attached is a broader example.