หาค่า location code และ location area จาก b_code

[sc201105]

เรียนอาจารย์ ที่เคราพ
ผมต้องรบกวนอย่างช่วยผมเกี่ยวกับการหาค่า location code และ location area จาก b_code ตามเอกสารแนบ ผมไม่รู้ว่าจะทำอย่างไร ผมได้อธิบายไว้ใน File แล้ว ประกอบด้วย File ข้อมูลจาก อยู่ที่ sheet source และข้อมูลอ้างอิง location code และ location area จะอยู่ที่ sheet location data ผมไม่รู้ว่าเขียนอย่างไรให้มันเช็ค digit ข้อมูลลดหลั่นตัวเลขของเพื่อให้ได้ข้อมูลตามที่บอกไว้ใน file
รบกวนด้วยครับ ไม่รู้จะทำอย่างไรจริงๆครับที่ให้มันทำงานและได้ผลลัพธ์อย่างที่ต้องการ

ขอบคุณมากๆ ครับผม

[snasui]

ดูตามไฟล์แนบครับ

ผมเพิ่มคอลัมน์ G:I มาเพื่อกระจายสูตรออกมาให้ทำความเข้าใจง่ายขึ้น สูตรใน G:H สามารถเขียนในเซลล์เดียวกันเพื่อหา Location_Code ได้

โดยมีขั้นตอนดังนี้

1. ที่เซลล์ G2 คีย์สูตรเพื่อหาค่าที่ต้องการตัดมามีกี่จำนวน

=7-MOD(ROWS(G$2:G2)-1,6)

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

2. ที่เซลล์ H2 คีย์สูตรเพื่อตัดค่าในคอลัมน์ B เริ่มจากตำแหน่งที่ 3 ของอักขระ มาเท่าจำนวนที่ได้ตามข้อ 1. ด้านบน

=MID(B2,3,G2)+0

Enter > Copy ลงด้านล่าง สังเกตการบวกด้วย 0 ก็เพื่อต้องการทำให้เป็นตัวเลข เนื่องจากค่าจากการตัดข้อความจะเป็นตัวอักษรเสมอ

3. ที่เซลล์ I2 คีย์สูตรเพื่อหาค่า Location_Area

=VLOOKUP(H2,'Location Data'!$A$2:$B$19,2,0)

Enter > Copy ลงด้านล่าง หากไม่พบจะแสดงผลลัพธ์เป็น #N/A

[sc201105]

เรียนท่านอาจารย์
ผมรบกวนอีกครั้งครับ มันไม่ได้อย่างที่ต้องการ เนื่องจากข้อมูลที่เป็นของจริงนั้นมันจะปะปนกันมาไม่เีรียงตามลำดับในการตัดว่า row แรก ต้อง 7 digits row ถัดมาของข้อมูลเป็น 6 digits ครับ ซึ่งผมทดลองนำข้อมูลมาคละกันสร้างเป็น sheets
source(2) และนำcopy สูตรของอาจารย์ไปวางไว้มันจะไม่ได้คำตอบที่ต้องการ
โดยจริงๆ แล้วต้องดูที่ b_code ทุกตัวเป็น 7 digits หมดก่อนแล้ว vlookup เพื่อให้ได้ค่าว่าพบกี่ row แล้ว row ที่ไม่พบนั้นให้นำกลับไปหาใหม่โดยให้ลดจำนวน digit b_code ลงเหลือ 6 digits แล้วทำการ lookup หาข้อมูลอีก แล้วนำตัวที่ไม่เจอนั้นมาหาใหม่ โดยลดจำนวน digit b_code ลงเหลือ 5 digits และ 4 digits ......ไปเลยๆ จนกระทั่งหาไม่พบจาก sheet location_data ซึ่งผมไม่รู้ว่าจะเขียน loop ให้มันแสดงค่าผลลัพธ์ที่ต้องการไว้ใน column เดียวกันได้อย่างไร คือที่ column H และ Column I ที่อาจารย์ช่วยทำขึ้นมาให้ครับผม
รบกวนอาจารย์ช่วยอีกครั้งครับ

ขอบคุณพระคุณครับผม

[snasui]

สามารถใช้สูตร Array ได้ครับแต่สูตรจะยากมาก ลองดูตามไฟล์แนบครับ

ที่เซลล์ E2 คีย์

=INDEX('Location Data'!$A$2:$A$19,SMALL(IF(ISNUMBER(MATCH('Location Data'!$A$2:$A$19,--MID(B2,3,8-ROW(INDIRECT("1:6"))),0)),ROW('Location Data'!$A$2:$A$19)-ROW('Location Data'!$A$2)+1),1))

