Forum Discussion
Excel - help me please
Hello, I want to know what formula I need when there is a list of duplicate different manufacturers and a number of duplicate different parts and I want to determine the manufacturer and next to it ...
I assume that you have somewhere a table along the lines of
Manufacturer Part
It is possible that for optical reasons you chose to lay out
Manufacturer1:
Part1
Part2
Manufacturer2:
Part1
Part2
Please give up on this human layout and switch to Excel's preferred layout:
Manufacturer1 | Part1
Manufacturer1 | Part2
Manufacturer2 | Part1
Manufacturer2 | Part2
It might not be so pleasing to the eye but you can now easily apply Data/Filters. This would be my static lookup, ie a table that tells which parts can be had from which manufacturer. (You can Filter by Part.)
I am now a bit lost. Do you literally just want to know how many parts a manufacturer sells? A really cool way to do this might be to cover the entire first column with a span
=SUBTOTAL(3;COl1). When you then Filter by Manufacturer1, the SUBTOTAL tells you the number of parts. (Filter by Parts and the same tally will tell you how many suppliers there are.)
However, if you additionally had an order table somewhere made up of many rows
ManufacturerX | PartX | QuantityOrdered
and you are wanting to know for each Part in the table above how many had been ordered, then simply add a 3rd (reporting) column to the static database:
| =SUMIFS(Orders/Quantity;Orders/Manufacturers;col1;Orders/Parts;col2)
and that will give you the total orders for each Part.
Manufacturer Part
It is possible that for optical reasons you chose to lay out
Manufacturer1:
Part1
Part2
Manufacturer2:
Part1
Part2
Please give up on this human layout and switch to Excel's preferred layout:
Manufacturer1 | Part1
Manufacturer1 | Part2
Manufacturer2 | Part1
Manufacturer2 | Part2
It might not be so pleasing to the eye but you can now easily apply Data/Filters. This would be my static lookup, ie a table that tells which parts can be had from which manufacturer. (You can Filter by Part.)
I am now a bit lost. Do you literally just want to know how many parts a manufacturer sells? A really cool way to do this might be to cover the entire first column with a span
=SUBTOTAL(3;COl1). When you then Filter by Manufacturer1, the SUBTOTAL tells you the number of parts. (Filter by Parts and the same tally will tell you how many suppliers there are.)
However, if you additionally had an order table somewhere made up of many rows
ManufacturerX | PartX | QuantityOrdered
and you are wanting to know for each Part in the table above how many had been ordered, then simply add a 3rd (reporting) column to the static database:
| =SUMIFS(Orders/Quantity;Orders/Manufacturers;col1;Orders/Parts;col2)
and that will give you the total orders for each Part.