Need formula pick out added and removed SKUs

[jonnieRC]

Hi Folks,

Hope somebody can help. I have attached a file with a list of SKU's from one year to the next. I am trying to fogure out what SKU is new in FY20 and what has been removed.

I tried dot use the formula below but am not confident that it is correct. I have included it in one of the orange cells on the file.

=IF(ISERROR(VLOOKUP(D3,$B$3:$B$1304,1,0)),"New","Old")

Would be great if someone could help me out.

Thanks in advance,
John

 

[vletm]

jonnieRC
Something like this?
( I copy & pasted only rows until 48 )

 

[jonnieRC]

Yes, that makes sense to me and ties into the formula i had.

Thanks for the help.

 

[AlanSidman]

@jonnieRC
Here is an alternative method. I used Power Query and joined the two tables in two different directions to obtain the results. File is attached for your review.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

 

[Peter Bartholomew]

Sorted lists of the deleted SKUs and the newly introduced ones
= SORT( FILTER( FY19SKU, ISERROR(XMATCH(FY19SKU, FY20SKU)) ) )
= SORT( FILTER( FY20SKU, ISERROR(XMATCH(FY20SKU, FY19SKU)) ) )
whilst those appearing in both years are given by
= SORT( FILTER( FY20SKU, ISNUMBER(XMATCH(FY20SKU, FY19SKU)) ) )