+ Reply to Thread
Results 1 to 5 of 5

Find duplicates in a string

  1. #1
    Registered User
    Join Date
    01-26-2020
    Location
    Amsterdam
    MS-Off Ver
    16
    Posts
    17

    Find duplicates in a string

    Dear all,

    I have a large list of geojson polygons in excel. Each cell contains a geojson polygon, such as this:

    {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[111.389514,-0.461461],[111.389468,-0.461498],[111.389487,-0.461534],[111.389488,-0.46158],[111.389479,-0.461618],[111.389477,-0.461673],[111.389459,-0.461752],[111.389452,-0.46183],[111.389451,-0.461893],[111.389436,-0.461941],[111.389427,-0.462026],[111.389426,-0.462087],[111.389381,-0.46211],[111.389515,-0.46146]]]}}]}

    See also attached file. I want to check if the polygon is closed. This means that the first coordinate [111.389514,-0.461461] must be the same as the last [111.389515,-0.46146]. In the upper example, this is not the case. I would like to find the cell's where this problem occurs: either by indicate correct polygons (where the first coordinate = last coordinate) or by looking for incorrect cells. To me it makes more sense to search on correct polygons (so cells that have 2 duplicate coordinate strings). Any idea what function I can use for this? I supose it should look for 2 duplicates in a string. If it find 2 duplicate strings, than the polygon is correct. Thanks in advance!
    Attached Files Attached Files
    Last edited by Kievet; 02-27-2020 at 06:03 PM. Reason: added example excel

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Find duplicates in a string

    Please read yellow banner above the post in order to help you ! Upload a sample book !
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Find duplicates in a string

    Hi Kievet,

    I came up with a VBA solution to your problem by extracting the first and last data points from the string and comparing.

    Another VBA solution with less code could probably be done using 'regular expressions': see https://analystcave.com/excel-regex-tutorial/

    A formula solution needing several helper columns could also probably be done.

    Since I am neither a 'regular expression' nor 'formula' guy, I did what I know best.

    See the following code in the attached modified copy of your sample workbook:
    Please Login or Register  to view this content.
    To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

    To enable Macros and to Run Macros see the following:
    http://office.microsoft.com/en-us/ex...010031071.aspx
    http://office.microsoft.com/en-us/ex...010014113.aspx
    If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

    To access Visual Basic (VBA) see:
    http://www.ablebits.com/office-addin...a-macro-excel/
    a. Click on any cell in the Excel Spreadsheet (may not be needed).
    b. ALT-F11 to get to VBA.
    c. CTRL-R to get project explorer (if it isn't already showing).
    d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

    Lewis

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Find duplicates in a string

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Find duplicates in a string

    If you want to go the macro route, I believe this one will work for you...
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find duplicates with a string of text
    By chrisellis250 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-16-2019, 09:25 AM
  2. [SOLVED] Macro to find duplicates, concatenate cells, then delete old duplicates (2)
    By cny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2016, 06:05 AM
  3. Macro to find duplicates, concatenate cells, then delete old duplicates
    By givemepuppies in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-04-2016, 02:43 AM
  4. [SOLVED] Macro to find duplicates, concatenate Unique Values, then delete old duplicates
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2013, 04:32 PM
  5. [SOLVED] Find duplicates, concatenate into different column, sum and delete duplicates
    By rosannang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 11:23 AM
  6. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  7. Replies: 1
    Last Post: 07-13-2010, 10:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1