Compares comma separated strings of information and returns the result if the data is the same

T

thehoang

New Member
Joined
Sep 21, 2023
Messages
7
Office Version
  1. 2016
Hello MrExcel.com
I haven't been back to the forum for a long time.

Currently, I have a problem, I present it as below.

I have 1 Sheet "RawData" and 1 Sheet "DataBase".

Sheet_RawData: B1:M8, this is the data I update daily on the production line.

Sheet_DataBase: A1:M35, this data will include a lot, each Finished Material in column B is a program.

Currently, I need to get data into column M of Sheet_RawData.

and the information to compare is column E and column J Sheet_RawData with column B and column I Sheet_DataBase.

There is a problem here, that is, the data in column J sheet_RawData will have 1 or several data and are connected by commas.

I wish there was a formula that could check all the values in the cells in column J separated by commas and compare them with column I Sheet_DataBase. If there are duplicate data values, it will return the content "Part Number" in column M sheet_RawData.
MrExcel Support.xlsm
ABCDEFGHIJKLM
1SideFinished MaterialSub-Assy MaterialPCB NameSeries NumberF/R P.Part NumberOld Part NumberPositionsDescriptionFeeder TypeComp.t pitchQty.
2MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 22IN6000000146810002477R2506R249K8mm4mm(4*1)1
3MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 23IN6000000375714-1662-492R5906R24.9K8mm4mm(4*1)1
4MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 24IN6000000117714-1662-152D14Diode8mm4mm(4*1)1
5MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 25IN6000003595314-5235-332D3Diode8mm4mm(4*1)2
6MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 26IN6000002397014-1664-992R46,R9906R49.9K8mm4mm(4*1)2
7MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 27IN6000001671114-1666-811R42,R8306R6.81K8mm4mm(4*1)2
8MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 28IN6000003130514-5225-102C36,C4508C1000P8mm4mm(4*1)2
9MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 29IN6000001148314-1651-0R0R116,R11708R10R8mm4mm(4*1)2
10MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 30IN6000003295710002638R114,R11508R24.9R8mm4mm(4*1)2
11MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 31IN6000003529010005081C6108C4.64P8mm4mm(4*1)2
12MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 32IN6000003757310001461R73,R7408R75R8mm4mm(4*1)2
13MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 33IN6000000277210002902R1906R2.67K8mm4mm(4*1)1
14MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 34IN6000003691210002253R9606R1M8mm4mm(4*1)1
15MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 35IN6000000182214-1661-243R2306R124K8mm4mm(4*1)1
16MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 36IN6000003593914-1661-102R2706R11K8mm4mm(4*1)1
17MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 37IN6000002464914-1661-103R8606R110K8mm4mm(4*1)1
18MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 38IN6000002528314-1661-053R6906R105K8mm4mm(4*1)1
19MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 39IN6000000247614-1660-0R0R11006R0R8mm4mm(4*1)1
20MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 40IN6000004700810015610C2806C1U8mm4mm(4*1)1
21MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 41IN6000001150014-5235-221C16,C2006C220P8mm4mm(4*1)2
22MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 42IN6000002065914-1662-002R67,R89,R97,R9806R20K8mm4mm(4*1)4
23MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 43IN6000003663214-1664-991R62,R106,R108,R11806R4.99K8mm4mm(4*1)4
24MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 44IN6000003595114-5225-104C59,C85,C86,C8708C0.1U8mm4mm(4*1)4
25MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 45IN6000002528014-1661-002R20,R56,R58,R65,R70,R71,R10406R10K8mm4mm(4*1)7
26MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 46IN6000003725814-1661-001R57,R63,R72,R88,R91,R102,R107,R11306R1K8mm4mm(4*1)8
27MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 47IN6000004653810004138C44,C47,C69,C70,C71,C72,C73,C74,C7506C0.1U8mm4mm(4*1)9
28MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 48IN6000002665914-1662-001R40,R43,R45,R55,R60,R66,R75,R92,R93,R9406R2K8mm4mm(4*1)10
29MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 49IN6000002295410004137C34,C49,C52,C53,C54,C55,C57,C76,C77,C78,C79,C80,C88,C9406C0.01U8mm4mm(4*1)14
30MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 50IN6000000884514-1661-003R26,R68,R90,R9506R100K8mm4mm(4*1)4
31MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 51IN6000002731614-5225-103C43,C51,C5808C.01U8mm4mm(4*1)3
32MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 52IN6000004084310000624R50,R54,R6106R3.01K8mm4mm(4*1)3
33MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 53IN6000001210514-1661-0R0R37,R38,R8106R10R8mm4mm(4*1)3
34MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 54IN6000001936514-1661-000R52,R53,R10106R100R8mm4mm(4*1)3
35MFA350PS24-XT1175SF10000013247-BIN60000013722-CMFA350PS24F- 55IN6000001150114-5235-471C18,C24,C2706C470P8mm4mm(4*1)3
DataBase


