Finding successive matches & extracting them to another location

[Gaurav Vohra]

Dear All, I've got two tables :
1) Table1: PRODUCTION DATA
2) Table2: ALLOCATION DATA
We want to extract the quantity of each part produced in each batch & place it another table in successive columns.
I tried using the formula mentioned below but it is returning ZERO value.
=AGGREGATE(15,6,(($B$4:$D$11=$J4)*($E$4:$G$11)),COLUMNS($K4:K4))
There is an attached file reflecting clearly the desired result.

 

[Gaurav Vohra]

Just for an update, I now understand why my formula is returning a zero value.
The question still stands.

 

[Peter Bartholomew]

As it stands each row is an array formula requiring committal with Ctrl+Shift+Enter'
= IFERROR( LARGE( IF(PartTbl=PartNo, QtyTbl), {1,2,3} ), "" )
[Note that the name 'PartNo' is a relative reference to a single value]

 

[bosco_yip]

In K4, formula copied across and down :

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($4:$7)/1%+{5,6,7}/($B$4:$D$7=$J4),COLUMN(A$1)),"R0C00"),0),"")

Regards
Bosco

 

[Gaurav Vohra]

Dear Peter & Bosco, thank you both for your help.
In formula provided by Peter qty.s for A are coming in front of A, same with B&C but their order is not correct.
In formula provided by Bosco, both the issues viz. match & order of match is OK.
Kindly give me some time to apply it to the actual larger database.
Thank you again.

 

[Peter Bartholomew]

Sorry about that, I had failed to recognise that there was an order so chose large to small.
I now assume it is in batch order and that a part number would occur only once within a given batch.

It is water under the bridge now but, in an idle moment, it looked at how I could best dispense with the need for CSE. I came up with the strategy of turning the array constant {1;2;3} into a relative reference 'k' and using a further named formula for the array calculation.
[CSE is needed to override the grid behaviour of replacing arrays with single values by using implicit intersection. A formula referred to by name is not subject to this process]

The worksheet formula is then
= IFERROR( LARGE( Qty, k ), "" )
where 'Qty' is defined by the formula
=IF(PartTbl=PartNo, QtyTbl)
Back to the drawing board!

 

[Peter Bartholomew]

Another go.
This one uses MMULT to add the quantity across the 3 columns to give a single column vector over batch.
=MMULT( IF(PartTbl=PartNo, QtyTbl, 0 ), {1;1;1} )
BatchNo is a compressed list of the batches in which the part was produced
= SMALL( IF(Qty,Batch), k )
The worksheet formula
= IFERROR( LOOKUP( BatchNo, Batch, Qty ), "" )
looks up the production quantity corresponding to the selected batch numbers.

The challenge for me is to produce solutions using names and array formulas, which is a very restrictive environment compared with the free for all of single-cell direct references. The idea is that anything meaningful can be done this way and only meaningless calculations are eliminated. That is not something that can not be proved but an increasing body of experience suggests it might be correct. The next challenge could be to open your problem on my other computer and see whether the solution can be improved using 'modern dynamic arrays'!

 

[deciog]

Gaurav

In cell J4 would put this formula copied down, to show only

=INDIRECT(TEXT(MIN(IF((COUNTIF(J$3:J3,$B$4:$D$11)=0)*ISTEXT($B$4:$D$11),ROW($4:$11)/1%+{2\3\4},4^8)),"R0C00"),)&""

It is matrix typed CTRL + SHIFT + ENTER

Decio

 

[Peter Bartholomew]

Just checked, dynamic arrays do help a bit. The quantity 'Qty' of a given part produced over each batch remains
= MMULT( IF(PartTbl=PartNo, QtyTbl, 0 ), {1;1;1} )
but then there is no need to mess with LARGE/SMALL and the sequence number k. Instead the required result is given by
= TRANSPOSE( FILTER( Qty, Qty,"") )
Each row is a spilt range so there are only formulas in the first column of the output.



I have attached the file just incase anyone wants to look at it using Office365 insider.
Don't know why but sometimes I get the feeling that my solutions are not totally mainstream

 

[Gaurav Vohra]

Hello Peter, Thanks again for your effort. Your "PB3" solution is working perfectly. Will check it on complete data.
Your PB2 solution is not delivering the correct order of matches, refer attached file

Deciog, thanks for your reply. You're using both styles of reference(??), should not the starting cell be J5. Also, excel is not identifying it as a formula. I think some issue with bracket placement. Can you clarify so that I can test it? As I'm totally unaware of your logic behind the formula.

