Excel Forum

รบกวนของสูตรที่ J3:N
J3:J หาเลขที่เหมือนกันใน Col1,Col2 (ข้อมูลอยู่ที่ A3:B)
K3:K หาเลขที่ต่างกันใน Col1,Col2 (ข้อมูลอยู่ที่ A3:B)
L3:L หาเลขที่มีเฉพาะ Col1 ไม่มีใน Col2 (ข้อมูลอยู่ที่ A3:B)
M3:M หาเลขที่มีเฉพาะ Col2 ไม่มีใน Col1 (ข้อมูลอยู่ที่ A3:B)
N3:N เรียงลำดับเลขที่พบมากไปน้อยสุดของ Col1,Col2,Col3,Col4 คั่นชุดข้อมูลด้วยเครื่องหมาย - เช่น
พบเลข 0 จาก 4 Column
พบเลข 12 จาก 3 Column
พบเลข 3456 จาก 2 Column
พบเลข 7 จาก 1 Column
ไม่พบเลข 89 เรียงลำดับดังนี้ 0-12-3456-7-89 เป็นต้น (ข้อมูลอยู่ที่ A3:D)


ตัวอย่างที่ต้องการคร่าวๆได้แสดงไว้ที่ E3:I ครับ
https://docs.google.com/spreadsheets/d/ ... sp=sharing

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ J3 คีย์
   =SUBSTITUTE(TEXTJOIN("",TRUE,SORT(FILTER(MID(A3,SEQUENCE(LEN(A3)),1),ISNUMBER(MATCH(MID(A3,SEQUENCE(LEN(A3)),1),MID(B3,SEQUENCE(LEN(B3)),1),0))))),"-","")
   Enter > Copy ลงด้านล่าง
2. ที่ K3 คีย์
   =IFERROR(SUBSTITUTE(TEXTJOIN("",TRUE,SORT(FILTER(MID(A3&B3,SEQUENCE(LEN(A3)+LEN(B3)),1),ISNA(MATCH(MID(A3&B3,SEQUENCE(LEN(A3)+LEN(B3)),1),MID(J3,SEQUENCE(MAX(1,LEN(J3))),1),0))))),"-",""),"")
   Enter > Copy ลงด้านล่าง
3. ที่ L3 คีย์
   =IFERROR(SUBSTITUTE(TEXTJOIN("",TRUE,SORT(FILTER(MID(A3,SEQUENCE(LEN(A3)),1),ISNA(MATCH(MID(A3,SEQUENCE(LEN(A3)),1),MID(B3,SEQUENCE(LEN(B3)),1),0))))),"-",""),"")
   Enter > Copy ลงด้านล่าง
4. ที่ M3 คีย์
   =IFERROR(SUBSTITUTE(TEXTJOIN("",TRUE,SORT(FILTER(MID(B3,SEQUENCE(LEN(B3)),1),ISNA(MATCH(MID(B3,SEQUENCE(LEN(B3)),1),MID(A3,SEQUENCE(LEN(A3)),1),0))))),"-",""),"")
   Enter > Copy ลงด้านล่าง

ส่วน N3 ยังไม่เข้าใจโจทย์ครับ

ขอบคุณสำหรับสูตรที่ J3:M ครับ

วิธีหาสูตรที่ N3:N
ตัวอย่าง1 เช่นที่ A5 = 382479, B5 = 5790136, C5 = -, D5 = 302981
ถ้านำเลขแต่ละ column มาเขียนเรียงกันใหม่ โดยเรียงลำดับจากพบจำนวนมากสุดขึ้นก่อน 3339990011227788456
พบเลข 39 = 3 ครั้ง พบเลข 01278 = 2 ครั้งและพบเลข 456 = 1 ครั้ง พอจัดกลุ่มจากมากไปน้อยจะได้ 39-01278-456

ตัวอย่าง2 เช่นที่ A7 = 50-4691, B7 = 192345, C7 = -, D7 = -
ถ้านำเลขแต่ละ column มาเขียนเรียงกันใหม่ โดยเรียงลำดับจากพบจำนวนมากสุดขึ้นก่อน 114455990236
จะพบเลข 1459 = 2 ครั้ง จะพบเลข 0236 = 1 ครั้ง และไม่พบเลข 78 = 0 พอจัดกลุ่มจากมากไปน้อยจะได้ 1459-0236-78 เป็นต้น

ลองนำไปปรับใช้ให้กระชับลงดูครับ

เซลล์ N3


=ARRAYFORMULA(TEXTJOIN("-",TRUE,CHOOSE({1,2,3,4,5,6,7,8,9,10},
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),1))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),2))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),3))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),4))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),5))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),6))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),7))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),8))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),9))),""),
IFERROR(TEXTJOIN("",TRUE,FILTER(--RIGHT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0)),--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))=LARGE(UNIQUE(--LEFT(MID(TEXTJOIN("",TRUE,FREQUENCY(--MID(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""),SEQUENCE(LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A3:D3),"-",""))),1),SEQUENCE(10,1,0))),SEQUENCE(10),1)&"."&SEQUENCE(10,1,0))),10))),""))))


Enter > Copy ลงด้านล่าง

ขอบคุณสำหรับสูตรครับ