lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2

[pkhardman]

อาจารย์ครับ ขอความช่วยเหลือด่วนๆ ผม lookup ค่าที่ซ้ำกันไม่ได้เพราะมันเจอค่าแรกเป็น 0 แก้ไขได้อย่างไรครับ ผมพยายามทำ index แล้วก็ไม่เจอ

ตามเอกสารที่แนบ ผมต้องการให้ sheet "BO_DE_OUTS(INDX_LNBR)" column "Z" หาค่าจาก column "c" ไปหาlookupค่าใน sheet "Cust" column "R" และนำค่าใน column "K" กลับมาแสดง แต่มันไปเจอบรรทัดบนก่อนที่มีค่าเป็น "0" มันเลยไม่เอาค่าที่ 2 มาครับควรแก้ยังไง


=INDEX(CUST!K:K,AGGREGATE(15,6,ROW(CUST!R:R)/(C2=CUST!$R:$R),COUNTIF(C$2:C2,C2))) สุตรที่ใช้ครับ

=XLOOKUP(@C:C,CUST!R:R,CUST!K:K) อีกสูตรก็ไม่เจอครับ

ปล ฝากดู VBA ให้ด้วยนะครับ ผมอยากลบทั้ง row ที่ column Q ไม่มีค่า แต่ให้เริ่มลบจากบรรทัดที่11 ครับ

[puriwutpokin]

Z2=IFERROR(INDEX(CUST!K:K,AGGREGATE(15,6,(ROW(CUST!$R:$R)-ROW(CUST!$R$1)+1)/($C3=CUST!$R:$R)/(0<CUST!K:K),COUNTIF($C$2:$C3,$C3))),) Copy to AA2 and Down
ส่วน VBA ต้องโพสโค้ดว่าติดที่ตรงไหนมาด้วยครับ

[pkhardman]

ขอบคุณมากๆครับ อาจารย์
ผมมีข้อสงสัย ทำไมอาจารย์ใช้ C3 ไม่ใช้ C2
($C3=CUST!$R:$R)/(0<CUST!K:K),COUNTIF($C$2:$C3,$C3))),

เหมือนตัวเลขจะข้ามไป1 row ผมแก้เป็น c2 แต่ก็ไม่มั่นใจจะถูกไหม ตัวเลขมันเหมือนกันเยอะมาก

ปล เดี่ยวส่ง vba ให้อีกรอบครับ ฝากด้วยนนะครับ

[puriwutpokin]

แก้ไขครับ
Z2=IFERROR(INDEX(CUST!K:K,AGGREGATE(15,6,(ROW(CUST!$R:$R)-ROW(CUST!$R$1)+1)/($C2=CUST!$R:$R)/(0<CUST!K:K),COUNTIF($C$2:$C2,$C2))),)
คัดลอกมาผิดครับ ขออภัยครับ
ส่วน VBA โคัดปรับตามนี้ดูครับ

Code: Select all

Sub DeleteRowIfCellBlank()
On Error Resume Next
    Dim ws As Worksheet
    Application.Calculation = xlManual
    Set ws = ThisWorkbook.Worksheets("CUST")
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "p").End(xlUp).Row
    Dim iRow As Long
    For iRow = LastRow To 10 Step -1
        If ws.Cells(iRow, "q").Value = 0 Then
            ws.Rows(iRow).Delete
        End If
    Next iRow
    Application.Calculation = xlAutomatic
End Sub

[pkhardman]

ขอบคุณมากครับ อาจารย์ ได้แล้วแหละ แต่ตอนนี่ file ผมมันอืดไปเลย ไม่รู้เป็นที่ สูตร index หรือเปล่าครับ เดี่ยวลองปรับดู ถ้าไม่ได้จะรบกวนอีกรอบนะครับ

[pkhardman]

อาจารครับ สูตร vba OK เลย แต่รบกวนอีกสักครั้ง ถ้าผมจะให้มันลบ row ที่ไม่มีคำว่า BB ใน column "Q" เลยทำได้ยังไงครับ
เผอิญตอนนี่มี XDAFFYRA คำนี่ติดอยู่อีก 2 row ไม่อยากได้เลย แฮะๆ ผมแนบ Excel มาให้อีกรอบครับ สำหรับ vba

ขอบคุณล่วงหน้า.

[puriwutpokin]

ปรับตรงนี้ครับ

Code: Select all

    For iRow = LastRow To 11 Step -1
        If ws.Cells(iRow, "q").Value = 0 Or Left(ws.Cells(iRow, "q").Value, 2) <> "BB" Then

[puriwutpokin]

ปรับตรงนี้ครับ

