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 "Old" items that have been upgraded previously that are ready for their next upgrade. There are "New" items that are fresh without any upgrades done in the past. For the lists below, I only focus on the items that have been upgraded before ("Old" items).
So I have several lists. I have the main data set, of all the raw data for that year, say 2017. Then I have the data set for 2017 of the "Old" items. The next list is "Removed" items that need to be removed from getting an upgrade for whatever reason. The final list are "Added" items that need to be added onto the "Old" list. These didn't make the cut before, but they are necessary to be done ASAP in this year.
This is the process to getting to the final table. This table is began with the original 2017 table. It takes the "Old" items, excluding the "Removed" items, and adding the "Added" items.
"Raw" Data of 2017. I added the ........... to expand list of items. There are items between it.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A3: Name of Item[/TD]
[TD]B3: Last Update[/TD]
[/TR]
[TR]
[TD]A4: Item-101[/TD]
[TD]B4: 1/1/2009[/TD]
[/TR]
[TR]
[TD]....................[/TD]
[TD]..............[/TD]
[/TR]
[TR]
[TD]A17: Item-001[/TD]
[TD]B17: 2/2/2014[/TD]
[/TR]
[TR]
[TD]A18: Item-055[/TD]
[TD]B18: (blank)[/TD]
[/TR]
</tbody>[/TABLE]
This table composes of the "Old" items taken "Raw" Data. "2017" is the year being called, and "Removed" items are to be excluded later. I bolded the "Removed" items to be seen clearly in the "Old" items for viewing of the example. There are no patterns, and items are chosen.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A20: "Old" items from "Raw" Data[/TD]
[TD]B20: 2017[/TD]
[TD]C20: "Removed" Items[/TD]
[/TR]
[TR]
[TD]A21: Item-001[/TD]
[TD][/TD]
[TD]C21: Item-009[/TD]
[/TR]
[TR]
[TD]A22: Item-009[/TD]
[TD][/TD]
[TD]C22: Item-290[/TD]
[/TR]
[TR]
[TD]A23: Item-150[/TD]
[TD][/TD]
[TD]C23: Item-395[/TD]
[/TR]
[TR]
[TD]A24: Item-290[/TD]
[TD][/TD]
[TD]C24: [/TD]
[/TR]
[TR]
[TD]A25: Item-095[/TD]
[TD][/TD]
[TD]C25: [/TD]
[/TR]
[TR]
[TD]A26: Item-395[/TD]
[TD][/TD]
[TD]C26: [/TD]
[/TR]
</tbody>[/TABLE]
This table is the "Added" lists. The year shows what year they should be added to. Years are in no pattern.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A29: "Added" Items[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A30: Item-101[/TD]
[TD]B30: 2017[/TD]
[/TR]
[TR]
[TD]A31: Item-001[/TD]
[TD]B31: 2017[/TD]
[/TR]
[TR]
[TD]A32: Item-055[/TD]
[TD]B32: 2017[/TD]
[/TR]
[TR]
[TD]A33: Item-900[/TD]
[TD]B33: 2018[/TD]
[/TR]
</tbody>[/TABLE]
Final Table I have after "Removed" and "Added"
[TABLE="width: 500"]
<tbody>[TR]
[TD]A37: Item-001[/TD]
[/TR]
[TR]
[TD]A38: Item-150[/TD]
[/TR]
[TR]
[TD]A39: Item-095[/TD]
[/TR]
[TR]
[TD]A40: Item-101[/TD]
[/TR]
[TR]
[TD]A41: Item-001[/TD]
[/TR]
[TR]
[TD]A42: Item-055[/TD]
[/TR]
</tbody>[/TABLE]
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,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" list and "Removed" list and outputs the difference in the two lists.
The second IFERROR is adding the "Added" items. The SUMPRODUCT makes sure when going through each iteration to ignore the first part or else it will error with the second IFERROR.
--------------------------------------------------------------
Now comes to my issues. The "Added" list may contain duplicates. Other parts of the programs reads this list as priority and ranks it higher. When I try to remove the duplicates, I added: IFERROR(INDEX($A$22:$A$25,SMALL(IF($A$22:$A$25<>"",IF($B$22:$B$25=C$12,IF(ISNA(MATCH(0,COUNTIF(A$10:A$15,$A$22:$A$25),0)). It leaves me with the list after the "Removed" Items are taken away (Item-001, 150, and 095). So it takes away the added, which need to be included.
Also, the code needs to know to only add items that are "Old" from the raw data. Right now, it adds everything. From my exmaple, for the "Old" items, it can only add Item-101 and Item-001. Item-055 has to be added when the "New" list is constructed. My first attempt was: IFERROR(INDEX($A$30:$A$33,SMALL(IF($A$30:$A$33<>"",IF($B$30:$B$33=B$20,IF(ISNUMBER(B$7:B$18). But it couldn't connect the item name to the date. I tried using "&" to append the item name to the date, but it didn't seem to work either.
So Overall issues:
> Remove a duplicate, if it appears
> Only make list of "Old" items, for this list.
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 "Old" items that have been upgraded previously that are ready for their next upgrade. There are "New" items that are fresh without any upgrades done in the past. For the lists below, I only focus on the items that have been upgraded before ("Old" items).
So I have several lists. I have the main data set, of all the raw data for that year, say 2017. Then I have the data set for 2017 of the "Old" items. The next list is "Removed" items that need to be removed from getting an upgrade for whatever reason. The final list are "Added" items that need to be added onto the "Old" list. These didn't make the cut before, but they are necessary to be done ASAP in this year.
This is the process to getting to the final table. This table is began with the original 2017 table. It takes the "Old" items, excluding the "Removed" items, and adding the "Added" items.
"Raw" Data of 2017. I added the ........... to expand list of items. There are items between it.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A3: Name of Item[/TD]
[TD]B3: Last Update[/TD]
[/TR]
[TR]
[TD]A4: Item-101[/TD]
[TD]B4: 1/1/2009[/TD]
[/TR]
[TR]
[TD]....................[/TD]
[TD]..............[/TD]
[/TR]
[TR]
[TD]A17: Item-001[/TD]
[TD]B17: 2/2/2014[/TD]
[/TR]
[TR]
[TD]A18: Item-055[/TD]
[TD]B18: (blank)[/TD]
[/TR]
</tbody>[/TABLE]
This table composes of the "Old" items taken "Raw" Data. "2017" is the year being called, and "Removed" items are to be excluded later. I bolded the "Removed" items to be seen clearly in the "Old" items for viewing of the example. There are no patterns, and items are chosen.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A20: "Old" items from "Raw" Data[/TD]
[TD]B20: 2017[/TD]
[TD]C20: "Removed" Items[/TD]
[/TR]
[TR]
[TD]A21: Item-001[/TD]
[TD][/TD]
[TD]C21: Item-009[/TD]
[/TR]
[TR]
[TD]A22: Item-009[/TD]
[TD][/TD]
[TD]C22: Item-290[/TD]
[/TR]
[TR]
[TD]A23: Item-150[/TD]
[TD][/TD]
[TD]C23: Item-395[/TD]
[/TR]
[TR]
[TD]A24: Item-290[/TD]
[TD][/TD]
[TD]C24: [/TD]
[/TR]
[TR]
[TD]A25: Item-095[/TD]
[TD][/TD]
[TD]C25: [/TD]
[/TR]
[TR]
[TD]A26: Item-395[/TD]
[TD][/TD]
[TD]C26: [/TD]
[/TR]
</tbody>[/TABLE]
This table is the "Added" lists. The year shows what year they should be added to. Years are in no pattern.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A29: "Added" Items[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A30: Item-101[/TD]
[TD]B30: 2017[/TD]
[/TR]
[TR]
[TD]A31: Item-001[/TD]
[TD]B31: 2017[/TD]
[/TR]
[TR]
[TD]A32: Item-055[/TD]
[TD]B32: 2017[/TD]
[/TR]
[TR]
[TD]A33: Item-900[/TD]
[TD]B33: 2018[/TD]
[/TR]
</tbody>[/TABLE]
Final Table I have after "Removed" and "Added"
[TABLE="width: 500"]
<tbody>[TR]
[TD]A37: Item-001[/TD]
[/TR]
[TR]
[TD]A38: Item-150[/TD]
[/TR]
[TR]
[TD]A39: Item-095[/TD]
[/TR]
[TR]
[TD]A40: Item-101[/TD]
[/TR]
[TR]
[TD]A41: Item-001[/TD]
[/TR]
[TR]
[TD]A42: Item-055[/TD]
[/TR]
</tbody>[/TABLE]
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,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" list and "Removed" list and outputs the difference in the two lists.
The second IFERROR is adding the "Added" items. The SUMPRODUCT makes sure when going through each iteration to ignore the first part or else it will error with the second IFERROR.
--------------------------------------------------------------
Now comes to my issues. The "Added" list may contain duplicates. Other parts of the programs reads this list as priority and ranks it higher. When I try to remove the duplicates, I added: IFERROR(INDEX($A$22:$A$25,SMALL(IF($A$22:$A$25<>"",IF($B$22:$B$25=C$12,IF(ISNA(MATCH(0,COUNTIF(A$10:A$15,$A$22:$A$25),0)). It leaves me with the list after the "Removed" Items are taken away (Item-001, 150, and 095). So it takes away the added, which need to be included.
Also, the code needs to know to only add items that are "Old" from the raw data. Right now, it adds everything. From my exmaple, for the "Old" items, it can only add Item-101 and Item-001. Item-055 has to be added when the "New" list is constructed. My first attempt was: IFERROR(INDEX($A$30:$A$33,SMALL(IF($A$30:$A$33<>"",IF($B$30:$B$33=B$20,IF(ISNUMBER(B$7:B$18). But it couldn't connect the item name to the date. I tried using "&" to append the item name to the date, but it didn't seem to work either.
So Overall issues:
> Remove a duplicate, if it appears
> Only make list of "Old" items, for this list.
