list of values where one value is replaced by another one

[adsako]

Hello All

I'm absolutely run out of any ideas how to approach my problem ...
In column A in attached file i have list of part numbers and in column B list of part numbers that builds part number from column A

I need to create list of unique values from column B based on criteria (in cell J1) however at the same time make sure that if in column B there is part number already mentioned in column A as the result I will get only part number that builds that specific part number, for instance
part A32651 is build with parts mentioned in cells E12/13/14/15 and these part numbers are not appearing in column A
however part A32657 is build with parts mentioned in cell E31/32/33 and I need excel to return values related to part numbers mentioned in these cells (that means as the result I should get from column B values that build part A32654/A32655/A32656)

So basically I want to "replace" each part number (child part number) in column B with child part number (column B) while child part number is also master part number (column A) ... if that make sene

I attached file where I explained more in details ...
it seems Pivot Table needs to be build but I do not know how to build relationships between all those parts

any help please?

thanks

[Bo_Ry]

You didn't show how you want to display child part number, so I guess.

J2 helper column

Please Login or Register  to view this content.

K2

Please Login or Register  to view this content.

[adsako]

Hi
thank you for this solution ... this helps only in this case (this part number) however I got file with different part numbers (different length and different nomenclature - some parts are 20 digits long and some only 3 with letters inside) therefore I'm looking for solution which help me to extract no matter how part number is build.
I was thinking that pivot table would be the best solution but as I said I do not know how to build relation between each part number



thanks

[JeteMc]

Perhaps this will be generalized enough to work:
1. Expand the table to include column N (previous results have been moved to columns Q)
2. Populate column I using: =ISNUMBER(MATCH([@[Child Part No]],[Master Part No],0))
3. Populate column J using: =AND([@[Mster Prt?]]=TRUE,COUNTIFS([Master Part No],[@[Child Part No]],[Mster Prt?],FALSE)=0)
4. Populate column K using: =IF(SUMPRODUCT(--([@[Mster Prt?]]:[@Replace]))=2,COUNTIFS([Master Part No],[@[Child Part No]]),1)
5. Populate column L using: =IF([@[Master Part No]]=R$1,SUM(L1,[@rows]),"")
6. Populate column M using:

Please Login or Register  to view this content.

7. Populate column N using (final output):

Please Login or Register  to view this content.

If this does not work then I suggest uploading a sample file that illustrates the problem(s).
Let us know if you have any questions.

[adsako]

Hello everyone

apologies however due to unexpected circumstances i was not able to reply to your solution
thank you for all ideas/solution

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'solved'. I hope that you have a blessed day.