Code: Select all

    For iRow = LastRow To 11 Step -1
        If ws.Cells(iRow, "q").Value = 0 Or Left(ws.Cells(iRow, "q").Value, 2) <> "BB" Then

สำหรับสูตร office 365 จะเร็วขึ้นมานิดหน่อยครับ
Z2=FILTER(CUST!K:K,($C2=CUST!$R:$R)*(0<CUST!K:K)) Copy to AA2

แต่ถ้ามีการจำกัดช่วงไว้ ก็จะเร็วขึ้นมาก แต่ปัญหา การลบบรรทัด ที่อีกชีท
ทำให้ช่วงข้อมูลที่จำกัดไว้นั้น เปลี่ยนแปลง แต่ไม่น่าจะมีประเด็น ถ้าข้อมูลไม่เพิ่มจากเดิมอีก
จำกัดช่วง

Z2=FILTER(CUST!K$11:K$9999,($C2=CUST!$R$11:$R$9999)*(0<CUST!K$11:K$9999))

[pkhardman]

ขอบคุณครับอาจารย์ ช่วยได้เยอะเลย แต่ปัญหาผมไม่จบไม่สิ้น file ที่มี vba พอผม clear ข้อมูลแล้วทำให้ สูตรนั้น error ไปด้วยครับ มันจะทำให้ กลายเป็น #REF! ไปเลย แบบนี่เราเอาสุตรไปทำเป็น VBA ได้ไหมครับ .

[puriwutpokin]

ทำได้ ลองทำมาเองดูก่อนครับ โดยเริ่มจากลองบันทึก Macro ดูแล้วเอาโค้ดไปปรับใช้ แล้วติดตรงไหนมาถามได้ในนี้ครับ

[pkhardman]

ิอาจารย์ครับผมทำได่้แล้ว เย้ๆ รบกวนดูและปรับสูตรให้หน่อยนะครับ มันจะมีช้าอยู่บ้างตอนที่
1 VBA ที่ 3 Delete Row ช้าหน่อย ปรับไงได้บ้าง
2. รบกวนดู vba ที่ 5 ครับ ถ้ามีจำนวนมากขึ้นแก้ยังไงได้ครับ เช็น เอาจาก "z2:Z9000" ทำแล้วมันขึ้น # calc!
3. ถ้าอยากให้มัน sum row สุดท้าย โดยที่เราแก้สูตรข้อ 2 แล้วทำไงครับ

อนาคต น่าจะมีรายการอีก เยอะ

[puriwutpokin]

ข้อ 1 ถือว่าปกติครับ เพราะ เป็นการ Loop ครับ
ข้อ 2-3 ปรับตามนี้ครับ

Code: Select all

Sub calculator()
Dim LastRow As Integer
 Worksheets("BO_DE_OUTS(INDX_LNBR)").Select
  LastRow = Sheet3.Cells(Sheet3.Rows.Count, "b").End(xlUp).Row - 1
 Sheet3.Cells(2, "z").Resize(LastRow, 2).Formula _
 = "=FILTER(CUST!K$11:K$9999,($C2=CUST!$R$11:$R$9999)*(0<CUST!K$11:K$9999))"
 Sheet3.Cells(2, "ab").Resize(LastRow).Formula = "=SUM(X2:AA2)"
 Sheet3.Cells(2, "ab").Offset(LastRow).FormulaR1C1 = "=SUM(R[-" & LastRow & "]C:R[-1]C)"
End Sub

[pkhardman]

อาจารย์ครับ ผมขออนุญาติแก้ สูตรเป็นแบบนี้ แต่เจอปัญหาว่า มันมีการคำนวนเกินมาด้านล่างอีก 6 row เกิดจากอะไรครับ
vba Module ที่ 7 นะครับ

Sub calculator()
Dim LastRow As Integer
Worksheets("BO_DE_OUTS(INDX_LNBR)").Select
LastRow = Sheet3.Cells(Sheet3.Rows.Count, "b").End(xlUp).Row - 1
Sheet3.Cells(8, "z").Resize(LastRow, 2).Formula _
= "=XLOOKUP(@C3,CUST!C18,CUST!C[-15])"
Sheet3.Cells(8, "AA").Resize(LastRow, 2).Formula _
= "=-XLOOKUP(@C3,CUST!C18,CUST!C[-15])"
Sheet3.Cells(8, "ab").Resize(LastRow).Formula = "=SUM(X8:AA8)"
End Sub

[puriwutpokin]

เปลี่ยนตรงนี้

Code: Select all

  LastRow = Sheet3.Cells(Sheet3.Rows.Count, "b").End(xlUp).Row - 1

้้เป็น

Code: Select all

  LastRow = Sheet3.Cells(Sheet3.Rows.Count, "b").End(xlUp).Row - 7