Searching Dynamic Range for most common value while ignoring text strings

[catchg]

I have an excel workbook that has several sheets. One of the sheets is named “Data” which contains 2 columns filled with latitude and longitude values. The latitude column is a dynamic range named “LatitudeSeries”. The longitude column is a dynamic range named “LongitudeSeries”.

The Latitude data is in the format: 29 50.0630N
The Longitude data is in the format: 081 23.0193W
In addition to the numbers, both columns contain random cells that have a text string named: Not Valid

I would like to create a formula that searches both dynamic range series for the most common value or the pair that appears the most times. The formula needs to ignore the text string: “Not Valid” but not ignore the number due to the “N” or “W” in the latitude/longitude data. I would like the most common pair to be extracted from the dynamic range and placed on a sheet named “GPS” in cell A1 for latitude and A2 for longitude.

Should I set up a MODE formula for latitude column and Vlookup for its respected longitude value?

I have been working with a formula that ignores the text string and finds the most common value but it also ignores the latitude value due to the”N” in the number. And I have been unable to direct it to the dynamic range named “Data!LatitudeSeries”

=IF(MODE(R1:R10)>0,MODE(R1:R10),"")

Is there a way to search for the most common pair while ignoring test string? Should I set up a mode formula for latitude range and vlookup longitude?

Any assistance would be appreciated!

 

[iyyi]

You can do it with one Helper Column. If have your LatitudeSeries in column A and the LongitudeSeries in column B, then you can add a column with the formula: =IF(A1<>"",A1&B1,"")
Copy the formula to the maximum number of rows you expect to have. Give the cells in the new column the name "All".
Now for Latitude you can use the formula:
=INDEX(LatitudeSeries,MAX((MAX((IF(ISERROR(SEARCH("Not Valid",All))*(All<>""),COUNTIF(All,All))))=IF(ISERROR(SEARCH("Not Valid",All)),COUNTIF(All,All)))*ROW(All)))
Enter with Ctrl+Shift +Enter

For the Longitude you can use the formula:
=INDEX(LongitudeSeries,MAX((MAX((IF(ISERROR(SEARCH("Not Valid",All))*(All<>""),COUNTIF(All,All))))=IF(ISERROR(SEARCH("Not Valid",All)),COUNTIF(All,All)))*ROW(All)))
Enter with Ctrl+Shift +Enter

 

[catchg]

Thank you, iyyi, for taking the time to reply to my post. Because the data is imported and always has a different number of columns, I can not predict the number of rows to copy the "All" formula. Most importantly, copying the "All" formula in every cell down the column creates a large workbook file. Any alternatives?

 

[iyyi]

Thank you, iyyi, for taking the time to reply to my post. Because the data is imported and always has a different number of columns, I can not predict the number of rows to copy the "All" formula. Most importantly, copying the "All" formula in every cell down the column creates a large workbook file. Any alternatives?

I don't think file size should be a problem. One column with that formula in 20,000 rows occupies 188 KB.
You don't need to predict the number of the rows. Assign the name "All" to the maximum possible number of row.
I hope it helps, I don't have other suggestions.