Array formula needed. Example sheet

[unclejemima]

Hi guys. I've got a formula I'm looking for, I think an array formula is what I need. Hoping this can be done without VBA...

What I'm looking for is a formula for column O:Q that gives the results as shown. Basically, for any instance of Main PN (circled in black), it would show the sub PN (circled in green) and the qty for the parts in columns C:M. You can see the groups C:D, F:G, I:J, L:M with PN and Qty.

Its kinda hard to explain but if you look at the screenshot you should be able to figure it out lol.

Thank you!!

 

[vletm]

unclejemima
As You're a new member, You've just read Forum Rules..

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Could You refresh Your memory and reread especially
How to get the Best Results at Chandoo.org -chapter.
There are many good hints and
You'll notice soon - what is missing from Your the 1st post?

 

[unclejemima]

Thanks I meant to attach the sample file but forgot. My bad

See attached. I tried to edit the first post but can't seem to figure out how.

Thank you all!

 

[Nebu]

Hi:

May be this using Power Query.

Thanks

 

[Peter Bartholomew]

I wouldn't describe an array solution as simple. I have used named formulas to hold parts of a nested formula and have access to dynamic arrays including the SEQUENCE and FILTER functions. Given that:


generates a dynamic array that only differs from your results in terms of its sort order.


(411 rows)

 

[unclejemima]

Thanks guys super appreciate it. Nebu, your power query looks good. Thank you!

Peter, any chance you can upload the sample sheet with your formula? I see the screenshot of your formula's but I'm not 100% sure how to apply them.

Thank you

 

[Peter Bartholomew]

This will only work is you have an appropriate version of Office 365.
Otherwise you need to create the sequence 'k' using row numbers. The filter would be done by returning an array of row numbers with missing values where Qty=0. The SMALL function would compress the list but it will not be good fun!

 

[unclejemima]

Yea my excel version was not so happy with that file lol. I'm using Excel 2013. Should have mentioned this.

So is it possible to build an array formula using SMALL function then? Or would the formula be as long as the bible?

If it is technically possible, I'd love to see an example sheet!

Thank you so kindly. This forum is awesome!

 

[Peter Bartholomew]

This may work. The first challenge is whether the Office 365 file with dynamic arrays converts correctly to Excel 2016.
Even if it does, another challenge might be whether you can live with the way I program Excel. It may not be unique but it is nowhere near a beaten track.

 

[unclejemima]

Peter. You are awesome. That appears to work great! Thank you

 

[unclejemima]

Am I supposed to mark the thread as "solved" when it's good?

 

[Peter Bartholomew]

It is good to know that it worked for you.

The traditional Excel environment makes handling array formulas a somewhat heavy and inflexible process that masks their inherent simplicity. Modern dynamic arrays are far more fluid and pleasant to use (for your problem I have only one cell that contains a formula!) On my laptop the transformation of data took less than 0.2sec which should be acceptable but, if the responsiveness of the workbook becomes sluggish, the calculation of the sequence numbers 'k' can be changed to make it non-volatile.