Joining 2 indexing (referencing) formulas

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Hello All,
I have had a huge amount of help from people on this (my formulation knowledge is pretty basic) and I am hoping to once again someone may be able to help me out with formulations. 3 sheets are involved.
Sheet 1 is a master sheet people input a "Y" into 2 different columns (E (part) & A (heading)). The same item can be listed against various headings.
Sheet 2 is part of an order form currently in use. This references the "Y" and pulls information accordingly. If the same part is required multiple times, it will list it multiple times.
The result I am looking for is Sheet . A MATCH / COUNTIF version of sheet 2. If the "Y" is placed against a part that is required multiple times, it is listed once and the total is tallied up.
All formulas currently in use have been listed. I know that with the 3 sheets I can get the result, however, I am hoping to only have 2 sheets. I am not the end user of this and I would like to not complicate what people are using or seeing
All columns are the same as the actual. Rows and sheet names are different, but I can deal with that later.
Sheet1
Book1.xlsx
ABCDEF
1 REPAIRRepair NumberNOMENCLATUREQTY RequiredORDER
2y1EXTERIOR
3Top1
4Middle1
51Bottom1y1079
61Holder1y1587
7Clip5
8Top1
9Middle1
101Bottom1y1079
111Holder1y1587
12Clip5
13y2boxes
142Box 1y1208
15Box1543
162BOLTS4y1373
172WASHERS4y1374
18y3cables
193Cable1y202
20y4rack
21Washers21374
22Rack11647
23Extension11648
24bracket1241
2543/8'6y1376
264bottom3y1095
27pin21271
284rope2y68
29Screw21380
30Washer21347
31y5brackets
321
3311380
34BOLTS31134
355WASHERS3y1392
36Harness11384
375Harness1y1076
38Bracket11074
395Bracket1y71
40TOP2167
41TOP271
42BOTTOM11379
43BOTTOM11042
445Cover1y1043
455Cover 1y1044
46y6rack
47Washers21374
48Rack11647
49Extension11648
50bracket1241
5163/8'6y1376
526bottom3y1095
53pin21271
546rope2y68
55Screw21380
56Washer2
57y7boxes1208
587Box 1y1208
59Box1543
607BOLTS4y1373
61WASHERS41374
Sheet1

Sheet 2
Cell Formulas
RangeFormula
C1C1=IFERROR(INDEX(Sheet1!$F$2:$F$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(C1:C$1))),"")
D1D1=IFERROR(INDEX(Sheet1!$C$1:$C$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(D1:D$1))),"")
C2:C21C2=IFERROR(INDEX(Sheet1!$F$2:$F$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(C$1:C2))),"")
D2:D21D2=IFERROR(INDEX(Sheet1!$C$1:$C$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(D$1:D2))),"")
I1I1=IFERROR(INDEX(Sheet1!$D$2:$D$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(I1:I$1))),"")
I2:I21I2=IFERROR(INDEX(Sheet1!$D$2:$D$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(I$1:I2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Sheet 3
Cell Formulas
RangeFormula
C2:C15C2=IFERROR(INDEX(Sheet2!$C$1:$C$980,MATCH(0,COUNTIF(Sheet3!$C$1:C1,Sheet2!$C$1:$C$980),0)),"")
D2:D15D2=IFERROR(VLOOKUP(C2,Sheet2!C:C:Sheet2!F:F,2,FALSE)&"","")
I2:I15I2=IF(SUMIF(Sheet2!C:C,C2,Sheet2!I:I)=0,"",SUMIF(Sheet2!C:C,C2,Sheet2!I:I))
Press CTRL+SHIFT+ENTER to enter array formulas.


I hope this is clear enough to any and all who have both come to look and or to help

Thanks once again
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
How about
Cell Formulas
RangeFormula
C2:C16C2=IFERROR(INDEX(Sheet1!$F$2:$F$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-ROW(Sheet1!$E$2)+1)/(Sheet1!$E$2:$E$1216="Y")/(ISNA(MATCH(Sheet1!$F$2:$F$1216,C$1:C1,0))),1)),"")
D2:D16D2=IFERROR(INDEX(Sheet1!$C$2:$C$1216,MATCH(C2,Sheet1!$F$2:$F$1216,0)),"")
I2:I16I2=IF(C2="","",SUMIFS(Sheet1!$D$2:$D$1216,Sheet1!$F$2:$F$1216,C2,Sheet1!$E$2:$E$1216,"y"))
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Ahh Fluff. You’ve done it again
Thanks very much. Works like a charm
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff
Again thanks for your help.
I have noticed that on a couple of rows (5 out of 287) for some reason on the sum formula though the return value is 0. I went back to the main sheet to re enter the quantities required, tried deleting the row and re typing the information, however regardless of what I tried the result still remains 0.
I have checked the sum formulas and it is the same as the ones that work.
Any ideas as to why this could occur?

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Only thing I can think of, is that the numbers on sheet1 are text & not numbers.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,600
Members
416,927
Latest member
BNM8V6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top