Results 1 to 11 of 11

Arrays: Combining Lists by its Date

Threaded View

  1. #1
    Registered User
    Join Date
    02-02-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Arrays: Combining Lists by its Date

    This is going to be a little long winded, but I hope I explain it well. I am stuck at the last part of my code, but I will explain what I have done so far. I want to display a list of Items that are associated with a date, while removing and adding other items.

    There are 4 lists:
    An original (Raw Data) list
    A modified list of items (Old Items) that had a date in the B column of the previous list (Raw Data)
    A list of items that should be removed (Removed Items)
    A list of items to add (Add Items)

    I have the original "Raw Data" for a year, say 2017. These are items in column A that have dates and have blank cells in column B
    I have the data set for 2017 of the "Old items". These items had a date in the "Raw Data" table. Cells that had blank cells in the date column were ignored.
    I have the "Removed items" that need to be removed from the "Old Items".
    I have the "Add items" that need to be added to the "Old Items" list. These may be duplicates.

    So the final table will have the "Old Items" that had a date in the date column of "Raw Data", items removed by "Removed Items" and items added by "Add Items".

    "Raw Data" of 2017. I added the ........... to expand list of items. There are items between it.
    A3: Name of Item B3: Last Update
    A4: Item-101 B4: 1/1/2009
    ....................(Random Items) ....................(these cells can be blank)
    A17: Item-001 B17: 2/2/2014
    A18: Item-055 B18: (blank)


    "Old Items" from "Raw Data". These had a date in the B column above. B20: "2017" is the year being called (used in formula down below)
    A21: Item-001
    A22: Item-009
    A23: Item-150
    A24: Item-290
    A25: Item-095
    A26: Item-395

    "Removed Items". I bolded the "Removed Items" to be seen clearly in the "Old Items" for viewing of the example. There are no patterns.
    C21: Item-009
    C22: Item-290
    C23: Item-395

    "Added Items". The year in the B column shows the year they are added to. Years are in no pattern.
    A30: Item-101 B30: 2017
    A31: Item-001 B31: 2017
    A32: Item-055 B32: 2017
    A33: Item-900 B33: 2018

    What I get now Final Table I have after "Removed" and "Added" from "Old Items"
    A37: Item-001
    A38: Item-150
    A39: Item-095
    A40: Item-101
    A41: Item-055

    My code in A37:
    =IFERROR(INDEX(A$21:A$26,SMALL(IF(A$21:A$26<>"",IF(ISNA(MATCH(A$21:A$26,C$21:C$23,0)),ROW(A$21:A$26)-ROW(A$21)+1)),ROWS(A$37:A37))),
    IFERROR(INDEX($A$30:$A$33,SMALL(IF($A$30:$A$33<>"",IF($B$30:$B$33=B$20,IF(ISNA(MATCH($A$30:$A$33,A$21:A$26,0)),ROW($A$30:$A$33)-ROW($A$30)+1))),ROWS(A$37:A37)-SUMPRODUCT(--(A$21:A$26<>""),--(ISNA(MATCH(A$21:A$26,C$21:C$23,0)))))),""))

    It is entered as an array and dropped down.

    The first IFERROR compares the "Old Items" and "Removed Items" and outputs the difference in the two lists.
    The second IFERROR is adding the "Add Items" that are for 2017.
    The SUMPRODUCT makes sure when going through each iteration to ignore the first part or else it will error with the second IFERROR.

    The bolded part is the second part where the trouble is. The first part works fine

    -----------------------------------------------------------------

    So the problem is that all items for 2017 in "Add Items" are added to "Old Items". The Item-055 should not be added because in the "Raw Data", it has a blank cell in column B for Last Update. Only items that have a date should be added.

    So the true result should be:
    A37: Item-001
    A38: Item-150
    A39: Item-095
    A40: Item-101

    I know adding IF(ISNUMBER(B$4:B$18) to the second IFERROR will allow it to read that there is a date in "Raw Data" as opposed to the blank cell. But not sure how to append it to the Item in the "Raw Data" to the Item in the "Add Item"
    Last edited by toongal12; 08-24-2017 at 01:47 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combining two lists in two equal lists
    By Excell1677 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-24-2016, 06:51 PM
  2. Combining lists
    By anakaine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2013, 06:16 PM
  3. [SOLVED] How would one create a single list of #'s (array) from multiple lists (arrays)?
    By Rstrand in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2013, 07:32 PM
  4. Combining two Arrays with different criteria's
    By Shaner73 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2012, 08:24 AM
  5. Combining lists
    By EdMac in forum Excel General
    Replies: 3
    Last Post: 09-01-2006, 06:55 AM
  6. [SOLVED] combining two lists
    By karmaisgreat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2006, 04:20 PM
  7. Combining 2 lists
    By stevenrhonda in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-05-2006, 06:50 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