+ Reply to Thread
Results 1 to 11 of 11

Creating 1 list from 2 sheets based on set of conditions

  1. #1
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Question Creating 1 list from 2 sheets based on set of conditions

    Please don't worry about names and such being mentioned inside the worksheets. All of the personal information in the sheets has been fictionalized so I could upload it here along with my question.

    At the moment I am trying to create a single list/overview of orders that have had their "Pick Line Created" before 19:05 on any given day and will be shipped through DHL but weren't marked "Ready For Shipment" on the same day. The shipment method conditional is based on column F (Shipment Method) of the Order Inquiry file. The status of the order is based on column E (TransactionType) and F (TransactionDate) combined, both shown in the Transactions export. Preferably, this new overview would only show the products for each order for which these conditions are met in case of partial fulfillment.

    Column N (Order ID) in the Transactions file corresponds to column M (Order ID) in the Order Inquiry file. Similarly, column H (Product ID) in the Transactions export corresponds to column T (Product5) in the Order Inquiry export.

    I've also added an example of the information I'd ideally get from this new overview. Just to clarify, order 5394545 would be excluded since DHL is not mentioned within the Shipment Method column in the Order Inquiry file. Similarly, orders 5428798 and 5433397 are excluded because both have a "Ready For Shipment" status on the same day they had their "Pick Line Created" for every single product ID within the order. Both 5427747 and 5430880 only show the relevant product IDs (when expanded) because some product lines did have a "Ready For Shipment" status on the same day.

    If any more info is required to be able to answer the question or put me into the right direction, please let me know and I'll do so as soon as possible. Thanks in advance to anyone that was able to help me with this.

  2. #2
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Creating 1 list from 2 sheets based on set of conditions

    Unfortunately no help here so cross-posting this on Mr Excel. Was hoping I would have at least been pointed into the right direction if a cookie-cutter solution proved to be too difficult and/or elaborate, which I could imagine. Mostly wondering if I need VBA for this or whether there are other Excel tools that could help me create the needed overview.

  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,409

    Re: Creating 1 list from 2 sheets based on set of conditions

    I'm sorry that you think that no help in under 24 hours is not good enough on a free forum, especially as you seem to have been expecting responses between the end of the working day and breakfast the next morning. However, that's your prerogative, so thanks for telling us.

    I have looked on Mr Excel with a view to posting a link here (as per our forum rules), but can't find your post. Maybe you discovered that you can't post attachments over there? Please let us know if you have posted there or elsewhere. Thanks.
    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.

  4. #4
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Creating 1 list from 2 sheets based on set of conditions

    I'm sorry and I didn't mean it that way, this is also coming from my experience with others forums on unrelated subjects where generally a thread is bound to be forgotten by the avalanche of new threads if there has been no activity after the first 12-14 hours. I probably should have bumped it in another way then.

    Correct, I tried to post on Mr Excel but I couldn't attach my files. I tried their plugin but it's hard for me to condense one of the exports any further and it will probably just overburden the post when being viewed inside the forum overview.

    Not sure if it's okay to post towards possible solutions for the issue at hand and the possible avenues already being considered? I think I've read that it's undesired that the person posing the question also suggests a possible solution. Nonetheless, I've been looking at Power Pivot to help me fix this, was hoping to avoid having to learn VBA but if that's the only way then so be it.

  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,409

    Re: Creating 1 list from 2 sheets based on set of conditions

    OK. Let me address each of your comments.

    1. Bumping a thread once a day is fine - however, you need to manage your expectations. If you require URGENT help, then consider paying an agency to do the work for you. Your wording was, sorry to say, quite churlish ("You haven't been able to solve this for me overnight because it was too hard for you, so I'm going to go elsewhere") - could have been a language issue, however this isn't a great first impression. We'll put it down to inexperience and naivety rather than impatience.
    2. So, you haven't posted on Mr Excel - correct?
    3. Yes, it's perfectly OK to tell us what you have considered and/or tried - in fact, it's positively encouraged here.

    So, let's start again. Add a new post to this thread that addresses my point #3.

  6. #6
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Creating 1 list from 2 sheets based on set of conditions

    1. Clear, I probably could have worded it better but I was simply trying to bump the thread and since I read that cross-posting is heavily discouraged (something I don't really understand honestly) I wanted to abide by that rule simultaneously. Just to clarify, I don't even expect a cookie-cutter solution to my problem at all. I fully understand the question is probably too dense for that to happen, but I'm just completely stuck in solving this within my current Excel capabilities and don't know how to continue.

    2. Correct, I have posted it on the Excel Subreddit a few days back but from that place the only response I got was that I put too much information within my original post and I needed to boil it down a lot in order to realistically get a response at all. This is also partially why I left out my earlier considered solutions thus far. Because of not being able to attach complete Excel files, I'll wait with cross posting to Mr Excel until I really need to.

    3. Thus far I only really tried to create somekind of indicator on which I could then filter the original exports using nested formulas with IF statements for the conditionals and INDEX MATCH (most because I prefer it to VLOOKUP) so I could tie the 2 files together. The problem is that it takes quite a bit of manual labor to do that and I kind of need this overview to be refreshed several times a day. So this is just not the right solution in practice.

    I'm sure you could fix this with the right VBA skills but I have 0 experience with that and I am kind of trying to avoid it because it seems rather daunting and I was hoping there would be an easier and, consindering my lack of experience, more efficient way. During my search I came across Power Pivot and am now researching whether this tool is capable of helping me fix the issue. I'm about to start an entry level class on Business Intelligence and Power BI seems all the rage within that field. Been looking into that as well but again, have no experience using it upto this point. Sorry if I shouldn't mention other software packages here since it's an Excel forum specifically.

  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,409

    Re: Creating 1 list from 2 sheets based on set of conditions

    something I don't really understand honestly
    Well, let me educate you. Please read this: http://www.excelguru.ca/content.php?184

    I have posted it on the Excel Subreddit a few days back
    Administrative Note:

    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.

    (Note: this requirement is not optional. No help to be offered until the link is provided. As you are new, you will need to add one or two spaces to the URL to be able to post the link.)

  8. #8
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Creating 1 list from 2 sheets based on set of conditions

    Quote Originally Posted by AliGW View Post
    Well, let me educate you. Please read this: - removed link because I couldn't post otherwise -
    Read and noted.

    Here is the link to my post on the Excel subreddit: http://www.reddit.com/r/excel/commen..._conditionals/

    I decided to post here when one of the mods there contacted me directly through a personal message and told me he would not be answering my question because it was too long/dense and he didn't expect I'd get an answer from anyone else.
    Last edited by AliGW; 10-06-2021 at 06:07 AM. Reason: PLEASE don't quote unnecessarily!

  9. #9
    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,409

    Re: Creating 1 list from 2 sheets based on set of conditions

    That's great, thanks, and I've made your link clickable.

    Please feel free to post more information here now. Try to make it succinct.

  10. #10
    Registered User
    Join Date
    09-27-2021
    Location
    Eindhoven
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Creating 1 list from 2 sheets based on set of conditions

    Bump for visibility!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Creating 1 list from 2 sheets based on set of conditions

    This is a pretty complicated task so don't be discouraged if there are no quick responses.
    One thing that makes it more complicated is that the Transaction Date column on the Datadump Transactions sheet is a combination of text (most) and actual dates (the last few).
    I would suggest not making the task more complicated by keeping data in separate workbooks, so I have put the data in the same workbook on separate sheets.
    Five columns are added to the data on the Datadump Transactions sheet.
    1. Date is populated using: =IF(ISNUMBER(F2),INT(F2),DATE(2000+MID(F2,7,2),MID(F2,4,2)+0,LEFT(F2,2)+0))
    2. Time is populated using: =IF(ISNUMBER(F2),MOD(F2,1),TIMEVALUE(RIGHT(F2,LEN(F2)-SEARCH(" ",F2))))
    3. Exclude is populated using: =IF(E2<>"Pick Line Created",TRUE,AND(W2<=TIME(19,5,0),SUMPRODUCT((E$2:E$220="Ready For Shipment")*(G$2:G$220=G2)*(N$2:N$220=N2)*(V$2:V$220=V2))))
    4. Reference ID is populated using: =IF(X2=TRUE,"",INDEX(Tabel1[Reference ID],MATCH(N2,Tabel1[Order ID],0)))
    5. LastTransactionOnDate is populated using: =IF(X2=TRUE,"",INDEX(E$2:E$220,AGGREGATE(15,6,(ROW(E$2:E$220)-ROW(E$1))/(W$2:W$220=MAXIFS(W$2:W$220,G$2:G$220,G2,N$2:N$220,N2,V$2:V$220,V2))/(G$2:G$220=G2)/(N$2:N$220=N2)/(V$2:V$220=V2),1)))
    A pivot table is produced on the Blad1 sheet.
    Note that there are some differences between the pivot table and the table in columns B:F. It is late here so I am not going to try and reconcile the differences. If you will explain where the pivot table is producing incorrect results then that may speed up the process of correcting them.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Creating copies of sheets based on a list
    By Peaky_Blinder in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-28-2021, 03:26 AM
  2. [SOLVED] Error handling when creating sheets based on list and hyperlink
    By gv29 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2015, 07:06 PM
  3. Creating groups from a list based on conditions with the maximum amount of variation
    By ShallowJamm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2015, 03:26 PM
  4. Creating separate sheets based on single rows in list?
    By Taxster in forum Excel General
    Replies: 3
    Last Post: 12-16-2014, 04:24 PM
  5. Help creating a worload list for undividual people based on data in different sheets.
    By Clyde Daleton in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-13-2013, 12:31 PM
  6. Creating separate lists from a master list based on conditions
    By kenk99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2010, 10:22 AM
  7. Creating a new list based on conditions
    By Dubbelito in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-05-2008, 03:58 AM

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