+ Reply to Thread
Results 1 to 10 of 10

Messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

  1. #1
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Hello all, this is my first post on this forum!

    I am working on research into fungal rarity, hoping soon to publish my first paper.

    I have data that looks like this:

    Leptosphaeria agnita
    NZ3513
    NZ81
    SD4774
    SD4777
    SE2773
    SE3742
    SE5612
    SE8584
    SO5615
    SO5911
    SO5914
    SO5918
    SO6018
    SO6414
    SO8913
    SO8914
    SP8151
    SP863538

    ..and I need it to look like this:

    Leptosphaeria agnita
    NZ35
    NZ81
    SD47
    SD47
    SE27
    SE37
    SE56
    SE85
    SO56
    SO59
    SO59
    SO59
    SO60
    SO64
    SO89
    SO89
    SP81
    SP86


    I have run into a problem with the grid reference column of my species list. I need unique 10km square grid refs. A 10km grid ref is two letters followed by two numbers. More numbers means a higher accuracy, for example two letters with six numbers means an accuracy of 100m. The issue is that I have a set of 'unique grid refs, but when they are trimmed to the 10km square standard, you can see there are many duplicates!

    So does anyone know how I can efficiently trim my grid ref column down to the two letter two number format which I need?

    All the best!

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,000

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    This will get you to your output but I don't understand your question about uniqueness. You will not get unique values doing this

    =LEFT(A1,4)
    Happy with my advice? Click on the * reputation button below

  3. #3
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Oh fantastic! you are amazing! I have added you some reputation.

    Dont worry, I am saying that previously unique refs, ie SD4774, SD4777, are no longer unique when in the 4 digit format ie SD47, SD47.
    Now you have shown me how to do this, I can apply it to all and then perform remove duplicates once more!

    There is one more thing I need to get over in this case and will post it in a minute just need a cup of tea, but you have just solved my major issue.

  4. #4
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    the next smaller issue is here:

    Leptosphaeria maculans NZ28
    Leptosphaeria maculans NZ10
    Leptosphaeria maculans SD47
    Leptosphaeria maculans SU81
    Leptosphaeria marina TF56
    Leptosphaeria modesta NM
    Leptosphaeria modesta NR60
    Leptosphaeria modesta NZ34
    Leptosphaeria modesta SE29
    Leptosphaeria octophragmia var. major SX25
    Leptosphaeria ogilviensis SP10
    Leptosphaeria ogilviensis SE88
    Leptosphaeria ogilviensis SM70
    Leptosphaeria ogilviensis TQ12
    Leptosphaeria ogilviensis SO55

    Now I have species and the 4 digit reference for most of them, BUT, there is the odd example which only has two digits (two letters, in this example 'NM')
    Can you think of a way to remove these, leaving me with only 4 digit examples?

    Thanks!

    Edit: the species being in column A, the 4 digit grid refs being in column B.
    Last edited by zvba010; 10-12-2016 at 10:26 PM. Reason: more info

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Hello zvba010,

    Crooza seems to be logged off at the moment.

    So with data in columns A:B try this as a 'helper' column in C1 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in E1 across column F and down until you get blanks use this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It is done for you in the attached.
    Attached Files Attached Files
    Dave

  6. #6
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    That looks great, I took a look and the function basically searches for the presence of numbers in the cell I see, very clever!
    I am very tired now but will try it on my raw data tomorrow, thanks very much!
    I have given you some reputation.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Glad it works. Thank you for the feedback and rep.

  8. #8
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Hello again

    Unfortunately when I apply this function: =IF(ISNUMBER(LOOKUP(1E+306,FIND({0,1,2,3,4,5,6,7,8,9},B1))),ROWS($1:1),"")

    ..to anything over about 20,000 results, excel crashes. I think its because I have about 750,000 results and it puts larger and larger numbers in the cells as its goes along.

    Might there be a countif function I can use along the lines of =COUNTIF(B2, {0,1,2,3,4,5,6,7,8,9,}) ???
    That countif doesnt work, but if I can get it to put a 1 when the cell contains numbers, and a 0 if it doesn't, then I can replace zeros with blank, highlight all blanks, delete all rows.. I know for sure my excel can handle countif functions on a large scale

    Thanks!

    Edit: Also tried =COUNTIF(A2, "*>0") and it did not work \:

    Edit: I did it! Using:

    =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0

    Which puts TRUE or FALSE
    then copied entire row, pasted values only
    Repllace All FALSE with nothing
    Select all blanks, delete, delete row!

    Great!
    Last edited by zvba010; 10-13-2016 at 07:35 PM.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    The COUNTIF should work but you will need a wildcard since the strings contain text "numbers". It will also need to be summed since there will be an array of returns.

    This works at my end.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Unfortunately when I apply this function: =IF(ISNUMBER(LOOKUP(1E+306,FIND({0,1,2,3,4,5,6,7,8,9},B1))),ROWS($1:1),"")

    ..to anything over about 20,000 results, excel crashes. I think its because I have about 750,000 results and it puts larger and larger numbers in the cells as its goes along.
    I was going to suggest another alternative to my previous. That LOOKUP was unnecessarily complicated. My apologies.

    Also the ROWS($1:1) will slow down somewhere between 40K and 50K cells.

    I've found this to do noticeably better on large data sets.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The $1:$1 must be the row number of the first cell the formula is entered into.

  10. #10
    Registered User
    Join Date
    10-12-2016
    Location
    London, England
    MS-Off Ver
    MIcrosoft Excel for Mac Version 16.33
    Posts
    17

    Re: Hello I am new! Issue with messy grid refs (tw3456, tw34, tw386956 > tw34, tw34, tw38)

    Thanks Flame for your amendments but I managed to do it using:

    =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0

    Which puts TRUE or FALSE
    then copied entire row, pasted values only
    Replace All FALSE with nothing
    Select all blanks, delete, delete entire row!
    Just waiting while it deletes now..
    Really need a faster machine for my research haha

    Thanks!

+ 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. Replies: 1
    Last Post: 10-29-2013, 02:11 AM
  2. Replies: 11
    Last Post: 11-09-2012, 09:31 PM
  3. Replies: 2
    Last Post: 12-15-2011, 04:14 AM
  4. #REFs eventually appear -- why?
    By Philster in forum Excel General
    Replies: 1
    Last Post: 03-12-2010, 02:34 PM
  5. Messy tab characters
    By Gargoyl in forum Excel General
    Replies: 2
    Last Post: 04-19-2006, 06:15 PM
  6. messy data
    By Adam in forum Excel General
    Replies: 4
    Last Post: 01-24-2006, 11:55 AM
  7. I need to convert grid.doc to an Excel Grid.
    By suavejohn in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-01-2005, 02:05 PM
  8. Sheet Refs in Add-In
    By Ian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-28-2005, 03:06 PM

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