Comparision of Data (IF)

Hello:

I need to pull the following data from attached above. I need the following assistance:

Note there are 2 tabs(Legacy and NEW).

Column A in the NEW tab has the finished good number. Column C has the components tied to the finished good number. I need to validate if those Components in column C are also in the "LEGACY" tab. I'd like to add this in Column D.

In Column E i'd like to identify the number of components that are in the LEGACY tab for the corresponding finished good in Column A.

If the component is tied to the finished good I'd like to add the percentage from the LEGACY tab in column G.

Thanks for the support.

You don't have a "Components" column in your Legacy tab, so which column in your Legacy tab do you want to match component numbers to?

Have a look at the attached. You can use an index/match formula in Column D of your "New" tab to search for the component number in the relevant column in the "Legacy" tab, and return the component number if it finds it; otherwise, it will return #N/A, or a custom message of your choosing, eg "NOT IN LEGACY". You'll need to do something about the numbers stored as text (rather than numbers) in the component column of "Legacy" , though, otherwise that won't work.

Please note I had to change some of your data to show you that this works - C6 & C12 have been changed on the "New" tab.


The second part of your question seems to be a fairly straightforward situation requiring the use of a countif formula; however be aware that as some of your component numbers are repeated (eg 801041) the number of components is repeated, also. You might be better off generating a list of components in which no component number is repeated, and working off that to get the result you are looking for in Column E.


Why don't you see if you can manage the last part of your question yourself, based on what's above?

Your best bet is probably to upload an example file clearly showing your expected results They can just be typed in - don't worry about formulas etc.

Originally Posted by johnsor1

Could the group kindly take a look at my most updated attachement and see if they could help.

Hello:

I need to pull the following data from attached above. I need the following assistance:

Note there are 2 tabs(Legacy and NEW).

Column A in the NEW tab has the finished good number. Column C has the components tied to the finished good number. I need to validate if those Components in column C are also in the "LEGACY" tab. I'd like to add this in Column D

These results are now in Column E - I left D as it was , for clarity. An index/match, combined with an if, allows you to look for a value (eg C2 of your New tab) in Column C of your Legacy tab, provided the match in C of the Legacy tab also has the corresponding value in Column A (in this case, A2 of the New tab.) This formula, in E2:

Please Login or Register  to view this content.

is an array formula, and must be entered by pressing Ctrl, Shift & Enter at the same time - you do not type the { or }.

As advised previously though, you'll need to convert the numbers that are stored as text, to numbers, or else find a different solution. And I'm not sure offhand how you could return "additional components.... if applicable" - you can have a think about that one.

Originally Posted by johnsor1

In Column E i'd like to identify the number of components that are in the LEGACY tab for the corresponding finished good in Column A.

Please Login or Register  to view this content.

in G2 will return the number of components on the Legacy tab that correspond to 901374 (the value in A2).

Originally Posted by johnsor1

If the component is tied to the finished good I'd like to add the percentage from the LEGACY tab in column G.

The above logic should allow you to complete this yourself.


Hope this helps,