Populated list

[ExcelRoy]

hi,

i am looking for a way to generate a combined/populated list of products that have been used on a particular job number

Column A has quantity
Column B has item number
Column C has description

i am looking for a sheet, lets say "Used" to show a combined list of the different parts used

ie

job 102 used

4x AAAA
1x BBBB
2x CCCC

job 175 used

1x AAAA
3x CCCC

so my populated list would show

AAAA x5
CCCC x 3
BBBB x 1

Hope this all makes sense

Thanks

 

[Akashwani]

Hi ExcelRoy,

Is SUMIF your solution?....

Excel Workbook
	A	B	C	D
1	Job Number	Quantity	Item Number	Description
2	102	4	AAAA	Part A
3	102	1	BBBB	Part B
4	102	2	CCCC	Part C
5	175	1	AAAA	Part A
6	175	3	CCCC	Part C
Sheet1

Excel Workbook
	A	B
1	Item Number	Quantity Used
2	AAAA	5
3	BBBB	1
4	CCCC	5
Used

You could also use a Pivot Table....

Excel Workbook
	F	G	H	I
2	*	*	*	*
3	*	Item Number	Sum of Quantity	*
4	*	AAAA	5	*
5	*	BBBB	1	*
6	*	CCCC	5	*
7	*	Grand Total	11	*
8	*	*	*	*
Sheet1

I hope that helps.

Ak

 

[ExcelRoy]

Fantastic Thanks

 

[ExcelRoy]

Hi Akashwani,

How would i achieve a sorted list with the most used part at the top and the least used part at the bottom?

I would prefer not to use a pivot table if possible?


Thanks

 

[Akashwani]

Hi,

You could try using a RANK formula in column C of Sheet Used and sort by that.

=RANK(B2,$B$2:$B$4)

AK

 

[ExcelRoy]

Yeah that was my thoughts, thanks

 

[Akashwani]

If you want to split ties....

=RANK(B2,$B$2:$B$4)+COUNTIF($B$1:B1,B2)

Ak