Prior to posting this question on the forum, I tried following:
=INDEX($E$4:$G$11,AGGREGATE(15,6,(ROW($B$4:$D$11)-ROW($B$4)+1)/($B$4:$D$11=$O4),1),AGGREGATE(15,6,IF(INDEX($B$4:$D$11,AGGREGATE(15,6,(ROW($B$4:$D$11)-ROW($B$4)+1)/($B$4:$D$11=$O4),1),0)=$O4,COLUMN($B$3:$D$3)-COLUMN($B$3)+1),1))

My approach was delivering the wrong column no. & obviously very long.

Another query was: Since from all the possible solutions posted, everybody has used R1C1 cell reference style. It seems to be more flexible in terms of delivering numbers to both rows & columns.

 

[Gaurav Vohra]

Also one question regarding the use of Indirect function.
Eventually, my data will grow to say 20 columns & thousands of rows.
Will using indirect be feasible then?

 

[Gaurav Vohra]

Sorry for multiple posts, but wanted to clarify one point, I tried following:
=INDEX($E$4:$G$11,AGGREGATE(15,6,(ROW($B$4:$D$11)-ROW($B$4)+1)/($B$4:$D$11=$O4),1),AGGREGATE(15,6,IF(INDEX($B$4:$D$11,AGGREGATE(15,6,(ROW($B$4:$D$11)-ROW($B$4)+1)/($B$4:$D$11=$O4),1),0)=$O4,COLUMN($B$3:$D$3)-COLUMN($B$3)+1),1))

My approach was delivering the wrong column no. & obviously very long.

In this case major issue for getting correct column no. was that the array that I was getting for column no.s from INDEX((COLUMN($B$4:$D$11)-COLUMN($B$4)+1)/($B$4:$D$11=$J4), instead of getting 1st smallest, 2nd smallest value & so on. I needed 1st non zero value, 2nd non-zerovalue & so on.
Is there a way to do this?

 

[deciog]

Sorry for the delay, reason time zone

Put the formula in the solution of colleague Peter

Check it out, I hope I have helped

Decio

 

[Gaurav Vohra]

Thank you.

 

[Peter Bartholomew]

@deciog
Wow, that is amazing! The number format "R0C00" came as a surprise and even /1% required a second take.
Makes my solutions look prosaic.

 

[Gaurav Vohra]

In solution by Deciog, his formula is not returning any part no. in column J.

 

[Gaurav Vohra]

Any comment on how to resolve the issue of finding 1st,2nd,---- non zero value in a linear array?
The contentious issue in my solution.

 

[Peter Bartholomew]

The usual is along the lines of
= IFERROR( INDEX( array, SMALL(IF(array,k), k) ), "" )
where 'k' is a helper range containing a sequence of record numbers.
I think it works either as a CSE array formula or using implicit intersection.

On this computer I have a new option of
= FILTER( array, array )
where the first copy of 'array' is the data to be filtered and the second is the condition, 0=FALSE.

 

[Peter Bartholomew]

In solution by Deciog, his formula is not returning any part no. in column J.

I have just downloaded deciog's solution onto a non dynamic array copy of Excel and got blank fields instead of the list of part numbers. Committing each cell individually with CSE sorted the problem (I had to remove implicit intersections to make the dynamic array version work).
Since the solution was built on a workbook that contained names, I introduced the names where they matched the direct references. I also inserted a new named formula 'Coords' to hold the array part of the calculation
=IF((COUNTIF(MATCH!R3C:R[-1]C,PartTbl)=0)*ISTEXT(PartTbl),ROW(Batch)/1%+{2,3,4},4^8)

The formula in column J then reduces to
=INDIRECT(TEXT(MIN(Coords),"R0C00"),)&""
which works without CSE.

I probably owe deciog an apology for hybridising the formula but at least I understand it an have it working on both Excel versions.

 

[deciog]

Peter, I'm sorry for the delay in answering, I've been on a client, and there's a time zone

This trick I learned with you, like Bosco, with GraH - Guido, with vletm, summarizing with the members of this forum

The credit is not mine, I do not remember which post I saw this formula

When I moved from Portuguese-Brazil "L0C00" to English "R0C00", I did not press CTRL + SHIFT + ENTER

The previous solution that you showed in # 19 that was great

Put it back with the right formula

Decio

 

[Nebu]

Hi:

Using Power query. I guess its easy and quick once you set this up, just need to refresh the pivot to get the results.

Thanks