MrExcel Support.xlsm
ABCDEFGHIJKLM
1BarcodeDefect CodeProblem CategoryFinished MaterialSeriesDateCheckLinePositions ErrorPositionsCountPart Number
2V24520054SM12Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2R81IN60000012105
3V24520020PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2D22,C79IN60000022954
4V24520019PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2C18,C53IN60000011501
5V24520009PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2C18,C24IN60000011501
6V24520015PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2D14IN60000001177
7V24520016PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2D30,D3IN60000035953
8V24520007PS03Non - WettingMFA350PS24-XT1175MFA350 14-Jan-25T. NGOCSMT2C18,C61IN60000035290
RawData
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G1Cell Valuecontains " "textNO


Hope to get some review and support for this article.
Thanks!
 

Attachments

  • DataBase.png
    DataBase.png
    177.4 KB · Views: 2
  • RawData.png
    RawData.png
    71.5 KB · Views: 2
Sort by date Sort by votes
Dear Folks,
Anyone can help to take a look and give for me some advise on this topic?,
I try something with short example as below, but can not.
=MAP(A3:A1005,E3:E1005,LAMBDA(x,y,LET(a,TRIM(TEXTSPLIT(x,",")),b,TRIM(TEXTSPLIT(y,",")),TEXTJOIN(",",,FILTER($C$3:$C$1005,(MMULT(SIGN(ISNUMBER(SEARCH(b," "&$A$3:$A$1005&","))),SEQUENCE(COLUMNS(b),,,0)))*(B3:B1005=F3))))))
1740501279328.png

Appreciate your support,
Thanks!
 

Attachments

  • 1740501165667.png
    1740501165667.png
    76.3 KB · Views: 3
Upvote 0
Would be helpful, if you show us how you want the result. Please check the SAP parts(Col G) in the below table. Is that correct.
Please note: I am not providing a sloution in VBA. Just to let you know this is how i got it.
Book1
ABCDEFGH
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q802,Q302,Q305YESIN60000027633Q802,Q205YESIN60000027633,IN60000019365IN60000027633
4US435,JP233,US2305,US1111NOIN60000035673US1111NOIN60000035673,IN60000040598#CALC!
5JP254GCS15OPS24IN60000035951JP254,US435,US1018GCS150PS24IN60000035673,IN60000035951,IN60000040598#CALC!
6US4777 EN768,US377GCS150PS24IN60000029018US377GCS150PS24IN60000029018#CALC!
7JP5444,EU23331,US233GCS150PS24IN60000008138JP5444GCS150PS24IN60000008138IN60000027633
8Q302,Q802,Q305GCS150PS25IN60000019365Q802GCS150PS25IN60000027633,IN60000019365#CALC!
9US435,JP233,US1111GCS150PS26IN60000040598US1111GCS150PS26IN60000035673,IN60000040598#VALUE!
Sheet2