Ctrl+Shift+Enter > Copy ลงด้านล่าง สำหรับค่าไหนที่ไม่มีในชีท Location Data จะแสดงค่า #Num!

[sc201105]

ขอบคุณครับท่านอาจารย์ ได้แล้วครับ มีคำถามเพิ่มเติมว่า
1. มัน run นานมากหากข้อมูลของผมมีขนาดเป็น หมื่นๆ แถว
ทำอย่างไรให้มันเร็วได้บ้างหรือเปล่าครับ
2. แล้วหาก copy สูตรเลยข้อมูลที่มีอยู่จะให้มันแสดงค่าเป็น
Null จะต้องเพิ่มเติมอย่างไรในสูตรบ้างครับ
3. แล้วหากเขียนเป็น VBA ผมจะเขียน code ตรงนี้ได้อย่างไร
บ้างครับผม
รบกวนอาจารย์เพิ่มอีกครั้งครับผม

ขอบพระคุณอาจารย์มาอีกครั้งครับผม

[snasui]

1. เนื่องจากเป็นสูตร Array ข้อมูลเป็นหมื่นแถวก็จะใช้เวลาคำนวณนานครับ จะให้เร็วเครื่องต้องแรงครับ

2. กรณีไม่ต้องการให้แสดงค่าผิดพลาดก็ให้เขียนสูตร If ครอบเข้าไปอีกชั้น ยกตัวอย่างเช่น

=If(iserr(...)),"",...)

เครื่องหมาย ... คือสูตรเดิมและจะใช้ได้กับ Version 2007 ขึ้นไปเนื่องจากซ้อนสูตรเกิน 7 ชั้นจึงไม่สามารถทำได้ใน 2003

อีกทางเลือกให้เพิ่มคอลัมน์เข้ามาช่วย ยกตัวอย่างจากไฟล์เดิมที่เซลล์ G2 คีย์สูตรเพื่อแปลงค่า Location_Code ที่ผิดพลาดเป็นค่าว่าง

=If(Iserr(E2),"",E2)

Enter

3. กรณีต้องการใช้ VBA ให้เขียนมาก่อนครับ ติดตรงไหนก็มาถามกันต่อครับ

[sc201105]

ขอบคุณครับอาจารย์ สำหรับคำตอบ ส่วนเรื่อง vba นั้นผมไม่รู้จริงๆ เลย เลยไม่รู้จะขึ้นต้นเขียนอย่างไร จะกำหนดตัวแปรอย่างไร เพียงแต่ผมลองนำสูตรที่อาจารย์ทำให้ แล้วทดลอง record marco จะได้สูตรอย่างนี้ แล้วหากผมจะเขียนเป็นตัวแปรพอจะแนะเป็นจุดเริ่มต้นได้หรือเปล่าว่าจะขึ้นอย่างไรก่อนดี

Sub add_location_code()
Range("E2").Select
Selection.FormulaArray = _
"=INDEX('Location Data'!R2C1:R19C1,SMALL(IF(ISNUMBER(MATCH('Location Data'!R2C1:R19C1,--MID(RC[-3],3,8-ROW(INDIRECT(""1:6""))),0)),ROW('Location Data'!R2C1:R19C1)-ROW('Location Data'!R2C1)+1),1))"
Selection.Copy
Range("E3:E20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Location Data'!R2C1:R19C2,2,FALSE)"
Selection.Copy
Range("F3:F20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

ขอบพระคุณครับผม

[snasui]

ดูการประยุกต์ใช้ Code ตามด้านล่างกับสูตร Array ตามไฟล์แนบครับ

Code: Select all

Sub Test0()
Dim r As Range, rs As Range, rt As Range
    With Worksheets("Source")
        Set rs = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    End With
    Set rt = Worksheets("Template").Range("B2")
    For Each r In rs
        rt = r
        r.Offset(0, 3) = rt.Offset(0, 3)
        r.Offset(0, 4) = rt.Offset(0, 4)
    Next
End Sub

[sc201105]

ขอบคุณครับอาจารย์ ผมขอไปศึกษาก่อนครับ เพื่อประยุกต์ใช้กับงานหน้าต่อไปด้วย หากติดขัดประการใดจะเรียนรบกวนสอบถามเพิ่มเติมอีกครั้งต่อไป

ขอบพระคุณอย่างสูงอีกครั้งครับผม