+ Reply to Thread
Results 1 to 25 of 25

Formula to automatically copy select row data across multiple columns into new sheet

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Formula to automatically copy select row data across multiple columns into new sheet

    Hi all

    I have tried to do this own but I am in deseperate need of helping and I cant get it done so will be eternally grateful if someone can help me with this.

    I have attached a spreadsheet to explain the formula/s I am looking for - the attachment has all dummy data and nothing personal without the use of MACROS.


    So I have two sheets:

    Analysis sheet: this sheet is where all the order data is held. We have loads of columns as want to hold all the data across ONE row so we can analyse the data efficiently and effectively using a pivot table and would like to keep the format.

    On this sheet, to enter individual product sales data we have dedicated multiple columns for each product as you will see. For example, each product we record will have 5 seperate columns to record each products:- product code, size, unit cost per sqm, quantity and total cost.

    Purchase Order sheet: This is where we want the formula to work. Since ever order on the analysis sheet is one one row. Whenever a new a order is recorded on the analysis sheet, using the ORDER-PO column (Purchase Order Number), we want to automatically add the respective rows to the Purchase order sheet without having to copy and paste or do anything.

    The only difference though is we want the formula to check how many products have been entered on the analysis sheet and then seperately record each product entry on seperate rows on the purchase order sheet.

    The attachment I have attached demonstrates what I mean if my explanation is a little unclear.


    Then once those all those product data have been copied over into the Purchase Order spreadsheet, in order to not duplicate totals I would also want the last 4 columns on the purchase order spreadsheet to only show the data on the last line of the last product. The reason for this is that when I run a pivot table I dont want the the totals or notes to apply to every single line as it can screw up our calculations and data.

    I am truly stuck and if someone can help me I would be super grateful.

    Many thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,629

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    You can't still be using the ancient Excel 2007 ...can you? If not, please update your forum profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Apologies I am using Microsoft Office 2019. I have updated my profile.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,629

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    You are probably going to need VBA for this - shall I move the thread for you?

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hey AliGW,

    Id like to see if someone can has a formula that would work because when I have vba theres always bugs with the spreadsheet afterwards.

    So if we can keep on here and see if anyone can do it through formulas I would greatly appreciate it.

    Many thanks

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,629

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    My concern is this requirement:

    Then once those all those product data have been copied over into the Purchase Order spreadsheet, in order to not duplicate totals I would also want the last 4 columns on the purchase order spreadsheet to only show the data on the last line of the last product.
    You understand, I hope, that your data layouts, etc. cannot be CHANGED with formulae? Nor can data be MOVED or COPIED. It can only be displayed.
    Last edited by AliGW; 07-04-2021 at 01:13 PM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    You will need VBA for this so you need think carefully how you want the VBA to be "triggered": probably using a button to start a macro.

    And Is there a Purchase Order sheet for each order or are all orders on one sheet?

    as a starter set of questions ....!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    The basic question is: what will "trigger" the copying from the Analysis sheet to the Purchase Order sheets: this cannot be done with formulae.

    You can have set of formulae in the Purchase Order (copied down as many rows as needed) which will simply copy (extract) the data from Analysis so as soon as ANY data is entered in the "Analysis" sheet it will be reflected in the "Purchase Order" sheet

    However, this is further complicated by the fact the number of products per Order varies so working out your "total" line is a problem . We do not know how many prouducts there are so do not know many "repeated" lines there are ( A:L on Purchase Order)

    And (i am sure this happens) what if there is a change (say of Installation date) once the (initial) Purchase Order) has been copied?

    Do not underestimate the complexity of this "Order System"
    Last edited by JohnTopley; 07-04-2021 at 01:40 PM.

  9. #9
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hey John and Ali,

    So apologies just to clarify.

    So what I meant by moved.

    Essentially if you look at the example spreadsheet that I have attached at the top of the post - I mean everytime on the analysis spreadsheet an order would be recorded, the purchase order sheet would through a formula add that particular order to the Purchase Order sheet. So in essence the purchase order sheet everytime a new Sales order is added on a row on the analysis sheet, would automatically add that order to the purchase order sheet but break it out over multiple rows if there is more than one item being sold per order.

    John to answer your question - the trigger really is everytime a new row is added to the Analysis sheet with its own unique purchase order number - the purchase order sheet (which does exist) would lookup and copy that data into the purchase order sheet.

    Hope that answers your question.

    If you look at the sample excel sheet at the top it will give you exactly an idea of how it want it work and how I would like the data to be displayed on the purchase order sheet once it carries the data over the analysis sheet.

    Any other questions please feel free to ask.

    Kind Regards

  10. #10
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hi John

    many thanks for this - and Ive tried to answer as best as I can below:

    a. The Trigger: is any new row/purchase order number added to the analysis spreadsheet.
    b. In terms of the second point - couldnt we add a formula which essentially seperates out all the products into seperate rows if there are text on them and have a formula in the total column which only sums all the individual product totals on the last line of the purchase order?
    c. In terms of changes to installation date - wouldn't the formula allow the data to be linked by the purchase order number - so if anything changes in the analysis sheet that the data will also change in the Purchase order sheet similar to like a vlookup.

    Many thanks again

  11. #11
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    If the totals pose a problem then Im not too bothered about that but if everytime a row is added to the Analysis spreadsheet the Purchase Order sheet is linked to it and has that entered there seperating each product row by row but still linked to the original purchase order number and the data is continued to be linked to the Analysis sheet, so if there any changes to the data in the Analysis sheet it will change the data in the Purchase Order Sheet that is key.

    Hope this elaboratesit further.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    I understand what you want but I do not think you understand how a formula solution will work.

    Any formula has to already exist in the "Purchase Order" file ("automation") as soon as you enter the Order No in column A of the "Analysis" sheet it will appear in the "Purchase Order" sheet. Until the "Analysis" sheet is completed, we do not know how many products (1 to 4) are on the order so therefore we do not know how many order lines there are. i.e repeated rows of columns A:L

    What happens if the number of products on an order changes so initially we require 2 but prior to installation we add (or remove) a product?

    This is not simple: Order processing is complex!

    I am sure some will give it ago: not me, I am still in the Excel "backwoods" with Excel 2010!
    Last edited by JohnTopley; 07-04-2021 at 02:01 PM.

  13. #13
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Thanks John for the message.

    Is there anyone else who can help me with this - I am helping out a friend who runs a small business which he's taken over from his father who never used IT or recorded his sales digitally before so this will really mean alot of someone can help with this.

    Many thanks

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    It is not a matter of helping you (or your friend): it is the practicality. The whole approach is wrong, Starting with "Analysis" which most people would describe a the last part in a system to derive "Purchas Orders" is the proverbial "cart before the horse".

    Before you go any further, have a search on the web to see if there any free suitable Excel templates for your friend's business.

  15. #15
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hi John Yes I understand your point. I mentioned the point around my friend because I know alot more around Excel than him and have gone through countless excel templates - which is why we decided on this spreadsheet.

    Its basically nearly there all we need is this last bit where we can connect the order numbers on both sheets and condense the item data across many fields to lesser columns on the Purchase Order sheet.

    Again anyone else out there who can help me with this.

    Kind Regards

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Work in Progress.

    All done from column A to column Q.

    1. Where does the entry in column R come from?

    2. Explain again what you want in columns R to U...

    3. Make sure spelling of column Headers are IDENTICAL in both sheets
    Attached Files Attached Files
    Glenn



  17. #17
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Glen you truly are a superstar this is fantastic.

    So Entries from R to U in the Purchase Order sheet go as follows.

    R (PAYMENT TOTAL ORDER COST) - That basically adds all the total product costs in column Q and only shows the total on the last line for that purchase order number. So for example, on the Purchase Order sheet - R would only show a value on the last purchase order row which in the sample spreadsheet is row 6. Could you do a formula for that?


    S (Customer Paid) - again like the R - this would pull the data in from column BK which is named the same - basically bringing over the amount the customer has paid and only present it on the last row of the purchase order number again which on the PURCHASE ORDER sheet would be row 6.

    T (Payment Oustanding) - need a formula which would subtract payment total order cost (column R) from payment - customer paid (column S) and then only show the number/result on the last row for that particular purchase order number which would be row 6 in this case on the example.

    U (Payment left) - would like to have another column if possible with a formula for this column I wanted to have a formula which would basically write out text which would indicate that if there was still money still owed by the customer (payment total order cost - payment-customer paid) it would mark paid not paid - again on the last line of the for the purchase order number which would be row 6 on the spreadsheet.


    Again many thanks for this Glenn - if you can help with those last columns I will be playing around with it over the next few days and let you know how I get on.

    Many thanks Again.

    Kind Regards

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    hionman, One of our senior members just caught this.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.



    (Note: this requirement is not optional. No help to be offered until the link is provided.)


    Link posted. Help can resume.
    Last edited by FlameRetired; 07-05-2021 at 06:47 PM.
    Dave

  19. #19
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hi

    My apologies I didnt know I couldnt do that.

    I have added the link here for the post on the other forum.

    https://www.mrexcel.com/board/thread.../#post-5717276

    Hope that resolves it.


    Kind Regards

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Quote Originally Posted by hionman View Post
    Hi

    My apologies I didnt know I couldnt do that.
    Please read the Forum Rules which we all agree to upon becoming members (blue bar at the top of the page).

  21. #21
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Hi

    I have read through it and thanks for making me aware.

    Can I still get help on this as some parts are still outstanding thanks.

  22. #22
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Yes.___________________

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    Final version.

    Next time, remember to declare cross-posts up front. It is EXTREMELY vexing if you spend a LOT of time working on something (as I did on this, yesterday) only to find that has already been solved elsewhere. OK, this didn't happen on this occasion... but do bear it in mind.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2019
    Posts
    43

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    You are an absolute Legend Glenn.

    Apologies again - didn't know the rules previously but will make sure I will only post here in the future.

    Many thanks again.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,629

    Re: Formula to automatically copy select row data across multiple columns into new sheet

    You still don't understand the rules, though!

    There is no rule against posting on multiple forums, however if you do, you have to declare it with a link. The same rule exists of all Excel forums I know.

    Also, when you joined, you ticked a box to say that you agreed to abide by the rules, therefore we should be able to assume that you had at least read them! Ours are pretty simple, so I suggest you familiarise yourself with them right now: https://www.excelforum.com/forum-rul...rum-rules.html

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Last edited by AliGW; 07-06-2021 at 04:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatically Copy Cell Data From Multiple Sheet to a Summary Sheet
    By Chris1976 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2020, 01:58 AM
  2. How to select multiple columns with the identical label automatically
    By nazaninibb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2018, 10:35 AM
  3. [SOLVED] Automatically Copy Data from Multiple Sheets to a Master Sheet
    By Ryan_Brandt in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-10-2014, 06:45 AM
  4. Replies: 0
    Last Post: 04-01-2014, 08:03 AM
  5. Macro to copy 3 columns of data from multiple sheets into 3 columns on 1 sheet
    By bballdcm2007 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2012, 04:37 PM
  6. Formula to automatically move/copy data from rows to columns
    By thchughesmathews in forum Excel General
    Replies: 0
    Last Post: 02-03-2012, 06:10 PM
  7. Create copy of multiple worksheets and automatically fill in data from a main sheet
    By NMChemEng in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2008, 05:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1