+ Reply to Thread
Results 1 to 10 of 10

Merging two sets of data into one output

  1. #1
    Registered User
    Join Date
    05-08-2005
    Location
    Glasgow
    MS-Off Ver
    Microsoft 365 Version 2110
    Posts
    7

    Merging two sets of data into one output

    Hello

    I have two lists of data which I am trying to combine as one output, this is basically a list of orders I have which I am looking to match up with relevant jobs.

    I have attached an example spreadsheet showing what I am looking for, the list marked as Data 1 shows all the relevant PO's with the part numbers, quantities and dates.

    The list marked as Data 2 shows all the jobs which are required with their relevant quantities.

    The list marked as Output is what I am looking to achieve automatically, this shows the Data 1 & 2 merged together to show which PO & quantity goes to each jobs, the blanks show as excess stock from the previous PO.

    Hopefully I've explained this correctly.

    thanks in advance.
    Attached Files Attached Files
    Last edited by dougie8rown; 12-03-2021 at 07:18 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Lookup Help

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

    Administrative Note:

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    Thank you for helping us to help you.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Merging two sets of data into one output

    Thank you for the title update.

    Please address the two requestes regarding your forum profile.

  4. #4
    Registered User
    Join Date
    05-08-2005
    Location
    Glasgow
    MS-Off Ver
    Microsoft 365 Version 2110
    Posts
    7

    Re: Merging two sets of data into one output

    Apologies, I have updated accordingly.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Merging two sets of data into one output

    That's great - thanks.

    I have looked at your workbook and the obvious way would be PowerQuery. However, Excel will not know which rows to match because there is only one link point (part number) which, on its own, is not enough. You need something else in both tables in order to link these correctly (order number or job due). Quantity won't do as it does not always match.

  6. #6
    Registered User
    Join Date
    05-08-2005
    Location
    Glasgow
    MS-Off Ver
    Microsoft 365 Version 2110
    Posts
    7

    Re: Merging two sets of data into one output

    Thanks for coming back to me, it doesn't matter which PO goes to which job as long as the PO date is before the job due date as the PO would have to be received to allow the job to hit the job date.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Merging two sets of data into one output

    The problem is that Excel will match it every which way! Excel NEEDS logic. If you say "match it to one but it doesn't really matter which", Excel will be confused. It cannot make those decisions for you.

    You need another matching element in the data 1 table to make this work.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Merging two sets of data into one output

    To illustrate my point, here's what Excel will assume without that second matching reference:

    AliGW on MS365 Insider (Windows) 64 bit
    K
    L
    M
    N
    O
    P
    Q
    14
    Part Number
    Order No
    Qty
    PO Due
    Job No
    Qty.1
    Job Due
    15
    ABC123
    12444
    100
    01/01/2022 00:00
    100
    50
    15/01/2022 00:00
    16
    ABC123
    12444
    100
    01/01/2022 00:00
    101
    50
    01/02/2022 00:00
    17
    ABC123
    12444
    100
    01/01/2022 00:00
    102
    50
    15/02/2022 00:00
    18
    ABC123
    12444
    100
    01/01/2022 00:00
    103
    50
    15/03/2022 00:00
    19
    ABC123
    12555
    50
    01/02/2022 00:00
    102
    50
    15/02/2022 00:00
    20
    ABC123
    12555
    50
    01/02/2022 00:00
    103
    50
    15/03/2022 00:00
    21
    ABC123
    12666
    50
    01/03/2022 00:00
    103
    50
    15/03/2022 00:00
    22
    DEF456
    11111
    100
    01/12/2021 00:00
    104
    50
    15/12/2021 00:00
    23
    DEF456
    11111
    100
    01/12/2021 00:00
    105
    50
    01/01/2022 00:00
    24
    DEF456
    11111
    100
    01/12/2021 00:00
    106
    50
    15/02/2022 00:00
    25
    DEF456
    11222
    100
    01/02/2022 00:00
    106
    50
    15/02/2022 00:00
    26
    DEF456
    11111
    100
    01/12/2021 00:00
    107
    50
    01/03/2022 00:00
    27
    DEF456
    11222
    100
    01/02/2022 00:00
    107
    50
    01/03/2022 00:00
    28
    GHI789
    33444
    50
    01/03/2022 00:00
    108
    50
    15/03/2022 00:00
    Sheet: Sheet1

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Merging two sets of data into one output

    Can you explain/justify your expected answers in the orange cells? Otherwise... is this close??

    =IF(AND(COUNTIF($F$4:$F4,$F4) > 1,SUMIFS($H$4:$H4,$F$4:$F4,$F4)< SUMIFS($C:$C,$A:$A,$F4,$D:$D,"< ="&$I4)),"",INDEX(B:B,AGGREGATE(14,6,ROW($B$4:$B$9)/(($A$4:$A$9=$F4)*($D$4:$D$9< =$I4)),1)))

    copied across and dnown.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  10. #10
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,011

    Re: Merging two sets of data into one output

    Got the formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Replies: 25
    Last Post: 09-17-2020, 02:30 PM
  2. [SOLVED] Lookup: Lookup employee id and return value in cell x basued on most recent start date
    By jekeith in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-21-2018, 12:56 PM
  3. Two Lookup and Return (Lookup the column, then lookup the row)
    By Branbran10 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-05-2015, 04:55 PM
  4. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  5. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  6. Replies: 2
    Last Post: 05-19-2013, 08:46 AM
  7. Replies: 7
    Last Post: 06-19-2011, 12:51 PM

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