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

thehoang

New Member
Joined
Sep 21, 2023
Messages
4
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: 1
  • RawData.png
    RawData.png
    71.5 KB · Views: 1
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: 2
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
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
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
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))))))
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
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,"")
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
Currently, it is comparing row by row to compare and return the result.
That seems to be exactly what your sample in post #4 did, so I replicated that. :confused:



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?
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

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