+ Reply to Thread
Results 1 to 16 of 16

Return Value Based on Several Criteria in Several Identically Formatted Data Sets

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Good morning Excel Community,

    I have been stumped on this for several days/weeks now and joined this forum to hopefully gain some insight. The attached Workbook is clean and shows the details of my problems. I am trying to create a single list based on multiple criteria evaluated amongst multiple datasets. I am able to return the values needed using index/match formulas and turn it into a list using MIN(IF(ISNA(MATCH(ROW functions, but only when I'm using a single dataset (Review Section). I have been unsuccessful in converting the formulas to evaluate all the review sections and create a list as a result. My actual Workbook contains perhaps 100 datasets and I am trying to create fillable formulas that will create the list shown in the "Discrepancies" Tab while analyzing every single data set from the "NPM Cigarette Invoice Review" Tab.

    Your help is greatly appreciated.

    Nick
    Last edited by ndudley26; 09-25-2018 at 02:20 PM.

  2. #2
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    To provide further clarification, I have found perhaps an easier way of posing part of my question. To attain the desired reference, I would need to be able to extract the ROW where the AND() array formula is used to compare two columns. If I could get the row numbers, then I could reference those rows and offset the columns using the user-input invoice numbers to get the necessary results. The information is RIGHT THERE as Excel tracks the information as a part of the array formula, but I don't know how to get to it. Let me know if this helps at all.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    ndudley26 welcome to the forum.

    I'll keep working on this. So far this array formula returns the relevant Invoice numbers in 'Discrepancies'. See if it gives you something to work with in the meantime.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit I failed to mention that the above formula references two helper cells in C2:D2 of 'Discrepancies'. I needed them to match the headers in the source page.
    C
    D
    2
    Claim
    Review
    Last edited by FlameRetired; 09-25-2018 at 11:01 PM.
    Dave

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    In the attached B12:O12 in the source data sheet find this helper row formula. It's there to address the merged invoice number cells.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In 'Discrepancies' column B
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Column C
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Column D
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Thank you, Dave. This is a great starting point. There are a few problems I'll have with this approach, one being that oftentimes the Invoice Numbers can include a string of text, and these formulas won't work in that case. I am also looking towards eliminating the helper row in the 12th row for address the merged invoice cells. Perhaps there may be a workaround with that by using offset. I'm diving into what you've given me and I'll try to work through these things. Let me know if you think of a way to address those issues.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    I was working on this last night (different approach from Dave's), but couldn't get the final formula to work before tiredness overcame me.

    If, as you say, your real workbook is different than the one you submitted (you talk about removing the merged cells, and of invoice numbers containing text), then it would be useful to attach a sample workbook which is more representative of your real file.

    Pete

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    ndudley thank you for the feedback. I was waiting to hear.

    I must agree with Pete on all points.

    That 1st part ... listing the invoice numbers ... was a challenging bottleneck. I didn't notice until the final stages of building the formula that there are formulas in the Review columns that result in "". If any of those are present elsewhere in the real data please include those also.

    Additionally
    Workbook contains perhaps 100 datasets and I am trying to create fillable formulas that will create the list shown in the "Discrepancies" Tab while analyzing every single data set from the "NPM Cigarette Invoice Review" Tab.
    ... suggests that dynamic named ranges and formulas are advised. Will know more with a representative upload.

  8. #8
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Hello Pete, Thanks for taking the time to work on this. Your help is greatly appreciated. I have attached a skeleton of my Workbook. All confidential information and formulas that have reference to confidential information have been removed. The end goal is to populate the list that Dave created in the "Cover Sheet" worksheet (B15:F55), as indicated by the column headers. The data for populating this would be located in the "NPM Cigarette Invoice Review" worksheet, very similar to what he created in his first solution. However, I'm looking to account for invoices that contain letters as well as numbers. Some samples of invoice number formats are indicated in Cells A14:A57 on the "NPM Cigarette Invoice Review" worksheet. I'm trying to keep all "helper rows" or "helper data" in the "Discrepancies" tab, and use that data to pull and auto populate the lists in the "Cover Sheet" worksheet. Again, thank you to both you and Dave for the assistance and let me know if there is any other information I can provide.

  9. #9
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Hi Dave, thanks for your response. Sorry I didn't see it prior to submitting my skeleton Workbook. It's safe to say that everything in the workbook with GREY Formatting indicates formulas that could result in "". I'm sorry for not leaving in all the formulas I have as many of them contain pieces of information that are confidential.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Your file in Post #8 is quite a bit different than the one in Post #1. You seem to be asking now for the list to be produced in the Cover sheet, whereas before it was in the Discrepancies sheet. I'm not really sure what the Discrepancies sheet is for now, other than to list all the invoices and then to mark in another column whether to include them or not (which could be achieved by just leaving the unwanted ones out of the list).

    You also say that you would like helper columns to be confined to the Discrepancies sheet, but the approach I was working on made use of helpers in the empty columns of the NPM Cigarette Invoice Review sheet (i.e. columns H, L, P, T etc.) to identify records where there were discrepancies AND where the invoice was to be included in each 3-column invoice block. This would not be very practical to be put in the Discrepancies sheet.

    I'll wait for you to get back before doing any more work on it.

    Pete

  11. #11
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Hi Pete, great questions.

    1) The discrepancies sheet uses different formulas to pull different invoices based on the type of error/discrepancy identified in the review. That makes it easier to write notes and identify pieces of information on the "Cover Sheet" page and identifying action items to take with each invoice based on what column the discrepancies are located in. All the discrepancy invoices are included on the "Cover Sheet".

    2) The "NPM Cigarette Invoice Review" is going to be worked on by several users and over time I don't trust that any additional formulas in empty spaces will go untouched. I've been trying to keep the spaces as clean, empty, lean, and user-friendly as possible in the "NPM Cigarette Invoice Review" and "Cover Sheet" while using any helper rows or columns in the "Discrepancies" tab which will be hidden in the end game. The results of the formula will ultimately be in the "Cover Sheet" but the actual formulas are preferred to be in the "Discrepancies" tab and just reference any maintenance work on the "Discrepancies" tab. That way if the formatting ever changes or additional information is required I won't have to touch the formulas in the "Cover Sheet" and I won't do anything in the "Discrepancies" tab besides add the new pieces of information.

    I hope that makes sense. End game, I was hoping to somehow be able to make a list of discrepancies for the "Brand", "Filing", and "Invoice" for the referenced discrepancy invoices that will be on the "Discrepancies" tab. I would then make reference to that list in the "Cover Sheet". Let me know your thoughts.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Presently the source data range is E9:EQ22 in 'NPM Cigarette Invoice Review'. It is my understanding that the number of columns can and will change. Can the same be said of the number of rows?

    If that is changeable can the section of Brand/Boxes/Total below that be moved elsewhere? I am entertaining ideas of dynamic named ranges, and the presence of that section below the data (or to the right of it) can thwart that approach.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    I decided to pick this up again, and try to confine everything to the Discrepancies sheet, as requested. Consequently, there is a yellow area from column AA onwards which identifies records which have a discrepancy AND where the invoice is to be included (based on TRUE in column F), and allocates a unique sequential number to each such record. If you have more invoices than the 36 in your file, then just copy column BK further to the right (you might need to adjust the ranges of some of the other formulae though).

    To the left of the yellow area is an orange area, with sequential numbers in column W, and then where the sequential number occurs in the block (i.e. column) and the row on which it occurs.

    The blue area then gives you the data that you requested, i.e. a list of the brands where there are discrepancies, against appropriate invoices. If you have more reported discrepancies, you can copy the formulae in R22:Y22 down as required.

    I presume you will be able to generate the information on the Cover sheet from this.

    Let me know if you need explanations on any of the formulae.

    Hope this helps.

    Pete

  14. #14
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Thank you Pete. This is excellent. I'm running some tests right now but so far this seems to be precisely what I was needing.

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Glad to hear it, and thanks for the rep. I was beginning to think that you hadn't noticed that I had posted it.

    If you conclude that it has answered your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Pete

  16. #16
    Registered User
    Join Date
    06-28-2018
    Location
    Tempe, Arizona
    MS-Off Ver
    2010
    Posts
    10

    Re: Return Value Based on Several Criteria in Several Identically Formatted Data Sets

    Will do. Thanks again and take care.

+ 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] Return Value based on Two Dynamic Sets of Criteria (Non-VBA)
    By derrickb0690 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2018, 05:00 PM
  2. Bring in data from one tab based on two sets of criteria on a different tab
    By tommypkoch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2013, 02:00 PM
  3. [SOLVED] VBA to paste 2 sets of formatted data into one sheet
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2012, 06:40 AM
  4. Extracting data from several identically-formatted webpages
    By zuri125 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2011, 02:13 PM
  5. Counting data sets based on multiple criteria
    By hjb0802 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2010, 10:47 AM
  6. [SOLVED] data return based on two criteria
    By Jimmy Joseph in forum Excel General
    Replies: 3
    Last Post: 06-09-2006, 09:40 AM
  7. Replies: 1
    Last Post: 05-18-2005, 06:06 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