Arrays: Combining Lists by its Date

[toongal12]

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"

[leelnich]

Hi, toongal12- A small sample workbook (NOT a picture) helps us to solve your problem quickly and accurately.
(Many of our contributors don't have the time or inclination to manually copy data from thread posts.)
Remove any sensitive or extraneous info, we just need some “realistic” data for testing purposes.
Please include examples of any variations the code or formula must address...
...and simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

[toongal12]

Uploaded the example

[toongal12]

bumpbumpbump

[leelnich]

Hey, sorry for the delay... Try this modification to your ARRAY FORMULA*. Paste in A37 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

[toongal12]

Hey, sorry for the delay... Try this modification to your ARRAY FORMULA*. Paste in A37 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

It didn't change anything when I entered as array :o

[leelnich]

I'm guessing you didn't remove "(blank)" from the relevant date cell. The formula as written just rejects EMPTY cells. You could also use a different test in the new clause:
*(COUNTIFS($A$4:$A$18,$A$30:$A$33,$B$4:$B$18,">0"))

[toongal12]

That works! I think some did have spaces in them

How would you do the opposite and display items that did not have a date? I can't figure out what to put in the part where you red highlighted to say "isblank"

[toongal12]

I tried using *(COUNTIFS($A$4:$A$18,$A$30:$A$33,$B$4:$B$18," ")), but it doesn't like it even though there is a space (" ") in the cell

[toongal12]

I did a test and the cells ="", but when i put *(COUNTIFS($A$4:$A$18,$A$30:$A$33,$B$4:$B$18,"")) still doesn't see it

[leelnich]

"" worked for me, but it won't catch " ". Without your data in front of me, I'm just guessing. If you can't upload it, how about:
In an empty column next to your data, paste and copy down:
= isnumber(B2)
Now Filter on False in the new column and then click on the date column drop-down to see a list of unique values in that column. Maybe you can spot the problem.

(I could PM you my personal e-mail if you don't want to upload publicly.)