+ Reply to Thread
Results 1 to 25 of 25

String Comparison

  1. #1
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    String Comparison

    How can I compare different parts of strings to similar parts in a different cell?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: String Comparison

    You can do this many ways, but how about providing some examples.
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    You can make a good start by reading the yellow banner (Top of page) and attaching a sample sheet, showing raw data and expected output (manually calculated).
    Glenn



  4. #4
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    Unfortunately I can't post links yet because i only joined today

    however, one cell would contain the following as an example

    1 14GA WHT

    or

    2 12GA RED

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: String Comparison

    What are you comparing? Is see the examples, but what do they mean to us? Even with a low post count, you can still attach a sample worksheet.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    You CAN post a file, you CAN NOT post links. Re-read the yellow banner and post a file with about 10 rows of data, showing what you want.

  7. #7
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    Did this work?
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    Do you really want the results concatenated into one cell (.xlsm, using a UDF is best solution)?

    If you are not allowed to use a macro, or do not REQUIRE the results to be in one cell, can they be in separate cells in the same row?

  9. #9
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    I would like each wire AWG and color to be in its own cell and sorted vertically in a column. And I'm trying to get the 'layup', ex:1-2-3, to be in a separate column but next to the corresponding wire. If that's not possible, putting the layup in individual cells in the same row after the wire would also work and I could work with that.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    Mmmm. Just noticed a complication:

    is 2 14GA RED the same as 1 14GA RED??

  11. #11
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    yes, they are the same wire, but at two different points.
    Each point has one end of the wire, so there is point 1, which shows 14GA RED going to point 2.
    Point 2 shows 14GA RED going to point 1.

    Hence the form Column 1 Column 2
    [Current point #] [previous point #][AWG][color]
    This is how we get the layup. it would be point 1->point 2

    Does that help make more sense?

    Note: some wires hit more than 2 points. EX: 1-2-3

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    Take a look at this....

    Happy to explain if it is working... Not convinced, yet!!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    It looks good and like something I can really work with!

    In the future it's highly likely I'll be changing wire points and AWG periodically, would this reflect any changes well on a potentially expanding table?

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    It should be expandable without any issues. Just increase the ranges to cover the data range.

    The ONLY thing to bear in mind is that the formula in D13 does not like blanks.

    To make that formula fully flexible, you can use a dynamic named range to set the range automatically. Do you know how to do that? if so, fine, if not I'll do it for you.

  15. #15
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    I'm afraid I don't know how to do it, I don't have much experience with large algorithms like these in excel.

    I'd very much appreciate it.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    The Named range (can be found in the Name manager... CTRL-F3) is called Partials and is:

    =testLink!$B$13:INDEX(testLink!$B:$B,MATCH("zzz",testLink!$B:$B))

    It starts from B13 and will run down column B until it finds the last non-blank cell, and passes that range to the formula in D13, which now looks like:


    =IFERROR(INDEX(Partials,MATCH(0,INDEX(COUNTIF($D$12:D12,Partials),0,0),0)),"")

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    Just one more thing, When I open the file now, some parts disappear, do I need to just copy and paste the full code to the parts below it?
    Attached Files Attached Files

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    "some parts disappear" means nothing. Be specific. I did delete a pile of blank rows from your sample, so I could see results and raw data at the same time. But that thas been like that since the first time i posted a sheet.

  19. #19
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    sorry, not being specific is my bad habit.

    in the full code, partial code, and uniques areas, the lines below row 13 became blank when I enabled editing.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    i can not reproduce that. Did you look at the correct file? I checked the file that you attached at Post 17 and it is FINE.


    First off. Ensure that NO copies of the file are open.

    Then, re-open my file (Post 16). Does the data below row 13 still "vanish"? If so, select cell A14. Is there still a formula there? Copy paste into the forum.

  21. #21
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    This is the code in cell A13

    =IFERROR(INDEX($1:$1048576,AGGREGATE(15,6,ROW($B$2:$P$9)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A12,$B$2:$P$9)=0)),1),ROUND(10^6*MOD(AGGREGATE(15,6,ROW($B$2:$P$9)+(COLUMN($B$2:$P$9)*10^-6)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A12,$B$2:$P$9)=0)),1),1),0)),"")

    This is the code in cell A14 -> A33

    =IFERROR(_xlfn.SINGLE(INDEX($1:$1048576,AGGREGATE(15,6,ROW($B$2:$P$9)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A13,$B$2:$P$9)=0)),1),ROUND(10^6*MOD(AGGREGATE(15,6,ROW($B$2:$P$9)+(COLUMN($B$2:$P$9)*10^-6)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A13,$B$2:$P$9)=0)),1),1),0))),"")

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    What version of Excel are you using?

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    OK. Got it. Bl@@dy Microsoft and their implicit intersection @.

    Just copy the formula in A13 down the column and (with a bit of luck) all will be well.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    01-14-2021
    Location
    United States
    MS-Off Ver
    2016
    Posts
    11

    Re: String Comparison

    All works well!

    Thank you very much!

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,021

    Re: String Comparison

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] String Comparison Not Working
    By js0873 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-02-2017, 10:11 AM
  2. case sensitive string comparison
    By Gourav in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2014, 02:31 PM
  3. Excel 2007 : text string comparison
    By pedro4545 in forum Excel General
    Replies: 0
    Last Post: 10-21-2011, 11:33 AM
  4. String Comparison
    By tactical in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-13-2011, 01:15 PM
  5. String comparison
    By HurricaneDan in forum Excel General
    Replies: 4
    Last Post: 11-08-2006, 02:05 PM
  6. [SOLVED] comparison string VBA excell
    By stats in forum Excel General
    Replies: 5
    Last Post: 03-16-2006, 06:15 PM
  7. [SOLVED] String Comparison
    By Purnima in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-08-2005, 08:06 AM

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