Offset match search for unsorted data

[KampKounslr]

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!

[KampKounslr]

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

[FDibbins]

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

[FDibbins]

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)

[FDibbins]

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)

[KampKounslr]

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!

[KampKounslr]

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?

[FlameRetired]

........ 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.

Please Login or Register  to view this content.

To locate say the 3rd one on the list:

Please Login or Register  to view this content.

To locate price with same:

Please Login or Register  to view this content.

To locate available date:

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.

[KampKounslr]

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!

[FDibbins]

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)

[FlameRetired]

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:

Please Login or Register  to view this content.

For the price:

Please Login or Register  to view this content.

For available date:

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

[FDibbins]

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