Compare and Rank Rows

[Utradeshow]

Hi Guy's,

I have this sheet that has hundreds if shipments. I am trying to summarize the data and find out how many of shipments went from the same origin and to the same destination and then rank them. What would be the best way to do this?

Find Air Freight Volume by Location Query.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		114222		########	########	671788		448061		Adairsville	30103	448062		Forest Park	30297
3		116609		1/3/2024	1/8/2024	676629		460583		Adairsville	30103	460584		Forest Park	30297
4		116610		1/3/2024	########	676641		460618		Adairsville	30103	460619		Forest Park	30297
5		116611		2/1/2024	2/9/2024	678083		464143		Adairsville	30103	464144		Forest Park	30297
6		118756		########	2/1/2024	677926		463753		Adairsville	30103	463754		Forest Park	30297
7		114433		3/7/2024	########	679667		468286		ADDISON	60101	468287		Des Plaines	60018
8		119949		########	########	679011		466532		ADDISON	60101	466533		Des Plaines	60018
9		103773		########	5/4/2023	665316		431560		Addison	75001	431561		Grapevine	76051
10		106620		########	########	666023		433374		ADDISON	75001	433375		Irving	75063
11		111426		########	9/1/2023	670497		444767		ADDISON	60101	444768		Logan	84321
12		124759		########	########	685413		483476		Addison	60101	483477		MANHATTAN BEACH	90266
13		92179		2/8/2023	########	661044		420764		AIEA	96701	420765		Honolulu	96819
Sheet3

 

[Cubist]

Which column indicates the origin?
Which column indicates the destination?
Rank them based on what?

 

[Utradeshow]

Which column indicates the origin?
Which column indicates the destination?
Rank them based on what?

On how many matching Origin Destination matches.

COLUMN J is Origin
COLUMN N is Destination

 

[Cubist]

With the new GROUPBY function:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1										Origin				Destination			Origin	Match Count
2		114222		45196.41667	45198	671788		448061		Forest Park	30103	448062		Forest Park	30297		Forest Park	5
3		116609		45294.41667	45299.85417	676629		460583		Forest Park	30103	460584		Forest Park	30297		ADDISON	2
4		116610		45294.41667	45306.85417	676641		460618		Forest Park	30103	460619		Forest Park	30297		AIEA	0
5		116611		45323	45331.85417	678083		464143		Forest Park	30103	464144		Forest Park	30297		Total	7
6		118756		45320.41667	45323	677926		463753		Forest Park	30103	463754		Forest Park	30297
7		114433		45358	45363	679667		468286		ADDISON	60101	468287		Des Plaines	60018
8		119949		45344	45348	679011		466532		ADDISON	60101	466533		Des Plaines	60018
9		103773		45041	45050	665316		431560		Addison	75001	431561		Addison	76051
10		106620		45061	45068	666023		433374		ADDISON	75001	433375		ADDISON	75063
11		111426		45169	45170	670497		444767		ADDISON	60101	444768		Logan	84321
12		124759		45464.375	45468.41667	685413		483476		Addison	60101	483477		MANHATTAN BEACH	90266
13		92179		44965	44974	661044		420764		AIEA	96701	420765		Honolulu	96819
Sheet3

Cell Formulas
Range	Formula
Q2:R5	Q2	=GROUPBY(J2:J13,--(J2:J13=N2:N13),SUM,0,1,-2)
Dynamic array formulas.

 

[Utradeshow]

Yes sir, Thank you so much