JannetteChristie
Board Regular
- Joined
- Dec 14, 2015
- Messages
- 124
- Office Version
- 365
Hello,
I have the following data on a worksheet and want to create 1 list of the combined columns.
I want to create the following on the same sheet but in columns R and S
Thanks
I have the following data on a worksheet and want to create 1 list of the combined columns.
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:A16 | A2 | =IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),1),"") |
B2:B16 | B2 | =IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),4),"") |
C2:C16 | C2 | =IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),2),"") |
D2:D16 | D2 | =IFERROR(SUMIF($K:$K,"="&$A2,$N:$N)*VLOOKUP(A2,Configurator!$A$2:$D$1102,4,FALSE),0) |
F2:F16 | F2 | =IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),1),"") |
G2:G16 | G2 | =IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),4),"") |
H2:H16 | H2 | =IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),2),"") |
I2:I16 | I2 | =IFERROR(INDEX('Non-Standard Costings'!$A$1:$F$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),6),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
waProducts | =Analysis!$AJ$1 | F2:I2, A2:C2 |
I want to create the following on the same sheet but in columns R and S
Quoting Tool.xlsm | ||||
---|---|---|---|---|
R | S | |||
1 | CQ - Quantity | CQ - Description | ||
2 | 1 | Removable Transport bar | ||
3 | 1 | Unit needs to be individually craned | ||
4 | 1 | Split ceiling with removable service panel for MVHR | ||
5 | 2 | Soil vent pipe access hatch | ||
6 | 1 | Standard MVHR single condensate pipework incl. tundish | ||
7 | 1 | Scolmore Double Un-Switched Socket | ||
8 | 1 | Scolmore Single Un-Switched Socket | ||
9 | 1 | Scolmore Switched Fused Spur (Incl. engraving charge) | ||
10 | 4 | Scolmore 6-way 2G Minigrid Assembly (no switches included) | ||
11 | 1 | Scolmore Minigrid 2-Module Fan Isolator Switch | ||
12 | 1 | 1G Empty Drylining Back box for client use | ||
13 | 3 | Testing | ||
14 | 4 | Test line 2 | ||
Analysis |
Thanks