+ Reply to Thread
Results 1 to 5 of 5

Altering a formula to sort two additional columns of text

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    New Bern North Carolina USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Altering a formula to sort two additional columns of text

    Thanks for taking the time to read this long post. Several months ago I posted a problem regarding searching four columns in order to return a specific text string. There were several great responses and one in particular that worked very well, however I have not been able to successfully alter the formula to search additional columns (up to 10 columns). I really have tried to understand each of the nine different nested functions within the two different formulas, but I keep getting an error message after every attempt to add any additional columns. I have posted the original spreadsheet that contains the two formulas in the "helper columns" I have also added two additional columns of data to be sorted if someone feels adventurous enough to give it a try by altering the two formulas. The formula posted below really is brilliant in its execution. It works flawlessly when extracting user defined text and numbers from four columns, and anyone who is trying to extract specific data from a table will find it a very useful tool. (Special thanks to user WHER for your help).

    I have posted the location of the original thread and the two formulas below used to sort the four columns. The crux of the original problem was that there were two different types of data being searched in the four columns. Both types are color coded black and red. The first formula in "Helper Column K" searches for one type of data , while the second formula in "Helper Column L" searches all four columns if the specific type of data is not found in columns "B" and "D" by the first formula. In the formulas shown below, columns $B$2:$B$18 and $D$2:$D$18 in the first formula are being searched to find the specific text shown in cell "I18". In the attached example, I want to add columns "F" and "G" to be searched as well. Any help is greatly appreciated, and let me give a special thanks to the people on this forum. Although I am new to Excel, my skills have improved dramatically these past months, mainly by reading the posts on this forum whenever I encounter a problem.


    =IFERROR(SUBSTITUTE(IFERROR(INDEX($B$2:$B$18,MAX(IF(ISNUMBER(FIND(I18,$B$2:$B$18))*(LEN(I18)<LEN($B$2:$B$18))=1,ROW($B$2:$B$18),""))-1),INDEX($D$2:$D$18,MAX(IF(ISNUMBER(FIND(I18,$D$2:$D$18))*(LEN(I18)<LEN($D$2:$D$18))=1,ROW($D$2:$D$18),""))-1)),I18,""),"")

    =IFERROR(IFERROR(INDEX($C$2:$C$18,MATCH(I18,$B$2:$B$18,0)),INDEX($E$2:$E$18,MATCH(I18,$D$2:$D$18,0))),"")


    My original post and question is provided below.
    http://www.excelforum.com/excel-gene...o-a-table.html
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Altering a formula to sort two additional columns of text

    i don't know if i can actually help you, but can i make a suggestion? Name your ranges. It will help you to write long formulas. instead of $B$2:$B$18 you could name it ColB, it is shorter to write and when you are writing out the formula it will give you a list of named ranges as you type (kinda like google instant where it guesses what you will be searching for). Also I would suggest to keep your data better organized. in column B you have Addresses, MLS#s, Pin# etc. it is very confusing and make you require complex and extremely long formulas to get back the data that you requrie.

    It would be alot easier to have a database layout where MLS# is in its own column, Address has its own, address2 has its own then use vlookups using a dropdown box that contains specific information like listing number, name etc.

    let me know where you get the data, how you actually use this workbook and I will try to help design this better so you don't need to have huge formulas, and helper columns
    (there is VBA too which can be used for searching, very helpful)

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Altering a formula to sort two additional columns of text

    k, read the original post, never mind about reformatting the data

  4. #4
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Altering a formula to sort two additional columns of text

    can i ask what site you are pulling the data from, i would like to take a look to see if there might be a simpler way

  5. #5
    Registered User
    Join Date
    11-07-2012
    Location
    New Bern North Carolina USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Altering a formula to sort two additional columns of text

    Hi Scott, and thanks for the suggestions. I am actually trying to do what you are suggesting by pulling the specific information from a saved webpage. The information is taken from a Multiple Listing Service website. the actual information that I'm trying to extract and put into a database is contained in an iframe on the webpage so I have to select everything on the frame, copy, and then paste it into excel. Is there a better way to scrape data from a site with frames to populate a database? I have attached the excel sheet with the information from the webpage pasted in. I would appreciate any ideas that you might have.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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