• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Formula to allocate Qty delivered to Purchase order |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Formula to allocate Qty delivered to Purchase order |General Excel Questions & Answers|Excel Forum|My Online Training Hub

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Formula to allocate Qty delivered t…
sp_PrintTopic sp_TopicIcon
Formula to allocate Qty delivered to Purchase order
Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
1
September 27, 2017 - 5:57 pm
sp_Permalink sp_Print

Dear Sir,

In our warehouse we have suppliers deliver our parts, ideally they should quote our Purchase order, but sometimes missed, sometimes  they only provide the materials and Qty delivered information in theri packing list. So, our question is how to set a formula match each quantity delivered [ assume First in First out basis against our outstanding Purchasing order balance in System relying on the only common key " Material"

Pls refer attached sample.

Thanks your help

 

B.Rgds

David

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
September 28, 2017 - 11:23 am
sp_Permalink sp_Print

Hi David

Give this a try.

Hope this helps.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
3
September 28, 2017 - 11:36 am
sp_Permalink sp_Print

Thanks Sunny, but  how can the formuda extend to the rest of the column F.

as delivered quantity is at random with reference to the material.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
September 28, 2017 - 12:12 pm
sp_Permalink sp_Print sp_EditHistory

Hi David

Maybe you can give as many scenario as possible in your example with the expected result (very important).

I didn't quite understand the part on "delivered quantity is at random with reference to the material".

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
5
September 28, 2017 - 12:38 pm
sp_Permalink sp_Print

Sure, please refer the attached again as the material deliverd from the Vendors to our warehouse varies daily

and from their packing list [ soft copy ] we extract only the material field and quantiy but no our Purchase order number can be extracted so we manuallly allocate the quantity recevied arbitrarily allcoate to each open Purchase order until each Purchase order is duly completed. thus we want a formula to allocate  QUANTITY received with respect to each respective material received.

In your  previous formula appied it almost there, but I just don't know how to extend the forumula to the rest in Column F. because when I copy the formula to ther rest of the cells in F column , it didn't work out becasue the relative and absolute address of the Formula changed, and I don't know how to anchor the cells to obtain the desired allocation ie the  result.   

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
September 28, 2017 - 1:11 pm
sp_Permalink sp_Print

Hi David

These formulas does not work by just copying them to another cell.

This is what you can do:

1) Let's say you copy the formula from cell F5 to cell F17.

2) Select cell F17 and press the F2 edit key (not the cell F2Laugh) to edit the formula.

3) The cells that the formula refer to will be highlighted with colored boxes.

4) Just drag the highlight to the correct cells.

Hope this helps.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
7
September 28, 2017 - 1:21 pm
sp_Permalink sp_Print

Sunny, you mean drag all  highlighted boxes to the designated Cells ?

But when I press Function key F2, boxes is hgihlighted , but can not  drag to the Cell(s) I want ..

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
September 28, 2017 - 1:28 pm
sp_Permalink sp_Print

Here I have a diagram for you.

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
9
September 28, 2017 - 1:34 pm
sp_Permalink sp_Print

Thanks Sunny.

 

I tried first to see if I can  control

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
10
September 28, 2017 - 2:01 pm
sp_Permalink sp_Print

Thanks Sunny , I tried it seems work out , but drag and dropped actions seems a very tediuos exercise , is there other simple way

we can copy the formula, though we can manuplate the raw data.

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
11
September 29, 2017 - 10:25 am
sp_Permalink sp_Print

How about if we reshuffle the data then applied the formula you illustrate ( ie can copy the formula) , a the drag and dropped action is very difficult to use if the row data become huge..

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
September 29, 2017 - 11:33 am
sp_Permalink sp_Print

Hi David

I have looked at reshuffling the data and even separating them into different sheets (may not be practical as you may have many customers/products) etc but have not been able to find a workable and practical solution so far.Cry

Your situation require that certain cells to be absolute (locked)

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
13
September 29, 2017 - 11:48 am
sp_Permalink sp_Print

Really deep thanks Sunny you take the effort look into the case. Yes we are handling few hundreds Vendors and about hundred Customers in our Group purchase, anyway your solution do help a lot already.   Thanks again!

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
14
September 29, 2017 - 12:38 pm
sp_Permalink sp_Print

Hi David

See if this will work. It needs a helper column.

Just make sure that the 1st row is the delivered value e.g. cells D4, D16 etc. It can be 0 if there are no deliveries.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
15
September 29, 2017 - 1:17 pm
sp_Permalink sp_Print

Sunny, you are superb, armed with this creative idea ie setting the helper column, this really bring us home and can make us early to bed. We cherish that idea so much as sometimes the delivered quantity which we need to allocate by manual to the Purchase Order takes ages to complete, worse still, allocation is oftern wrong, now not only take few minutes to complete but with comfort of assurance in accuracy. Really thanks !

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
16
September 29, 2017 - 1:24 pm
sp_Permalink sp_Print

Hi David

Thanks for your feedback and complimentsSmile

By helping you, I have also learned a thing or two. Otherwise I wouldn't have even look at such a situation.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
17
September 29, 2017 - 4:34 pm
sp_Permalink sp_Print

Allow me Sunny I modified the formula a bit. ie Put a conjunction( two condition ) in the If command to make it more precise.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
18
September 29, 2017 - 4:52 pm
sp_Permalink sp_Print

Hi David

You can modify to suit your needs.

Just remember that the 1st row must be the delivered value e.g. cells D4, D16 etc Otherwise the formula will fail.

I noticed that quite a number of rows (e.g. rows 43, 46, 49 etc) for the delivered values are not added in your attachment. I suggest you add them BEFORE you copy the formulas to prevent any problem.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
19
October 3, 2017 - 10:55 am
sp_Permalink sp_Print

Thanks Sunny the remind..

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Mynda Treacy, Riny van Eekelen, Ivan Kulubya, Duaa Elsheikh, Christian Huapaya, Laxmi Praveen
Guest(s) 33
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 845
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Armani Quenga
moshood bello
annelies b
James1989
lucy gilmour
Forum Stats:
Groups: 3
Forums: 24
Topics: 6045
Posts: 26524

 

Member Stats:
Guest Posters: 49
Members: 31492
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x