Page 1 of 1
lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 4:21 pm
by 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 ครับ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 4:46 pm
by 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 ต้องโพสโค้ดว่าติดที่ตรงไหนมาด้วยครับ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 5:11 pm
by pkhardman
ขอบคุณมากๆครับ อาจารย์
ผมมีข้อสงสัย ทำไมอาจารย์ใช้ C3 ไม่ใช้ C2
($C3=CUST!$R:$R)/(0<CUST!K:K),COUNTIF($C$2:$C3,$C3))),
เหมือนตัวเลขจะข้ามไป1 row ผมแก้เป็น c2 แต่ก็ไม่มั่นใจจะถูกไหม ตัวเลขมันเหมือนกันเยอะมาก
ปล เดี่ยวส่ง vba ให้อีกรอบครับ ฝากด้วยนนะครับ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 5:20 pm
by 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
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 5:35 pm
by pkhardman
ขอบคุณมากครับ อาจารย์ ได้แล้วแหละ แต่ตอนนี่ file ผมมันอืดไปเลย ไม่รู้เป็นที่ สูตร index หรือเปล่าครับ เดี่ยวลองปรับดู ถ้าไม่ได้จะรบกวนอีกรอบนะครับ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 5:53 pm
by pkhardman
อาจารครับ สูตร vba OK เลย
แต่รบกวนอีกสักครั้ง ถ้าผมจะให้มันลบ row ที่ไม่มีคำว่า BB ใน column "Q" เลยทำได้ยังไงครับ
เผอิญตอนนี่มี XDAFFYRA คำนี่ติดอยู่อีก 2 row ไม่อยากได้เลย แฮะๆ ผมแนบ Excel มาให้อีกรอบครับ สำหรับ vba
ขอบคุณล่วงหน้า.
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 9:17 pm
by 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
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Wed Sep 20, 2023 9:18 pm
by 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))
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Thu Sep 21, 2023 9:35 am
by pkhardman
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Thu Sep 21, 2023 10:32 am
by puriwutpokin
ทำได้ ลองทำมาเองดูก่อนครับ โดยเริ่มจากลองบันทึก Macro ดูแล้วเอาโค้ดไปปรับใช้ แล้วติดตรงไหนมาถามได้ในนี้ครับ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Thu Sep 21, 2023 10:57 am
by pkhardman
ิอาจารย์ครับผมทำได่้แล้ว เย้ๆ รบกวนดูและปรับสูตรให้หน่อยนะครับ มันจะมีช้าอยู่บ้างตอนที่
1 VBA ที่ 3 Delete Row ช้าหน่อย ปรับไงได้บ้าง
2. รบกวนดู vba ที่ 5 ครับ ถ้ามีจำนวนมากขึ้นแก้ยังไงได้ครับ เช็น เอาจาก "z2:Z9000" ทำแล้วมันขึ้น # calc!
3. ถ้าอยากให้มัน sum row สุดท้าย โดยที่เราแก้สูตรข้อ 2 แล้วทำไงครับ
อนาคต น่าจะมีรายการอีก เยอะ
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Thu Sep 21, 2023 12:50 pm
by 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
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Fri Sep 29, 2023 1:43 pm
by 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
Re: lookup ค่าที่ที่เหมือนกัน โดยที่เจอค่าที่ 1 แต่ไม่เอาจะเอาค่าที่ 2
Posted: Fri Sep 29, 2023 3:49 pm
by 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