VBA Code: 
Sub MatchSAPParts()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim rawPositions As Variant, fssPositions As Variant
    Dim result As String
    Dim arrE As Variant, arrA As Variant
    Dim sapPart As String
    
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    For i = 3 To lastRow
        result = ""
        rawPositions = ws.Cells(i, 5).Value

        arrE = Split(rawPositions, ",")

        For j = 3 To lastRow
            fssPositions = ws.Cells(j, 1).Value
            sapPart = ws.Cells(j, 3).Value

            arrA = Split(fssPositions, ",")

            Dim k As Integer, m As Integer
            For k = LBound(arrE) To UBound(arrE)
                For m = LBound(arrA) To UBound(arrA)
                    If Trim(arrE(k)) = Trim(arrA(m)) Then
                        If result = "" Then
                            result = sapPart
                        Else
                            result = result & "," & sapPart
                        End If
                        Exit For
                    End If
                Next m
            Next k
        Next j

        ws.Cells(i, 7).Value = result
    Next i
   
End Sub
 
Upvote 0
Sam_D_Ben said:
Would be helpful, if you show us how you want the result. Please check the SAP parts(Col G) in the below table. Is that correct.
Please note: I am not providing a sloution in VBA. Just to let you know this is how i got it.
Book1
ABCDEFGH
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q802,Q302,Q305YESIN60000027633Q802,Q205YESIN60000027633,IN60000019365IN60000027633
4US435,JP233,US2305,US1111NOIN60000035673US1111NOIN60000035673,IN60000040598#CALC!
5JP254GCS15OPS24IN60000035951JP254,US435,US1018GCS150PS24IN60000035673,IN60000035951,IN60000040598#CALC!
6US4777 EN768,US377GCS150PS24IN60000029018US377GCS150PS24IN60000029018#CALC!
7JP5444,EU23331,US233GCS150PS24IN60000008138JP5444GCS150PS24IN60000008138IN60000027633
8Q302,Q802,Q305GCS150PS25IN60000019365Q802GCS150PS25IN60000027633,IN60000019365#CALC!
9US435,JP233,US1111GCS150PS26IN60000040598US1111GCS150PS26IN60000035673,IN60000040598#VALUE!
Sheet2

VBA Code: 
Sub MatchSAPParts()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim rawPositions As Variant, fssPositions As Variant
    Dim result As String
    Dim arrE As Variant, arrA As Variant
    Dim sapPart As String
   
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    For i = 3 To lastRow
        result = ""
        rawPositions = ws.Cells(i, 5).Value

        arrE = Split(rawPositions, ",")

        For j = 3 To lastRow
            fssPositions = ws.Cells(j, 1).Value
            sapPart = ws.Cells(j, 3).Value

            arrA = Split(fssPositions, ",")

            Dim k As Integer, m As Integer
            For k = LBound(arrE) To UBound(arrE)
                For m = LBound(arrA) To UBound(arrA)
                    If Trim(arrE(k)) = Trim(arrA(m)) Then
                        If result = "" Then
                            result = sapPart
                        Else
                            result = result & "," & sapPart
                        End If
                        Exit For
                    End If
                Next m
            Next k
        Next j

        ws.Cells(i, 7).Value = result
    Next i
  
End Sub
Click to expand...
Hi Sam_D_Ben,
Thank you with the support by VBA code.
"Would be helpful, if you show us how you want the result. Please check the SAP parts(Col G) in the below table. Is that correct.
Please note: I am not providing a sloution in VBA. Just to let you know this is how i got it.".
Sorry because i forgot show the result of column G (SAP part), below is the result i want to have.
1740528206398.png

