Predominant formula help

[Springer_30]

I have posted a similar question before, but this one is a bit different.


I have a spreadsheet I am using to assist in building a distribution delivery model with. Each customer account is unique and has various customer details assigned to it.

There are however different geo-codes for some of the customers and I am trying to return the geo-code which occurs the most under a particular customer account number.

I do have a previous formula that works but it puts too much strain on my system.

See examlple below:

Column Ais the customer account number
Column B the lats
Column Cis the longs
Column Dthe answer I would like returned for LATS
Column Ethe answer I would like returned for LONGS

Is this possible?


<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=423 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=3 width=82><TBODY><TR style="HEIGHT: 47.45pt; mso-height-source: userset" height=63><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 77pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 47.45pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=102 height=63>Customer account</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 56pt; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=75>Latitude</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 62pt; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=82>Longitude</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 62pt; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=82>Answer lats</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 62pt; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=82>Answer longs</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145209</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.940664</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.589102</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145209</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.940577</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.589142</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145209</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.940641</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.589114</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145209</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145209</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-33.931705</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.578311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145733</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.037851</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.615698</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145733</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.037938</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.615712</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145733</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.037743</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.615616</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145733</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right height=20>145733</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>-34.192746</TD><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" align=right>18.42853</TD></TR></TBODY></TABLE>

 

[GlennUK]

And what is the previous formula, that puts too much strain on your system? For all I know it's the same as my array formula ( entered using Ctrl-Shift-Enter instead of Enter ):

Excel Workbook
	A	B	C	D	E
1	Customer account	Latitude	Longitude	Answer lats	Answer longs
2	145209	-33.9407	18.5891	-33.9317	18.57831
3	145209	-33.9406	18.58914	-33.9317	18.57831
4	145209	-33.9406	18.58911	-33.9317	18.57831
5	145209	-33.9317	18.57831	-33.9317	18.57831
6	145209	-33.9317	18.57831	-33.9317	18.57831
7	145733	-34.0379	18.6157	-34.1927	18.42853
8	145733	-34.0379	18.61571	-34.1927	18.42853
9	145733	-34.0377	18.61562	-34.1927	18.42853
10	145733	-34.1927	18.42853	-34.1927	18.42853
11	145733	-34.1927	18.42853	-34.1927	18.42853
Sheet3

 

[Springer_30]

Thanks Glenn

It has worked just fine!!!