เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

[Nutta]

ต้องการเช็คสถานะใน col E ว่าลูกค้าคนไหนที่มีการติดต่อเข้ามาสอบถาม(New Inquiry,Existing Inquiry) แล้วยังไม่ได้กลายมาเป็นคนไข้(New Patient,Existing Patient) โดยกำหนดว่าถ้าเกิน 2 weeks จากวันที่ใน col A ให้นำข้อมูลใน col B,C,D,E,F มาแสดงใน sheet 2 ตามลำดับ โดยข้อมูลที่ซ้ำนำมาแสดงเพียงค่าเดียว
ไม่ทราบว่าสามารถใช้สูตรในการหาคำตอบได้หรือเปล่าค่ะ หรือว่าต้องเขียน VBA และถ้าต้องเขียน VBA รบกวนแนะนำเป็น guideline ให้หน่อยค่ะ เพราะไม่มีความรู้เกี่ยวกับ VBA เลยค่ะ

[DhitiBank]

ใช้สูตรได้ครับ แต่ขอสอบถามเพิ่มเติมหน่อยนะครับ

1. ในไฟล์แนบ ชีทที่ 2 เป็นตัวอย่างคำตอบที่ถูกต้องแล้วใช่ไหมครับ หากใช่ ทำไม Nuttakarn ถึงต้องดึงรายชื่อมาด้วยครับ เพราะจากวันที่เริ่มติดต่อคือ 1/1/2016 และกลายเป็น New patient วันที่ 3/1/2016 ผ่านไปแค่ 2 วันเอง ยังไม่ถึง 2 สัปดาห์เลยครับ

2. ต้องการเอาข้อมูลมาแสดงแค่ 3 คอลัมน์ คือ Enquiry Date, PT Name และ Type เหมือนในตัวอย่างใช่ไหมครับ หรือว่าต้องการดึงมาเหมือนที่อธิบายด้านบน

หากไฟล์แนบยังไม่ใช่คำตอบที่ต้องการ รบกวนใส่สิ่งที่ถูกต้องลงมาในชีท 2 เลยครับ จะได้เขียนสูตรคำนวณเทียบได้ครับ

[Nutta]

พอดีตอนทำไฟล์มึนนิดหน่อยค่ะ หาวิธีทำมาทั้งวัน ^^
คำตอบต้องการผลลัพธ์ตามที่เขียนอธิบายไว้ค่ะ แก้ไขและแนบไฟล์ใหม่เรียบร้อยแล้วค่ะ

[snasui]

ตัวอย่างสูตรครับ

