• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula pick out added and removed SKUs

jonnieRC

New Member
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
 

Attachments

AlanSidman

Well-Known Member
@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
 

Attachments

Peter Bartholomew

Well-Known Member
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)) ) )
 
Top