Validation conditions to get data for column G
1) A3 and E3, like Q802 and B3 = F3, then result G3 will take a value of C3
2) A4 and E4, like US1111, B4 # F4, then G4 will be blank.
3) A5 and E5, like JP254, B5 = F5, then result G5 will take a value of C5
4) A9 and E9, do not same, B9 = F9, then G9 will be blank
nn) Similar for the cases below.
SMT Defect V1.41.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q802,Q302,Q305YESIN60000027633Q802,Q205YESIN60000027633
4US435,JP233,US2305,US1111NOIN60000035673US1111YES
5JP254GCS150PS24IN60000035951JP254,US435,US1018GCS150PS24IN60000035951
6US4777 ,EN768,US377GCS150PS24IN60000029018US377GCS150PS24IN60000029018
7JP5444, EU23331, US233GCS150PS24IN60000008138JP5444GCS150PS24IN60000008138
8Q302,Q802,Q305GCS150PS25IN60000019365Q802GCS150PS24
9US435,JP233,US1111GCS150PS26IN60000040598US2222GCS150PS26
Test


Based on the above result, can you give me more suggestion to get result by using Excel formula,
Because, I don't have VBA background, so it will be difficult for me to approach.
Again, Many thanks!
 
Upvote 0
thehoang said:
I try something with short example as below, but can not.
=MAP(A3:A1005,E3:E1005,LAMBDA(x,y,LET(a,TRIM(TEXTSPLIT(x,",")),b,TRIM(TEXTSPLIT(y,",")),TEXTJOIN(",",,FILTER($C$3:$C$1005,(MMULT(SIGN(ISNUMBER(SEARCH(b," "&$A$3:$A$1005&","))),SEQUENCE(COLUMNS(b),,,0)))*(B3:B1005=F3))))))
Click to expand...
Given that you tried that formula, it would seem that your profile might need updating from excel 2016?

If you have all those functions then see if this is any use.

25 02 26.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q802,Q302,Q305YESIN60000027633Q802,Q205YESIN60000027633
4US435,JP233,US2305,US1111NOIN60000035673US1111YES 
5JP254GCS150PS24IN60000035951JP254,US435,US1018GCS150PS24IN60000035951
6US4777 ,EN768,US377GCS150PS24IN60000029018US377GCS150PS24IN60000029018
7JP5444, EU23331, US233GCS150PS24IN60000008138JP5444GCS150PS24IN60000008138
8Q302,Q802,Q305GCS150PS25IN60000019365Q802GCS150PS24 
9US435,JP233,US1111GCS150PS26IN60000040598US2222GCS150PS26 
SAP Part
Cell Formulas
RangeFormula
G3:G9G3=IF(AND(COUNT(MATCH(TEXTSPLIT(E3,","),TEXTSPLIT(A3,","),0)),B3=F3),C3,"")
 
Upvote 0
Peter_SSs said:
Given that you tried that formula, it would seem that your profile might need updating from excel 2016?

If you have all those functions then see if this is any use.

25 02 26.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q802,Q302,Q305YESIN60000027633Q802,Q205YESIN60000027633
4US435,JP233,US2305,US1111NOIN60000035673US1111YES 
5JP254GCS150PS24IN60000035951JP254,US435,US1018GCS150PS24IN60000035951
6US4777 ,EN768,US377GCS150PS24IN60000029018US377GCS150PS24IN60000029018
7JP5444, EU23331, US233GCS150PS24IN60000008138JP5444GCS150PS24IN60000008138
8Q302,Q802,Q305GCS150PS25IN60000019365Q802GCS150PS24 
9US435,JP233,US1111GCS150PS26IN60000040598US2222GCS150PS26 
SAP Part
Cell Formulas
RangeFormula
G3:G9G3=IF(AND(COUNT(MATCH(TEXTSPLIT(E3,","),TEXTSPLIT(A3,","),0)),B3=F3),C3,"")
Click to expand...
Hi Peter_SSs,
Very happy to see your input the fomular and support.
So some a few things, i will share below,
FSS Data: Column A:C
RawData: Column D:F
FSS Data is Total data, and RawData is daily collected data.
Currently, FSS Data total data is 9260 rows.
RawData data has 1584 rows.
For the formula "=IF(AND(COUNT(MATCH(TEXTSPLIT(E3,","),TEXTSPLIT(A3,","),0)),B3=F3),C3,"")"
Currently, it is comparing row by row to compare and return the result.
Can you give me more advice, how to use the data in columns E and F to search and compare with the total data of columns A and B to look up the result of column C?
 