1. Sheet1 เซลล์ G2 คีย์
   =IF(ISNUMBER(SEARCH("Patient",E2)),FALSE,IFERROR(INDEX($A$2:$A$13,MATCH(1,IF($B$2:$B$13=B2,IF($F$2:$F$13=F2,IF(ISNUMBER(SEARCH("Patient",$E$2:$E$13)),1))),0))-A2>=14,TRUE))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. Sheet2 เซลล์ A2 คีย์
   =IFERROR(INDEX(Sheet1!A$2:A$13,SMALL(IF(FREQUENCY(IF(Sheet1!$G$2:$G$13,MATCH(Sheet1!$B$2:$B$13,Sheet1!$B$2:$B$13,0)),ROW(Sheet1!$A$2:$A$13)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$13)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"")
   Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

!

Note: Ctrl+Shift+Enter หมายถึง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[Nutta]

ขอบคุณค่ะ ได้คำตอบที่ต้องการแล้วค่ะ
มีเรื่องสอบถามเพิ่มเติมค่ะ
1.ถ้าเซลล์ใดใน sheet1 ไม่มีข้อมูล เมื่อแสดงผลลัพธ์ใน sheet2 ให้แสดงเป็น blank
2.ในการทำงานข้อมูลจะมีจำนวนมากและเพิ่มขึ้นเรื่อยๆ แล้วทำให้การประมวลผลช้า มีวิธีที่จะทำให้การประมวลผลเร็วขึ้นบ้างไหมค่ะ เพราะข้อมูลไม่สามารถแยกเป็นไฟล์ย่อยได้ เนื่องจากจะมีการเช็คสถานะของรายชื่ออยู่ตลอด

[DhitiBank]

ไม่แน่ใจจะทำให้เร็วขึ้นหรือเปล่านะครับ ลองใช้คอลัมน์ช่วยดูครับ

1. ที่ Sheet1
1.1 G1 คีย์
=COUNTA(A1:A10000)

1.2 H2 คีย์
=MATCH(B2,B3:INDEX(B:B,$G$1),0)

1.3 I2 คีย์
=IF(COUNTIF(E2,"*Inquiry"),IF(ISNUMBER(H2),INDEX(A2:INDEX(A:A,$G$1),H2+1)-A2,15),0)

1.4 J2 คีย์
=IF(I2<=14,"",IF(COUNTIFS(B$2:B2,B2,I$2:I2,">=14")>1,"",ROWS(J$2:J2)))

1.5 คัดลอกสูตรทั้งหมดลงด้านล่างจนสุดตารางในชีท 1

2. ที่ Sheet2
2.1 A2 คีย์
=IFERROR(INDEX(Sheet!A$2:INDEX(Sheet1!A:A,Sheet1!$G$1),SMALL(Sheet1!$J$2:INDEX(Sheet1!$J:$J,Sheet1!$G$1),ROWS(A$2:A2))),"")

2.2 สำหรับตรงไหนที่เป็น 0 หากอยากให้เป็นค่าว่างก็ตั้งค่าใน Format cell ครับ
เลือกเซลล์ -> คลิกขวา -> format cell -> number -> custom -> ช่อง type คีย์ 0;[Red]-0;

[Nutta]

ขอบคุณ คุณ DhitiBank ค่ะ
มีเรื่องจะสอบถามค่ะว่า จากสูตรที่อาจารย์ snasui เขียนมาให้ ใน sheet2 คำตอบที่ได้มาจากการนับวันที่จากข้อมูลล่าสุดของรายชื่อที่ซ้ำว่าเกิน 2 weeks หรือจากข้อมูลแรกของรายชื่อค่ะ และมีโจทย์เพิ่มมาให้ช่วยอีกแล้วค่ะ
1.จากตอนแรกที่แจ้งว่าผลลัพธ์ใน sheet2 ให้นำข้อมูลที่รายชื่อซ้ำกันมาแสดงแค่ค่าเดียว แต่ตอนนี้ต้องการให้แสดงทุกค่าค่ะ ตามไฟล์แนบค่ะ
2.ต้องการให้ excel อัพเดทข้อมูลใน sheet2 โดยอัตโนมัติ โดยที่ตั้งเวลาอัพเดทและมีการแจ้งเตือนทุกๆ 2 weeks สามารถทำได้ไหมค่ะ

[snasui]

จากสูตรที่อาจารย์ snasui เขียนมาให้ ใน sheet2 คำตอบที่ได้มาจากการนับวันที่จากข้อมูลล่าสุดของรายชื่อที่ซ้ำว่าเกิน 2 weeks หรือจากข้อมูลแรกของรายชื่อค่ะ

นับจากวันที่ของบรรทัดนั้น ๆ ว่ามากกว่าหรือเท่ากับ 14 วันหรือไม่ ไม่ใช่นับจากบรรทัดใดบรรทัดหนึ่งของชื่อที่ซ้ำกันครับ

สำหรับคำถามใหม่ ปรับสูตรที่ Sheet2 เซลล์ A2 เป็น

=IFERROR(INDEX(Sheet1!A$2:A$15,SMALL(IF(Sheet1!$G$2:$G$15,ROW(Sheet1!$G$2:$G$15)-ROW(Sheet1!$G$2)+1),ROWS(A$2:A2))),"")

Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

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

[Nutta]

ขอบคุณค่ะ อาจารย์ snasui แล้วถ้าต้องการให้เช็คจากค่าสุดท้ายของชื่อที่ซ้ำกันสามารถทำได้ไหมค่ะ
และคำถามในข้อที่ 2 ที่ต้องการให้ excel อัพเดทข้อมูลใน sheet2 โดยอัตโนมัติ โดยที่ตั้งเวลาอัพเดทและมีการแจ้งเตือนทุกๆ 2 weeks สามารถทำโดยวิธีไหนได้บ้างค่ะ รบกวนอาจารย์ช่วยแนะนำด้วยค่ะ

[snasui]

ขอบคุณค่ะ อาจารย์ snasui แล้วถ้าต้องการให้เช็คจากค่าสุดท้ายของชื่อที่ซ้ำกันสามารถทำได้ไหมค่ะ

ทำได้ครับ ในส่วนที่เป็น -A2 ก็ต้องปรับเป็นเช่น

-MAX(IF($B$2:$B$13=B2,IF($F$2:$F$13=F2,IF(ISNUMBER(SEARCH("Inquiry",$E$2:$E$13)),$A$2:$A$13))))

และคำถามในข้อที่ 2 ที่ต้องการให้ excel อัพเดทข้อมูลใน sheet2 โดยอัตโนมัติ โดยที่ตั้งเวลาอัพเดทและมีการแจ้งเตือนทุกๆ 2 weeks สามารถทำโดยวิธีไหนได้บ้างค่ะ รบกวนอาจารย์ช่วยแนะนำด้วยค่ะ

การตั้งเวลา Update และแจ้งเตือน ที่กล่าวถึงนั้น Update อะไรและแจ้งเตือนอะไร และมีลักษณะอย่างไรครับ สำหรับสูตรสามารถที่จะ Update ได้ Real time อยู่แล้ว การตั้งเวลา Update จะต้องพึ่งการเขียนโปรแกรม การตั้งเวลาแจ้งเตือนก็เช่นเดียวกัน ลองเขียนมาเองก่อน ติดแล้วค่อยถามกันต่อครับ

[Nutta]

ขอบคุณค่ะอาจารย์ ยังไงถ้าเขียนโปรแกรมแล้วติดขัดตรงไหนจะมาสอบถามใหม่ค่ะ

[Nutta]

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

[snasui]

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

สูตรนั้นตอบคำถามนี้ที่ว่าให้นำค่าซ้ำมาแสดงด้วยครับ

1.จากตอนแรกที่แจ้งว่าผลลัพธ์ใน sheet2 ให้นำข้อมูลที่รายชื่อซ้ำกันมาแสดงแค่ค่าเดียว แต่ตอนนี้ต้องการให้แสดงทุกค่าค่ะ ตามไฟล์แนบค่ะ

ตามที่ทำตัวอย่างมาล่าสุดช่วยอธิบายวิธีคิดว่าคำตอบที่ยกตัวอย่างมานั้นมีวิธีคิดอย่างไรจึงนำมาแสดงเช่นนั้น เหตุใดไม่รวม PT Name ที่ชื่อ Decha ครับ

[Nutta]

สาเหตุที่ไม่รวม decha เนื่องจากครั้งล่าสุดที่ decha ติดต่อมา ยังไม่เกิน 2 สัปดาห์ค่ะ คำตอบที่ต้องการคือ ให้แสดงรายชื่อที่สถานะยังไม่ได้เปลี่ยนเป็น patient โดยมีเงื่อนไขว่าเช็คจากครั้งล่าสุดที่รายชื่อนั้นๆติดต่อมาแล้วเกิน 2 สัปดาห์ค่ะ ถ้าก่อนหน้านี้อธิบายแล้วไม่ชัดเจนต้องขอโทษด้วยค่ะ

[snasui]

ที่ว่าไม่เกิน 2 สัปดาห์นั้นคิดจากวันไหนและคิดอย่างไรครับ จากภาพด้านล่างจะเห็นว่า บรรทัดล่าง มากกว่าบรรทัดแรกเกิน 2 สัปดาห์จึงถูกนำมาแสดงด้วยครับ

[Nutta]

คิดจากครั้งล่าสุดเทียบกับวันที่ปัจจุบันแล้วเกิน 2 สัปดาห์ค่ะ ไมได้เทียบกับครั้งก่อนหน้าค่ะ

[snasui]

เซลล์ G2 ปรับสูตรเป็นด้านล่างครับ

=AND(COUNTIFS($B$2:$B$15,B2,$E$2:$E$15,"*Patient",$F$2:$F$15,F2)=0,ISNUMBER(SEARCH("Inquiry",E2)),TODAY()-MAX(IF($B$2:$B$15=B2,IF($F$2:$F$15=F2,$A$2:$A$15)))>=14)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[Nutta]

ขอบคุณค่ะ ได้รับคำตอบที่ต้องการแล้วค่ะ

[Nutta]

มีเรื่องสอบถามเพิ่มเติมค่ะ
1. ในตอนนี้มีชุดข้อมูลลักษณะตามไฟล์แนบใน sheet1 สิ่งที่ต้องการคือ ต้องการให้นำข้อมูลใน sheet1 ไปแสดงใน sheet2 โดยมีเงื่อนไขว่า ถ้าใน col K ไม่มีการกรอกข้อมูล และวันที่ใน col B เมื่อนับจนถึงวันที่ปัจจุบันต้องเกิน 335 วัน ซึ่งจากข้อมูลจะเห็นว่าใน row3,4,5,6 เซลล์ที่เป็นเซลล์ว่าง จะใช้ข้อมูลจาก row2 และ row8,9 เซลล์ที่เป็นเซลล์ว่าง จะใช้ข้อมูลจาก row7 และผลลัพธ์ที่ต้องการคือค่าใน col L ค่ะ
ตัวอย่างเช่นในเซลล์ L4 คำตอบเป็น TRUE เพราะในเซลล์ K4 ยังไม่มีการกรอกข้อมูลและวันที่ในเซลล์ B2 เมื่อนับจนถึงวันที่ปัจจุบันเกิน 335 วัน ส่วนในเซลล์ L9 คำตอบเป็น FALSE เพราะวันที่ในเซลล์ B7 เมื่อนับจนถึงวันที่ปัจจุบันยังไม่เกิน 335 วัน ถึงเเม้ในเซลล์ K9 จะยังไม่มีการกรอกข้อมูลก็ตาม
2. ใน sheet2 จะเป็นข้อมูลที่ดึงมาจาก sheet1 คำถามคือต้องการนับเซลล์ว่างใน col K จาก sheet1 ว่ามีจำนวนเท่าไร โดยมีเงื่อนไขว่าวันที่ใน col B ใน sheet1 เมื่อนับจนถึงวันที่ปัจจุบันต้องเกิน 335 วัน และแยกเป็นแต่ละวันที่ แต่ละรายชื่อ คำตอบตามไฟล์แนบค่ะ

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

[snasui]

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

1. เซลล์ L2 คีย์สูตร
   =AND(K2="",TODAY()-LOOKUP(9.99999999999999E+307,B$2:B2)>335)
   Enter > Copy ลงด้านล่าง
2. เซลล์ M2 คีย์สูตร
   =IF(L2,LOOKUP(9.99999999999999E+307,B$2:B2),"")
   Enter > Copy ลงด้านล่าง
3. เซลล์ N2 คีย์สูตร
   =IF(L2,LOOKUP(2,1/(E$2:E2<>""),E$2:E2),"")
   Enter > Copy ลงด้านล่าง
4. เซลล์ O1 คีย์เลข 0
5. เซลล์ O2 คีย์สูตร
   =IF(AND(N2<>"",COUNTIFS(M$2:M2,M2,N$2:N2,N2)=1),LOOKUP(9.99999999999999E+307,O$1:O1)+1,"")
   Enter > Copy ลงด้านล่าง

ที่ Sheet2

1. เซลล์ A2 คีย์สูตร
   =IF(ROWS(A$2:A2)>LOOKUP(9.99999999999999E+307,Sheet1!$O$2:$O$1000),"",LOOKUP(ROWS(A$2:A2),Sheet1!$O$2:$O$1000,Sheet1!$M$2:$M$1000))
   Enter > Copy ลงด้านล่าง
2. เซลล์ C2 คีย์สูตร
   =IF(ROWS(C$2:C2)>LOOKUP(9.99999999999999E+307,Sheet1!$O$2:$O$1000),"",LOOKUP(ROWS(C$2:C2),Sheet1!$O$2:$O$1000,Sheet1!$N$2:$N$1000))
   Enter > Copy ลงด้านล่าง

ส่วนคอลัมน์ D ของ Sheet2 มีวิธีคิดอย่างไร แจ้งมาด้วยครับ