Find all similar entry names and add their qty

09/04/20
Accepted

Hello I have a sheet I made where I load in all my orders of items needed for a job. Each Item has a code assigned to it I'm calling the Entry Name. There will be times where the same Code/Entry Name will appear several times during the life of the project.

For Example This week I might need 4 - BT01 (Entry Name) then as the project goes on the next phase I'll need 5 more - BTO1.

I'm trying to make a Master sheet Look at the order sheet every time it finds a BT01 it will take the qty ordered and add it total For that one item and display it on this new Maser list sheet I'm trying to make.

I plan to do this for the item for QTY Rec'd, QTY Ordered, Outstanding, How many shipped to the job.

Any suggestions how to make this come to life?

Best Answer

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Grant Graves

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Hope this helps @Andrée Starå Basically what I'm making is a shipping order/shipment tracker for items I order for a construction job. So for example sometimes there is a area of early turn over items I need so they can complete the section that must be finished and turned over to the owner. Then later I will have either 1 Bulk order of the remaining items to finish the project. Or possibly several small orders.

    Each of these Orders can have duplicate entry names. As we might need more as the project goes on or need the same items for another area of the building.

    What I need help with is I'm trying to make something Look at this sheet Find Same Entry Names Then add the Qty's Ordered. and list that. So I can have an overall view of how many of that item was ordered over the whole timeline of the project.

    So this example below the first 2 Orders that were placed One order has 3 Unique items and how many we ordered. And the 2nd order has more items. The first item on both orders are the same Entry Name. So I'm looking a way to make another sheet Report Hey you have so far ordered (22+3=) 25 Total of the EA04 (entry name) Then I plan on making another column on that same sheet show me How many of that item were Received (using the data in the Rec'd Column)


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try working with the SUMIFS function (link included).

  • @Paul Newcome If I used that I would have to write each "Entry Name" in the formula?


    There will be hundreds/thousands of items I would have to list. I'm wondering If I can do something with the Index & match Feature to do the work.....

  • edited 09/08/20

    @Paul Newcome So Let's say the first order I get 3 - EA04 I'm looking to either have a column or another sheet give a summary Saying EA04 you ordered 3 of them.

    Then as the time goes on I place another order for 10 more EA04. I'm looking to have that sheet Show now the new Total of 13 EA04 (Goal is to only show each item once with combined count of qty. Rather than showing EA04 listed several times with different QTY.)

    Hope I'm explaining this clearly.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Either way you are going to need somewhere to track each individual order. You could do this with a form on a separate sheet (my personal suggestion) or any number of other ways.

    Then on your "summary sheet" you would only list out EA04 one time and then use a SUMIFS to get that 13. It will automatically update as new entries are made and consolidates the data so that you only have one row to look at for a live total.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Grant Graves

    You're more than welcome!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.