Formula to extract data from list

[ExcelRoy]

Hi All,

I am looking for a formula to extract data from a list dependent on what is in columns D

ie the list on sheet 1 will be

A1 : Parts
B1 : Description
C1 : Cost
D1 : Status

On sheet 2 I need

A1 : Parts only with "Ordered","Partially Received" or "Pending" status
B1 : Description with "Ordered","Partially Received" or "Pending" status
C1 : Cost with "Ordered","Partially Received" or "Pending" status
D1 : Status with "Ordered","Partially Received" or "Pending" status

Rick kindly gave me a coded version but i would prefer a formula if at all possible

Many thanks

 

[oldbrewer]

parts

descr

cost

status

part1

descr1

100

ordered

2

On sheet 2 I need

part2

descr2

105

par rec

3

part3

descr3

110

pending

4

A1 : Parts only with "Ordered","Partially Received" or "Pending" status

part4

descr4

115

not ordered

B1 : Description with "Ordered","Partially Received" or "Pending" status

part5

descr5

120

par rec

6

C1 : Cost with "Ordered","Partially Received" or "Pending" status

part6

descr6

125

not ordered

part7

descr7

130

ordered

8

part8

descr8

135

not ordered

part9

descr9

140

pending

10

1

part1

descr1

100

2

part2

descr2

105

3

part3

descr3

110

4

part5

descr5

120

5

part7

descr7

130

6

part9

descr9

140

7

#NUM!

#NUM!

#NUM!

8

#NUM!

#NUM!

#NUM!

9

#NUM!

#NUM!

#NUM!

10

#NUM!

#NUM!

#NUM!

is this what you mean ?

<colgroup><col span="3"><col><col span="11"></colgroup><tbody>
</tbody>

 

[ExcelRoy]

Hi oldbrewer,

Yes please, without the #NUM if possible but exactly that

Many thanks

 

[oldbrewer]

parts

descr

cost

status

part1

descr1

100

ordered

2

On sheet 2 I need

part2

descr2

105

par rec

3

part3

descr3

110

pending

4

A1 : Parts only with "Ordered","Partially Received" or "Pending" status

part4

descr4

115

not ordered

B1 : Description with "Ordered","Partially Received" or "Pending" status

part5

descr5

120

par rec

6

C1 : Cost with "Ordered","Partially Received" or "Pending" status

part6

descr6

125

not ordered

part7

descr7

130

ordered

8

part8

descr8

135

not ordered

part9

descr9

140

pending

10

1

part1

descr1

100

2

part2

descr2

105

3

part3

descr3

110

4

part5

descr5

120

5

part7

descr7

130

6

part9

descr9

140

7

8

9

10

formuls giving

part1

=IF(ISERROR(MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0)),"",OFFSET($E$1,MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0),-4))

descr1

=IF(ISERROR(MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0)),"",OFFSET($E$1,MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0),-3))

100

=IF(ISERROR(MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0)),"",OFFSET($E$1,MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0),-2))

<colgroup><col span="3"><col><col span="14"></colgroup><tbody>
</tbody>

 

[oldbrewer]

parts

descr

cost

status

part1

descr1

100

ordered

2

On sheet 2 I need

part2

descr2

105

par rec

3

part3

descr3

110

pending

4

A1 : Parts only with "Ordered","Partially Received" or "Pending" status

part4

descr4

115

not ordered

B1 : Description with "Ordered","Partially Received" or "Pending" status

part5

descr5

120

par rec

6

C1 : Cost with "Ordered","Partially Received" or "Pending" status

part1

descr1

333

ordered

7

part2

descr2

600

par rec

8

part3

descr3

110

pending

9

part1

descr9

555

not ordered

1

part1

descr1

433

2

part2

descr2

705

3

part3

descr3

220

4

part5

descr5

120

5

part1

descr1

433

6

part2

descr2

705

7

part3

descr3

220

8

9

10

formulas giving

part1

=IF(ISERROR(MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0)),"",OFFSET($E$1,MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0),-4))

descr1

=IF(ISERROR(MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0)),"",OFFSET($E$1,MATCH(SMALL($E$2:$E$10,$E15),$E$2:$E$10,0),-3))

433

=IF(F15="","",SUMPRODUCT(($A$2:$A$10=F15)*($C$2:$C$10))-SUMPRODUCT(($A$2:$A$10=F15)*($D$2:$D$10="not ordered")*($C$2:$C$10)))

this mod allows for a part number to occur more than once

<colgroup><col span="3"><col><col span="14"></colgroup><tbody>
</tbody>

 

[oldbrewer]

just noticed part1 is listed too many times in output table, will have another look....

 

[oldbrewer]

parts

descr

cost

status

helper

part1

descr1

100

ordered

1

part1

1

descr1

part2

descr2

105

par rec

2

part2

2

descr2

< < < mytable

part3

descr3

110

pending

3

part3

3

descr3

part4

descr4

115

not ordered

part4

4

descr4

part5

descr5

120

par rec

5

part5

5

descr5

part1

descr1

333

ordered

1

part2

descr2

600

par rec

2

part3

descr3

110

pending

3

part1

descr1

555

not ordered

col G

row 15

part1

433

descr1

part2

705

descr2

part3

220

descr3

part4

0

descr4

part5

120

descr5

433

=SUMPRODUCT(($E$2:$E$10=RIGHT($G15,1)*1)*($C$2:$C$10))

1 under helper header

=IF(D2="not ordered","",VLOOKUP(A2,mytable,2))

descr1 to right of 433

=VLOOKUP(G15,mytable,3)

<colgroup><col span="3"><col><col span="13"></colgroup><tbody>
</tbody>