Complex search problem using arrays

[camylarde]

Hello,

I have a dynamic list of data that contains dynamically sized blocks that i need to search within.

Its a list of orders, and comprises of all the items purchased within the orders. I am able to find the start of each particular block, and also the end of it. In an order, it is possible (and that is the core of my problem) that one particular product may appear in the order(=block of data) multiple times. And I need an automated function that would ceck all the occurences of that product and compare its units of measure if they match each other.

I've gone as far as array formula:

{=IF(OFFSET(SOURCE!$C$1,RESULTS!B11-1,0,RESULTS!C11-RESULTS!B11+1,1)=SOURCE!C11,
,OFFSET(SOURCE!$C$1,RESULTS!B11-1,0,RESULTS!C11-RESULTS!B11+1,1),"")}

but the excel does not seem to comply with this. I would use that input as an array for lookup function and then play with the results, but this seems to be impossible now.

A small comment to understand what I am doing (without the real table, it might be impossible):
The OFFSET part generates correctly the block array that the particular order occupies within the list. It uses separately computed first and last line of that block present in columns RESULTS!B and RESULTS!C.

By using the array type of formula I hope to create something like:

{If (A1:A5=A1,A1:A5,"")}

and hope to get results like {A1,0,0,A4,A5} that I would then that I would use into some other construction. However this formula is not working. Using Evaluate Formula I am getting the first part (of my original formula) as an array:

{If ({true|false|false|true|true},A1:A5,"")}

But the evaluation to TRUE or false does not generate an array.

Oh, and I'm using Excel 2003.

 

[the_net_2.0]

hello camy,

I'm not big with Excel, but I would like to make a suggestion to you. First of all, the use of the 'array' word is a little bit confusing. The proper term for a 'block' of cells in Excel I believe is range. I don't see any arrays in your code examples. So reading the description was confusing in that respect.

I also think it would benefit the forum if you could post an example 'sheet' of data so people could get an accurate depiction of what you're dealing with. I think you're right...without a picture this would take quite a few posts to hash out a solution.

 

[camylarde]

hello camy,

I'm not big with Excel, but I would like to make a suggestion to you. First of all, the use of the 'array' word is a little bit confusing. The proper term for a 'block' of cells in Excel I believe is range. I don't see any arrays in your code examples. So reading the description was confusing in that respect.

I also think it would benefit the forum if you could post an example 'sheet' of data so people could get an accurate depiction of what you're dealing with. I think you're right...without a picture this would take quite a few posts to hash out a solution.

Youre quite right, altough i felt the block is just a part of the range occupied by the entire list, it still is a range. Anyway, I've solved the problem yesterday and the final formula should look like this:

{=AND(IF(SOURCE!C11=OFFSET(SOURCE!$C$1,RESULTS!B11-1,0,RESULTS!C11-RESULTS!B11+1,1),
    ,OFFSET(SOURCE!$F$1,RESULTS!B11-1,0,RESULTS!C11-RESULTS!B11+1,1),SOURCE!F11)=SOURCE!F11)}

My previous code lacked the AND(if(... part that actually produced a single result of the array contained within.

Lets break up here what this code does.

Dynamic list in the sheet SOURCE contains order numbers in column A. Products ordered within that order are present in column C. Units of Measure present in column F.

On the sheet Results, columns B and C do contain row number of the start and the end of the range containing products from the same order.

This formula (Entered as an array formula - using ctrl+shift+enter) does search for every product line its associated range and searches for duplicates within that order and checks whether the units of measure are same across all the occurences of the particular product.
(don't ask me for why I do this, its actually quite a silly reason)

Breakup of the code (hoping that you can search excel help to get familiar with the functions yourself):

OFFSET(SOURCE!$C$1,RESULTS!B11-1,0,RESULTS!C11-RESULTS!B11+1,1)

Does generate the particular range that the order occupies in the list. Well, the column C range that is. Example can be, say : C455:C457. The other offset generates the same, except in the column F.

IF(SOURCE!C11=C455:C457,F455:F457,SOURCE!F11)

Now this part is much more readable, basically simple IF except it is an array formula, so the formula is rather looking like this:

IF[I](Product A I am comparing against={Product A|Product B|Product A}[/I],
   ,{[I]Product A UOM|Product B UOM|Product A UOM[/I]},[I]Product A UOM[/I])

Note the little trick I've used here to obtain the result needed. If a product is evaluated as different than the product I compare against, the formula does give the positive answer (from the FALSE part of the IF). Here, the positive answer is actually correct as we do not care what UOM do different products have on their respective lines, we care only about the particular UOM's of the selected products.

So the

{AND(IF( ... )=SOURCE!F11)}

code would evaluate liek this:
{true because UOM of the product A on the line 455 is same as UOM we compare against |
| true because we do not care about the product B and we've put the right answer manually to trick the formula |
| true because UOM of the product A on the line 457 is same as UOM we compare against }

And here we go. No matter how complex the list is, we can now compare values in a column based on the data in a different column. No list order is required, we only need to know the range where to look.

P.S. Somebody please help me to find the proper name for this exercise so that it is properly googleable for others to use.

P.S.2 Array formulas - you need to enter them not by pressing ENTER, but by pressing CTRL+SHIFT+ENTER

Cheers, Camylarde

 

[the_net_2.0]

good for you! good luck