Discontinued Items Worksheet Compared to Source Worksheet Question

[Danielc1234]

Hi All,

It's been a long long while since I have played with excel and have an issue I am trying to figure out. I have a workbook that contains a 'Source' sheet that contains our company's inventory list. Column F is the location where the SKU is showing. On a second worksheet I have a sheet named 'Discontinued Items'. The SKUs are in column A. The worksheet Discontinued Items obviously contains my manufacturers discontinued list that is put out bi-monthly. I am trying to figure a way to have or Source Sheet use the Discontinued Items worksheet and compile a list of our SKUs so I know which ones to deactivate in our original Source file. Hope I am explaining it the correctly. Like I said, it has been a long time since I have programmed anything in excel, but am excited to give this a try! Thanks in advance for any ideas or suggestions. Please see the image of the workbook sheets.

Dan


compare-sheets.jpg

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Danielc1234]

Hi Allan,

I have attached the excel file. I was thinking the easiest way to do this is to do a MATCH function that I have in Column G of the 'Source' worksheet. However, what I have so far is NOT working.

Any suggestions or ideas would be great. or if you need more explanation, please feel free to ask.

[Danielc1234]

An example of what I am trying to do as it pertains to #2 on your list is: On worksheet 'Discontinued Items', row A1622 for the Item ID = PWBBUL which is an exact match to worksheet 'Source' column sku(F) is F1515, which is also equal to PWBBUL.
I need a way to list all matches or to at least isolate them.

[Danielc1234]

I think I have made some progress by using =IF(MATCH(F2,Discontinued!$A$2:$A$20000,0),"Yes","No") However, if there is NO match, the results are showing #N/A
If there is a match, it shows the YES as it is supposed to. Any ideas?

[xladept]

This finds 168 matches:

Please Login or Register  to view this content.

[Danielc1234]

@ xladept - I'm not sure how to set that up in the sheet. Can you give me a little more details on how you accomplished this?

[xladept]

I inserted a standard module for the code and saved the book as macro-enabled.

Here's the book:

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)


Press ALT + F8 then double click on the macro name

[Danielc1234]

@xladept - This is a nice macro, however I need a way to keep track of which ones are actually discontinued. Because I will need to be able to Disable the ones that are found in my Source worksheet.

[bakerman2]

How about this.

=IF(ISNA(MATCH(F2,'Discontinued Items'!$A$2:$A$20000,0)),"No","Yes")  

[xladept]

What result would you prefer? I see that bakerman's formula would do the same thing

@ Rudi - Hi Rudi, It's been too long