Upvote 0
thehoang said:
Currently, it is comparing row by row to compare and return the result.
Click to expand...
That seems to be exactly what your sample in post #4 did, so I replicated that. :confused:



thehoang said:
Can you give me more advice, how to use the data in columns E and F to search and compare with the total data of columns A and B to look up the result of column C?
Click to expand...
Could you give a small sample of data and expected results with XL2BB that demonstrates the changed requirement?

What happens if more than one row matches? Is that possible?
 
Upvote 0
Peter_SSs said:
Could you give a small sample of data and expected results with XL2BB that demonstrates the changed requirement?

What happens if more than one row matches? Is that possible?
Click to expand...
Hi Peter_SSs,
Please be updated the small sample with data and expected result in column G.

SMT Defect V1.41.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302, Q802MHP650PS24-XB0820IN60000018391
4Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302,Q802,Q305MHP650PS24-XB0820IN60000018391
5U50MHP650PS24-XB0820IN60000046547D2ECM60UD21IN60000047866
6D202MHP650PS24-XB0820IN60000002184D6, D4ECM60UD21IN60000047866
7D201MHP650PS24-XB0820IN60000039663D7ECM60UD21IN60000047866
8U902,U903MHP650PS24-XB0820IN60000011142Q802MHP650PS24-XB0820IN60000018391
9R1,R2,R3MHP650PS24-XB0820IN60000040902Q305,Q302MHP650PS24-XB0820IN60000018391
10D58,D59,D206,D207,D208,D603MHP650PS24-XB0820IN60000031309U23SF10000002358
11Q6ECM60UD21IN60000040605C32SF10000044267IN60000046538
12Q2,Q5ECM60UD21IN60000041847D20SF10000044267IN60000047866
13D2,D4, D5,D6, D7ECM60UD21IN60000047866Q1SF10000006130RM20000019391
14R31ECM60UD21IN60000036922C66GFR1K5PS24-XT1279IN60000019699
15C19ECM60UD21IN60000002781C14GFR1K5PS24-XT1279IN60000017664
16C115SF10000002358IN60000034004SCRAPGCS150PS24
17U1SF10000002358RM20000019321R51GCS150PS24IN60000008138
18U11,U17,U19,U21SF10000002358IN60000011142D20, C36GCS150PS24IN60000005670
19U32SF10000002358IN60000021997R46GCS150PS24
20U27SF10000002358IN60000000041
21D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
22C9,C14,C19,C22,C24,C31, C32,C36,C46,C51SF10000044267IN60000046538
23D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
24U6SF10000006130IN60000047869
25Q1SF10000006130RM20000019391
26CR1SF10000006130IN60000004171
27C13, C14 ,C16,C27,C34,C35,C44,C122GFR1K5PS24-XT1279IN60000017664
28C30,C64,C66,C68,C69,C70,C93GFR1K5PS24-XT1279IN60000019699
29C116GFR1K5PS24-XT1279IN60000035951
30C102GFR1K5PS24-XT1279IN60000005004
31R51GCS150PS24IN60000008138
32R51GCS150PS24IN60000008138
33D2,D20GCS150PS24IN60000005670
Test (2)


