Fun Index array formula based on multiple criteria

[Brad B]

Hey guys, I'm sure this is possible, but I've had quite a time trying to figure out how to do it. Any help would be awesome. Currently this is basically done manually and it's very time consuming: I know there is a better way.

I'll describe a simplified version of what I need below, and then adapt the answer to my purposes.
Table X

Material Type	M	T	W	R	F	Work Center
abc1	1000					1
abc2		1500				1
abc3						2
abc4			2000			3
abc5			2500			3
abc6					3000	3
abc7					3500	2

<tbody>
</tbody>

First Formula I need: pull data from table X into table Y

I was thinking it would be easiest to modify 5 different formulas (rather than an additional step in the formula to match which day it's on, but if you want bonus points, write me a formula that I can drag both ways to accomplish all this

here's what I had in mind though: 1 formula for each day, M-F, drag down in respective columns.
ie: for monday, it might look something like: index($a$2:$a$8,small(if($b$2:$b$8>0,"",...

I have seen similar array formulas with "row", and I'm not very comfortable working with that yet but I do generally understand what it does, and how it works in an array.

I am comfortable with index(match,(match), and I think match will be needed to get the work center, but there could be multiple materials on the same work center, and match will only return the first it finds. (I also know you can match based on multiple criteria, but I'm not quite sure how to put it all together).

If there are multiple results for one cell (see Wed, work center 3), i think it would be sufficient to show in same cell, separated by a comma & space: (result)&", "&(result). It would also be ok for now to just show the first result and not the others for a particular cell.

Table Y: material by day

Work Center	M	T	W	R	F
1	abc1	abc2
2					abc7
3			abc4, abc5		abc6

<tbody>
</tbody>

Table Z: Lbs by day
I want to do a very similar formula next, to pull the amount ordered, instead of the name of the material.

Work Center	M	T	W	R	F
1	1000	1500
2					3500
3			2000, 2500		3000

<tbody>
</tbody>

*Would it be easier to do this with some kind of pivot table instead? I realize that's basically what pivot tables are for... data transformations like like this. Can a pivot table be set to refresh / re-calc every time data is changed in it's source table? Also, as far as I know, pivot tables don't play nice with text, only numbers...So table Z could probably be done with a pivot, but I'm not so sure about table Y.

Either way, I'd love to hear some feedback so I can learn more about this type or formula, because I think it'll be helpful in my work regardless to know how to accomplish this.

Thanks everyone!

 

[Eric W]

Well, let's address the Pivot Table idea first. I'm not an expert with Pivot tables, so there may very well be a way to use them to get what you want. But in my experimentation, I couldn't manage to get your table Y to work. I could get the table Z to work, but the values were summed (4500) and not concatenated (2000, 2500). It ended up looking like:

	A	B	C	D	E	F
2
3	Row Labels	Sum of M	Sum of T	Sum of W	Sum of R	Sum of F
4	1	1000	1500
5	2					3500
6	3			4500		3000
7	Grand Total	1000	1500	4500		6500
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

To get this result, select your table X, click Insert > Pivot Table. Click OK to say "put on new sheet". Then from the PivotTable Fields dialog, drag Work Center to the Rows box, and drag M, T, W, R, F to the Values box. Then click on each of those letters, select Value Field settings, and select Sum.


Now if you want to create the tables using formulas, you'll need the new TEXTJOIN function. Microsoft added this for Excel 2016.

	A	B	C	D	E	F	G
1	Material Type	M	T	W	R	F	Work Center
2	abc1	1000					1
3	abc2		1500				1
4	abc3						2
5	abc4			2000			3
6	abc5			2500			3
7	abc6					3000	3
8	abc7					3500	2
9
10	Material by day	M	T	W	R	F
11	1	abc1	abc2
12	2					abc7
13	3			abc4, abc5		abc6
14
15
16
17	Lbs by day
18	Work Center	M	T	W	R	F
19	1	1000	1500
20	2					3500
21	3			2000, 2500		3000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula B11 {=TEXTJOIN(", ",TRUE,IF(($G$2:$G$8=$A11)*(B$2:B$8<>""),$A$2:$A$8&"",""))} B19 {=TEXTJOIN(", ",TRUE,IF($G$2:$G$8=$A19,B$2:B$8&"",""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in B11, and confirm with Control+Shift+Enter. Drag down and to the right. Repeat with the B19 formula.


Finally, if you do not have the TEXTJOIN function, you can still use those formulas with a VBA add-in.

Let me know if any of this helps, or if you want the add-in.

 

[Brad B]

Hi Eric,

Thanks for your response.

I'm using excel 2010, so I don't have the textjoin funtion. Though I do have a basic working knowledge with VBA, I'd prefer not to go that route.

If I'm ok not pulling in multiple matches - just showing the first match for each cell - can my table Y be accomplished with some kind of index & match formula, maybe with multiple criteria?

I tried to break down what I would do as a human if I was taking data from table X to table Y, to create the logical steps of the formula, but I kind of got stuck.

1. I would look at All Material types
index(material types,__,__)
2. I would look at a subset of all materials: just the material types that are being ordered
if(total>0)
index(material types*if(total_order>0,__,__)

3. Then I would check against that subset (just the materials being ordered) until I found the first one that I matched whichever work center I was on (ie, the current line in table Y.
match(work center,in work center list)
index(material types*if(total_order>0,match(work_center,in work_center_list),__)

Match_2 a second condition on the same match (the row element of the index formula) could be to match the amount of material, ie: 1500. (It's not fail-safe though - it would be rare that two materials had the same qty ordered, but it could happen on occasion)

4. Then I would match the day
match(day_current column in table Y),days list(table x header row: days)


1. An issue I had is that I don't know how to limit the array I'm indexing to just materials actually being ordered.
ie: in a total column, (total material for the week - materials are generally only ordered once per week), I could check against the condition >0. I'm not sure the syntax and I played around with it some, but I'm going for index(material_type*if(total_ordered>0)


Let me know if you have any other ideas on how to proceed.

Thanks!

 

[Eric W]

If you just want the first entry found instead of all them concatenated, try:

	A	B	C	D	E	F	G
1	Material Type	M	T	W	R	F	Work Center
2	abc1	1000					1
3	abc2		1500				1
4	abc3						2
5	abc4			2000			3
6	abc5			2500			3
7	abc6					3000	3
8	abc7					3500	2
9
10	Material by day	M	T	W	R	F
11	1	abc1	abc2
12	2					abc7
13	3			abc4		abc6
14
15
16
17	Lbs by day
18	Work Center	M	T	W	R	F
19	1	1000	1500
20	2					3500
21	3			2000		3000

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula B11 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF(($G$2:$G$8=$A11)*(B$2:B$8<>""),ROW($G$2:$G$8)-ROW($G$2)+1),1)),"")} B19 {=IFERROR(INDEX(B$2:B$8,SMALL(IF(($G$2:$G$8=$A11)*(B$2:B$8<>""),ROW($G$2:$G$8)-ROW($G$2)+1),1)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Enter them in the appropriate cells and drag down and across as needed. Note that the formulas are identical, except for the column used in the index. For the first formula it's a fixed $A$2:$A$8, and for the second it's B$2:B$8, with the column not fixed, which means when you drag to the right, it will adjust to a different day.

I'm not sure what you mean when you want to select by amount, 1500. Are you saying that for table Y, if you enter an amount of 2500 on Wednesday, it will return abc5 instead of abc4?

 

[Brad B]

Thanks Eric,

That worked great! I was able to modify it to my needs. You can disregard the question I had about matching the weight; it was an additional criteria I was thinking of using to match the material type, but I realize now that it's not necessary.

In thinking about the multiple items on a work center issue, and trying to avoid concatenating (It's actually the goal not to have multiple materials on a work center on a day anyway), I was wondering if I could create another table Y, with the SECOND occurring material on that work center & day. This would serve as a notification to me of which materials are "extra" or scheduled in addition to the first materials that occur on the list.

Is it as simple as changing the 1 to a 2 on the end of the formula, or something similar?

I didn't fully understand the small and rows part of the formula, so if you wouldn't mind, could you explain how that part works? (Particularly the ROW($G$2:$G$8)-ROW($G$2)+1),1)) part).

Thanks for all the help!

 

[Brad B]

I changed the 1 to a 2 on the small function and it worked exactly as I was looking for above. All I need now as a little explanation of how the rows function is being used in this formula and why it works

Thanks

 

[Eric W]

Good job figuring out the SMALL function!

As far as the ROW function, first keep in mind that there are 2 functions, ROW and ROWS which are different. ROW(A5) will return the row of the cell, or 5. ROWS(A5:A6) will return the number of rows in the range, in this case, 2 rows (5 and 6).

And as far as how I use it, let me give you an alternate version of the B11 formula first:

=IFERROR(INDEX($A:$A,SMALL(IF(($G$2:$G$8=$A11)*(B$2:B$8<>""),ROW($G$2:$G$8)),1)),"")

The way the array part of the formula works, it looks at each cell in the $G$2:$G$8 range and each cell in the B$2:B$8 range, and if they match what we're looking for, it returns ROW($G$2:$G$8), or in other words, the row with the matching values. (If I used ROWS instead of ROW there, it would always return 7, not the row number that matches.) Then out of the internal array of all the matching rows, SMALL,1 will pick out the smallest row, and INDEX($A:$A ... will get the value from that row in column A. Make sense?

The original version I posted uses $A$2:$A$8 as the range for INDEX instead of the whole column. In order to get the proper offset into $A$2:$A$8 instead of $A:$A ($A$1:$A$1000000), I have to change the actual row number from 2,3,4,5,6,7,8 to an offset into the range, 1,2,3,4,5,6,7 which is what the ROW($G$2:$G$8)-ROW($G$2)+1 does, the second ROW refers to the top row of the table.

Which version to use largely depends on personal taste. It's often useful to know how to adjust formulas to account for headings, which is why I first gave you the version I did.

Glad to help!