+ Reply to Thread
Results 1 to 12 of 12

Offset match search for unsorted data

  1. #1
    Registered User
    Join Date
    03-04-2015
    Location
    Tucson, AZ, USA
    MS-Off Ver
    2010
    Posts
    5

    Offset match search for unsorted data

    Hello all,

    I am creating an Excel spreadsheet for my work that, ideally, will go through unsorted data pasted into one sheet then output this data on the main sheet. See attached file.

    The In-House Price Sheet is where I am running into problems; my understanding with Offset Match is that the data needs to be sorted for an Offset Match search to work (and not produce duplicates in the following cells), unfortunately there is no real way for me to get it sorted aside from manually doing so (idea is to save time). Am I correct that an Offset Match function will not work here? Is there something that would work better or should I be looking at doing VB scripting to accomplish this? One way that someone had suggested involved using curly brackets '{}' and alt+enter but I don't really understand how that would work.

    I appreciate any help that you might be able to offer. Thanks in advance!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-04-2015
    Location
    Tucson, AZ, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Offset match search for unsorted data

    I also am hoping to have the data output in sorted manner on the In-House Price Sheet, in order of Date Available from soonest date to furthest out. If I cant do this then at the least I can get the data output

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Offset match search for unsorted data

    What answer where you expecting? 5/31/2015?

    You left out a 0 (see bolded)...
    =OFFSET('Paste Availability Here'!$B$1:$B$1000,MATCH(('Community Info'!$F$3 & "*"),'Paste Availability Here'!$B$1:$B$1000,0)-2, 8,1,1)

    Im sure there is a better way to do this, give me a few minutes to look
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Offset match search for unsorted data

    hmmm ok, see if this will get you wnat you want? (I have a feeling I have overlooked something though)
    =INDEX('Paste Availability Here'!J:J,MATCH('Community Info'!$F$3&"*",'Paste Availability Here'!B:B,0)-1)

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Offset match search for unsorted data

    Slight modification as I am understanding your file better...
    =INDEX('Paste Availability Here'!J:J,MATCH(--MID(B4,FIND(",",B4,1)+1,5)&"*",'Paste Availability Here'!B:B,0)-1)

    The bolded part will work for every1 "1st row" of each type you copy it to (D6, D12, D18 etc), but you will have to adjust the reference when you copy it within a type (D6 to D7, to D8 etc)

  6. #6
    Registered User
    Join Date
    03-04-2015
    Location
    Tucson, AZ, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Offset match search for unsorted data

    That's not quite what I was looking to do. I want the 3 'In-House Price Sheet' fields to be filled with the content on the 'Paste Availability Here' sheet, the problem that I am running into is that the current formula will find the first result of that floorplan but then I need the second field to find a different apartment of the same floorplan. Not sure how to get it to differentiate the results without the data already being sorted.

    I hope that helps clarify. Thank you!

  7. #7
    Registered User
    Join Date
    03-04-2015
    Location
    Tucson, AZ, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Offset match search for unsorted data

    I did try your second suggestion on adding the 0, that made it correctly pull the first result (thank you!) but I am still unsure how to get it to find the next result. I even tried using a large IF() statement but I cant get it to move onto the next set of matching data in the spreadsheet... trying to avoid using VB script (as I have never used it), but if there is no other way to go about it then does anyone have suggestions how I would write that?

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,617

    Re: Offset match search for unsorted data

    Quote Originally Posted by KampKounslr View Post
    ........ the problem that I am running into is that the current formula will find the first result of that floorplan but then I need the second field to find a different apartment of the same floorplan. Not sure how to get it to differentiate the results without the data already being sorted.
    Have an approach which will yield this array of matched rows for Community Info $F$3 (524 Sq Ft) against floor plans in column B of Paste Availability Here. There are six of them.

    Please Login or Register  to view this content.
    Designating which of the 524 sq ft floor plans you want can be done with SMALL. Here's the base formula for that.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To locate say the 3rd one on the list:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To locate price with same:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To locate available date:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    These are all array entered....committed by holding Ctrl + Shift while hitting Enter. I put these in row 8 under "Saguaro: 1x1, 524 Sq. Ft., W/D included" section to coincide with the 3rd matching item in column B of Paste Availability Here.

    File is attached.

    Doe this help? If yes will need to modify MATCH criteria. This one forces the 3 left most characters of Sq ft. Will tweak to complete this part if this much does the job so far.
    Attached Files Attached Files
    Last edited by FlameRetired; 03-05-2015 at 11:11 PM.

  9. #9
    Registered User
    Join Date
    03-04-2015
    Location
    Tucson, AZ, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Offset match search for unsorted data

    YOU ROCK!!! That is exactly what I needed it to do. If you dont mind me asking, what is the '--' in '--TRIM()' for? I have figured out what I need to do with the formula to move it around and this will work, just curious on the usage of --.

    Thanks a million FlameRetired!

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Offset match search for unsorted data

    Nice 1 FT

    the "--" is not actually on the TRIM, it is on the LEFT

    LEFT/MID/RIGHT a;; produce text, not a value/number.

    To conver a text number to a real number, there are a few different ways...
    1. *1
    2. +0
    3. -- (double negative), kinda the same as addong 0, it converts it to a negative, then converts that to a positive
    (yes, 2 wrongs (negatives) do make a right (positive) Bart lol)

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

    Re: Offset match search for unsorted data

    The "--" part coerces text values presented as numbers into their underlying numeric values. Since 524 is already a number in the Community Info sheet it can only match with the same number. The LEFT function removes the 3 left most characters from the located item in Paste Availability Here, but LEFT is a text function. The resulting number needs to be coerced into value 524 or else it is evaluated as 0. 0 is also a number just not what you want.

    I'm glad this looks promising. However these formulas aren't flexible. They could stand some "tuning".

    1.) LEFT extracts the first 3 left most characters described above. Not all Sq ft is 3 digits. That can be fixed.

    2.) Designation of the 3rd available 524 sq ft unit is hard coded. That could stand a helper cell.

    3.) The manner in which ROW is assigned is not the standard way. It works here because of serendipity. Everything here seems designed to start in row 1. I went with that. Wiser approach is in order should you insert new rows into the table. The ranges / row stop at row 200. If you add beyond that those will need to change to accommodate the new range. I / we strongly advise people to resist referencing whole columns / rows in their formulas. It consumes resources and slows workbooks down.

    I've been working on some of the details of those things pending your feedback.

    Here's the fix for the Sq ft extractions. I arbitrarily picked cell O2 In-House Price sheet to input the 1,2,3,...... replacing the hard coded number 3. For the unit number:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    For the price:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    For available date:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    All committed CSE of course.

    Updated workbook attached.

    PS. While I was busy typing I missed Ford's explanation on the "--". Should have refreshed 1st. LOL
    Attached Files Attached Files
    Last edited by FlameRetired; 03-06-2015 at 07:33 PM. Reason: clarity / typos

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Offset match search for unsorted data

    OK try this...

    I added a helper column to P-A-H sheet in S10 and copied this down...
    =INDEX('Paste Availability Here'!$J$10:$J$200,SMALL(IF('Paste Availability Here'!$S$10:$S$200=--MID(B$4,FIND(",",B$4,1)+1,5),ROW('Paste Availability Here'!$S$10:$S$200)-10),ROWS($A$1:A1)))

    then I used this ARRAY formula which will work when copied down in the 1st "set" (D6:D8)...
    =INDEX('Paste Availability Here'!$J$10:$J$200,SMALL(IF('Paste Availability Here'!$S$10:$S$200=--MID(B$4,FIND(",",B$4,1)+1,5),ROW('Paste Availability Here'!$S$10:$S$200)-10),ROWS($A$1:A1)))

    The boldedc parts will need to be re=set for the 1st row of each "set"

    (I could probably work a way to do that automatically, but the formula would also probably get really complex

+ 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] INDEX-MATCH to find next highest value (unsorted data)
    By Lady222 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2012, 10:15 AM
  2. Replies: 3
    Last Post: 07-14-2011, 11:18 PM
  3. Find closest match unsorted data
    By Yappa in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-11-2010, 01:30 PM
  4. search and match an unsorted list
    By Venka in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2008, 03:11 AM
  5. [SOLVED] Using INDEX w/MATCH to get data from unsorted source
    By Lynn Bales in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-22-2005, 11:05 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