Peter_SSs said:
That seems to be exactly what your sample in post #4 did, so I replicated that. :confused:
Click to expand...
this is my fault, I gave little data, and at that time, Sam_D_Ben used VBA Code to process those data lines, I forgot, and interpreted line by line.
Actually, my wish at the beginning, (I also tried using functions (Let, Lambda, map,....) to expect to process data in the area and return the result.
Sorry for confusing you here.
Many thanks!
 
Upvote 0
Thanks for the new sample data. see how this goes.
The formulas could be simpler if your data was more consistent. Both column A and column E mostly have just a comma between the values, but sometimes a comma and a space.
Example
1740620838762.png


BTW, your profile still shows Excel 2016, yet you seem to have functions not available in the version. Please update your profile information.

(The colours below were just to help me check data/results).

thehoang.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302, Q802MHP650PS24-XB0820IN60000018391
4Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302,Q802,Q305MHP650PS24-XB0820IN60000018391
5U50MHP650PS24-XB0820IN60000046547D2ECM60UD21IN60000047866
6D202MHP650PS24-XB0820IN60000002184D6, D4ECM60UD21IN60000047866
7D201MHP650PS24-XB0820IN60000039663D7ECM60UD21IN60000047866
8U902,U903MHP650PS24-XB0820IN60000011142Q802MHP650PS24-XB0820IN60000018391
9R1,R2,R3MHP650PS24-XB0820IN60000040902Q305,Q302MHP650PS24-XB0820IN60000018391
10D58,D59,D206,D207,D208,D603MHP650PS24-XB0820IN60000031309U23SF10000002358 
11Q6ECM60UD21IN60000040605C32SF10000044267IN60000046538
12Q2,Q5ECM60UD21IN60000041847D20SF10000044267IN60000047866
13D2,D4, D5,D6, D7ECM60UD21IN60000047866Q1SF10000006130RM20000019391
14R31ECM60UD21IN60000036922C66GFR1K5PS24-XT1279IN60000019699
15C19ECM60UD21IN60000002781C14GFR1K5PS24-XT1279IN60000017664
16C115SF10000002358IN60000034004SCRAPGCS150PS24 
17U1SF10000002358RM20000019321R51GCS150PS24IN60000008138
18U11,U17,U19,U21SF10000002358IN60000011142D20, C36GCS150PS24IN60000005670
19U32SF10000002358IN60000021997R46GCS150PS24 
20U27SF10000002358IN60000000041
21D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
22C9,C14,C19,C22,C24,C31, C32,C36,C46,C51SF10000044267IN60000046538
23D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
24U6SF10000006130IN60000047869
25Q1SF10000006130RM20000019391
26CR1SF10000006130IN60000004171
27C13, C14 ,C16,C27,C34,C35,C44,C122GFR1K5PS24-XT1279IN60000017664
28C30,C64,C66,C68,C69,C70,C93GFR1K5PS24-XT1279IN60000019699
29C116GFR1K5PS24-XT1279IN60000035951
30C102GFR1K5PS24-XT1279IN60000005004
31R51GCS150PS24IN60000008138
32R51GCS150PS24IN60000008138
33D2,D20GCS150PS24IN60000005670
Sheet2
Cell Formulas
RangeFormula
G3:G19G3=INDEX(FILTER(C$3:C$33,BYROW(A$3:A$33,LAMBDA(r,COUNT(MATCH(TEXTSPLIT(SUBSTITUTE(r," ",""),","),TEXTSPLIT(SUBSTITUTE(E3," ",""),","),0))))*(B$3:B$33=F3),""),1)
 
Upvote 0
Peter_SSs said:
Thanks for the new sample data. see how this goes.
The formulas could be simpler if your data was more consistent. Both column A and column E mostly have just a comma between the values, but sometimes a comma and a space.
Example
View attachment 122768

BTW, your profile still shows Excel 2016, yet you seem to have functions not available in the version. Please update your profile information.

(The colours below were just to help me check data/results).

thehoang.xlsm
ABCDEFG
1FSS DataRawData
2PositionsFGSAP PartPositionsFGSAP Part
3Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302, Q802MHP650PS24-XB0820IN60000018391
4Q302,Q305,Q802MHP650PS24-XB0820IN60000018391Q302,Q802,Q305MHP650PS24-XB0820IN60000018391
5U50MHP650PS24-XB0820IN60000046547D2ECM60UD21IN60000047866
6D202MHP650PS24-XB0820IN60000002184D6, D4ECM60UD21IN60000047866
7D201MHP650PS24-XB0820IN60000039663D7ECM60UD21IN60000047866
8U902,U903MHP650PS24-XB0820IN60000011142Q802MHP650PS24-XB0820IN60000018391
9R1,R2,R3MHP650PS24-XB0820IN60000040902Q305,Q302MHP650PS24-XB0820IN60000018391
10D58,D59,D206,D207,D208,D603MHP650PS24-XB0820IN60000031309U23SF10000002358 
11Q6ECM60UD21IN60000040605C32SF10000044267IN60000046538
12Q2,Q5ECM60UD21IN60000041847D20SF10000044267IN60000047866
13D2,D4, D5,D6, D7ECM60UD21IN60000047866Q1SF10000006130RM20000019391
14R31ECM60UD21IN60000036922C66GFR1K5PS24-XT1279IN60000019699
15C19ECM60UD21IN60000002781C14GFR1K5PS24-XT1279IN60000017664
16C115SF10000002358IN60000034004SCRAPGCS150PS24 
17U1SF10000002358RM20000019321R51GCS150PS24IN60000008138
18U11,U17,U19,U21SF10000002358IN60000011142D20, C36GCS150PS24IN60000005670
19U32SF10000002358IN60000021997R46GCS150PS24 
20U27SF10000002358IN60000000041
21D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
22C9,C14,C19,C22,C24,C31, C32,C36,C46,C51SF10000044267IN60000046538
23D6,D11,D16,D17,D18,D20,D24SF10000044267IN60000047866
24U6SF10000006130IN60000047869
25Q1SF10000006130RM20000019391
26CR1SF10000006130IN60000004171
27C13, C14 ,C16,C27,C34,C35,C44,C122GFR1K5PS24-XT1279IN60000017664
28C30,C64,C66,C68,C69,C70,C93GFR1K5PS24-XT1279IN60000019699
29C116GFR1K5PS24-XT1279IN60000035951
30C102GFR1K5PS24-XT1279IN60000005004
31R51GCS150PS24IN60000008138
32R51GCS150PS24IN60000008138
33D2,D20GCS150PS24IN60000005670
Sheet2
Cell Formulas
RangeFormula
G3:G19G3=INDEX(FILTER(C$3:C$33,BYROW(A$3:A$33,LAMBDA(r,COUNT(MATCH(TEXTSPLIT(SUBSTITUTE(r," ",""),","),TEXTSPLIT(SUBSTITUTE(E3," ",""),","),0))))*(B$3:B$33=F3),""),1)
Click to expand...
Hi Peter_SSs
I have no words to express my joy,
Your recipe is amazing, it's perfect,...

"BTW, your profile still shows Excel 2016, yet you seem to have functions not available in the version. Please update your profile information."
-> With the current Excel 2016, your fomular no problem.

"Both column A and column E mostly have just a comma between the values, but sometimes a comma and a space."
-> Rawdata, column E is hand-entered data, so sometimes there will be spaces before and after, so I still set spaces in the sample data, and you can set the function replace "SUBSTITUTE". That's great.
Many thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
compare extraction right data for information invoices with wrong data for each range
Replies
2
Views
434
abdo meghari
A
E
  • Solved
Comparing two comma-delimited lists and getting unique values
Replies
7
Views
1K
Fluff
Fluff
V
  • Question Question
Formula issues to find variations when zero is one reference value
Replies
0
Views
264
vladimiratanasiu
V
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
M
  • Question Question
Different results running same macro with same data
Replies
3
Views
3K
Logit
Logit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top