Adapt this array formula to sort if possible

**sipa** · 10-29-2015, 07:31 AM

This array formula copies data from a sheet "Items" with 11 dynamic ranges, to another sheet "Costs" and places all of the data from the eleven different dynamic ranges (columns) in a single column, in the same individual range order without spaces but unsorted.

Any update to the "items" sheet is reflected in the "Costs" sheet but in the range order.

=IFERROR(INDIRECT("Items!"&TEXT(SMALL(IF(Items!$A$2:$K$60<>"",ROW(Items!$A$2:$K$60)*10^4+COLUMN(Items!$A$2:$K$60)),ROWS($A$1:A1)),"R0000C0000"),0),"")

Can this formula be adapted to sort the copied data A to Z, if so how would you do it.

Many thanks

**XOR LX** · 10-29-2015, 07:52 AM

Hi.

It would seem then that non-uniqueness is not an issue for your set-up.

Array formula**:

=IFERROR(INDIRECT("Items!"&TEXT(MAX(IF(COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60)=SMALL(IF(Items!$A$2:$K$60<>"",COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60)),ROWS($1:1)),10^5*ROW(Items!$A$2:$K$60)+COLUMN(Items!$A$2:$K$60))),"R0C00000"),0),"")

Note that 10^4 would not be sufficient for all ranges within a standard Excel workbook, though 10^5 would.

Regards

**sipa** · 10-29-2015, 08:26 AM

Thanks for helping.

Your adapted formula, almost works perfectly. Just a couple of issues, the first item returned is a zero. and it doesn't handle additions to the items list correctly.

I've attached a sample sheet for you.

sorry, in the sample file the correction should read, A to Z Apple & Blackberry Pie then Apples.

with regards

**XOR LX** · 10-29-2015, 10:01 AM

Apologies. Missed a clause:

=IFERROR(INDIRECT("Items!"&TEXT(MAX(IF(Items!$A$2:$K$60<>"",IF(COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60)=SMALL(IF(Items!$A$2:$K$60<>"",COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60)),ROWS($1:1)),10^5*ROW(Items!$A$2:$K$60)+COLUMN(Items!$A$2:$K$60)))),"R0C00000"),0),"")

though I might disagree (and Excel certainly agrees with me!) that "Apples" precedes "Apple & Blackberry Pie" alphabetically.

Regards

**sipa** · 10-29-2015, 10:23 AM

Crikey !

No apology necessary. I'm just very grateful for your help.
Works perfectly now, thank you.
Regards

**XOR LX** · 10-29-2015, 10:34 AM

You're welcome!

Cheers

**sipa** · 10-29-2015, 10:59 AM

Having got that bit working, would it be possible to exclude certain items from the copy based on a list of locations, say freezer 1, freezer 3 etc ?

**XOR LX** · 10-29-2015, 11:18 AM

Can you clarify? Where is this list of locations? In the actual worksheet? A Defined Name? Or to be entered in-formula?

Regards

**sipa** · 10-29-2015, 11:35 AM

A dynamic list in sheet Data, f2:f20

thanks

**XOR LX** · 10-29-2015, 11:55 AM

If the list of locations is in F2:F20, how do I relate them to the Items?

Regards

**sipa** · 10-29-2015, 12:26 PM

Sorry Brain freeze.... Lets reset.

In my real workbook there is a sheet "Freezer Stocks" this holds a list of items in B4:b200 which is dynamic and random order . there is also a list of locations held in n4:n200 against each item, although some can be blank.

hope that helps

**XOR LX** · 10-29-2015, 01:35 PM

Thanks. And which of those do you wish to exclude?

Perhaps you could re-post your workbook with this in mind to make things a touch easier?

Regards

**sipa** · 10-29-2015, 04:03 PM

Here is a new sample file, additional sheets.

"freezer stocks" holds a random list of items and corresponding location.

"Exclusions" holds a list of locations where items should be excluded from the sorted list.

Thanks

**XOR LX** · 11-02-2015, 05:12 AM

Ok, but things are getting a bit resource-heavy now...

=IFERROR(INDIRECT("Items!"&TEXT(MAX(IF(Items!$A$2:$K$60<>"",IF(1-ISNUMBER(MATCH(INDEX('Freezer stocks'!$N$3:$N$115,N(IF(1,MATCH(Items!$A$2:$K$60,'Freezer stocks'!$B$3:$B$115,0)))),Exclusions!$F$2:$F$5,0)),IF(COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60)=SMALL(IF(Items!$A$2:$K$60<>"",IF(1-ISNUMBER(MATCH(INDEX('Freezer stocks'!$N$3:$N$115,N(IF(1,MATCH(Items!$A$2:$K$60,'Freezer stocks'!$B$3:$B$115,0)))),Exclusions!$F$2:$F$5,0)),COUNTIF(Items!$A$2:$K$60,"<"&Items!$A$2:$K$60))),ROWS($1:1)),10^5*ROW(Items!$A$2:$K$60)+COLUMN(Items!$A$2:$K$60))))),"R0C00000"),0),"")

Regards

**sipa** · 11-02-2015, 06:48 AM

Xor LX

Absolutely brilliant, your formula is working well in the sample sheet, now to test in the real sheet. I'll post back with any issues, but can't see there being any.

As you say it's getting resource heavy, It does take a bit of time to recalculate, but that's not really an issue as the exclusions don't change very often.

Thanks again & regards

**sipa** · 11-03-2015, 01:03 PM

All good, thanks

**XOR LX** · 11-03-2015, 06:50 PM

Great!

Cheers.

Adapt this array formula to sort if possible

LinkBack

Thread Tools

Rate This Thread

Display

Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Re: Adapt this array formula to sort if possible

Thread Information

Users Browsing this Thread

Similar Threads

Need to auto sort tables or adapt formulas to fill cells based on values.

Sort data using array formula

Adapt Quicksort to Sort Two Arrays Simultaneously

[SOLVED] How to adapt 'sort' code to unprotect/protect worksheet

Thinking maybe some sort of array formula?

Sort a Array Formula

how can i adapt this formula?

[SOLVED] How to adapt a formula?

Bookmarks

Bookmarks

Posting Permissions