Function to Return Mutiple Text or Addresses

[jdurrmsu]

I am trying to derive a formula to return text or Row-Column addresses with possibly more than one true instance and I am having trouble getting it to work. I tried using the sumproduct function but it does not return text. To fix this I added a column with nothing but numbers to return them but when the function finds more than one instance where it meets the requirements it sums the values (as it should) but I need a formula to return both values seperately. Currently this is how my formula reads. =SUMPRODUCT(--(D[Y]=Sheet1!D2:D2000),--(E[Y]=Sheet1!E2:E2000),Sheet1!A2:A2000) where [Y] denotes the particular row in which the formula is entered. I do not care if the formula that I need returns text or the Row-Column address where each value is true. Any direction would be much appreciated. Thanks.

 

[Aladin Akyurek]

Could you possibly construct a small sample data and post it here along with the desired results?

 

[jdurrmsu]

Ok here is what I basically have the function doing. If D[Y] on Sheet2 which is a purchase order number equals the same purchase order number in cells D2 to D2000 on Sheet1 and if E[Y] on Sheet2 which is a item number equals the same item number in cells E2 to E2000 on Sheet1 then I want the function to return all of the corresponding data that meets the criteria in cells A2 to A2000 on Sheet1 to A[Y] on Sheet2. The data in column A on Sheet1 are shipment numbers. I am trying to use this formula to track materials to see what we have shipped and what is still outstanding. I forgot to mention that my formula is entered into the A[Y] box. A[Y] = SUMPRODUCT(--(D[Y]=Sheet1!D2:D2000),--(E[Y]=Sheet1!E2:E2000),Sheet1!A2:A2000)

Sheet1 Column A Col B Col C Col D Col E
Row 1 Shipment # QTY U/M PO # Item #
Row 2 R524 5 each LL-16-5 1
Row 3 R524 12 each LL-16-2 2
Row 4 T435 5 each LL-16-5 1
Row 5 T435 3 each LL-16-3 3

Sheet2 Column A Col B Col C Col D Col E
Row 1 Shipment #'s QTY U/M PO # Item #
Row 2 A[2] formula 10 each LL-16-5 1
Row 3 A[3] formula 12 each LL-16-2 2
Row 4 A[4] formula 3 each LL-16-3 3

Formula A[2] needs to return R524 and T435.
Formula A[3] needs to return R524.
Formula A[4] needs to return T435.
And so on and so on

 

[jdurrmsu]

Sorry that did not come out so well. Maybe this will be a little better. It has been to long since I wrote HTML so I have forgotten how to insert tables.

Sheet1...|....Col A..............|...Col B...|..Col C....|...Col D......|....Col E
Row 1....|....Shipment #'s...|...QTY.....|..U/M......|...PO #.......|....Item #
Row 2....|....R524...............|...5.........|..each.....|....LL-16-5..|....1
Row 3....|....R524...............|...12.......|..each.....|....LL-16-2..|....2
Row 4....|....T435...............|...5.........|..each.....|....LL-16-5..|....1
Row 5....|....T435...............|...3.........|..each.....|....LL-16-3..|....3

Sheet2...|....Col A..............|...Col B...|..Col C....|...Col D......|....Col E
Row 1....|....Shipment #'s...|...QTY.....|..U/M......|...PO #.......|....Item #
Row 2....|....A[2] formula....|...10.......|..each.....|...LL-16-5...|....1
Row 3....|....A[3] formula....|...12.......|..each.....|...LL-16-2...|....2
Row 4....|....A[4] formula....|...5.........|..each.....|...LL-16-3...|....3

Formula A[2] needs to return R524 and T435.
Formula A[3] needs to return R524.
Formula A[4] needs to return T435.
And so on and so on

 

[Aladin Akyurek]

Ok here is what I basically have the function doing. If D[Y] on Sheet2 which is a purchase order number equals the same purchase order number in cells D2 to D2000 on Sheet1 and if E[Y] on Sheet2 which is a item number equals the same item number in cells E2 to E2000 on Sheet1 then I want the function to return all of the corresponding data that meets the criteria in cells A2 to A2000 on Sheet1 to A[Y] on Sheet2. The data in column A on Sheet1 are shipment numbers. I am trying to use this formula to track materials to see what we have shipped and what is still outstanding. I forgot to mention that my formula is entered into the A[Y] box. A[Y] = SUMPRODUCT(--(D[Y]=Sheet1!D2:D2000),--(E[Y]=Sheet1!E2:E2000),Sheet1!A2:A2000)

Sheet1 Column A Col B Col C Col D Col E
Row 1 Shipment # QTY U/M PO # Item #
Row 2 R524 5 each LL-16-5 1
Row 3 R524 12 each LL-16-2 2
Row 4 T435 5 each LL-16-5 1
Row 5 T435 3 each LL-16-3 3

Sheet2 Column A Col B Col C Col D Col E
Row 1 Shipment #'s QTY U/M PO # Item #
Row 2 A[2] formula 10 each LL-16-5 1
Row 3 A[3] formula 12 each LL-16-2 2
Row 4 A[4] formula 3 each LL-16-3 3

Formula A[2] needs to return R524 and T435.
Formula A[3] needs to return R524.
Formula A[4] needs to return T435.
And so on and so on

Book3
	A	B	C	D	E	F
1	Shipment #'s	QTY	U/M	PO #	Item #
2	R524,T435	10	each	LL-16-5	1
3	R524	12	each	LL-16-2	2
4	T435	3	each	LL-16-3	3
5
Sheet2

A2:

=SUBSTITUTE(MCONCAT(IF(D2=Sheet1!$D$2:$D$5,IF(E2=Sheet1!$E$2:$E$5,","&Sheet1!$A$2:$A$5,""),"")),",","",1)

which is confirmed with control+shift+enter (not just with enter) then copied down.

The formula requires the morefunc.xll add-in that provides the MCONCAT function.

 

[jdurrmsu]

Man that formula works great! Thanks a lot! That saves me a ton of time considering I have nearly 2000 rows that I would have had to check by hand.