Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่าง

[khainui]

- จากไฟล์ตัวอย่างที่แนบ ใน Sheet Report Cell E 2 ใช้ validation list มี 2 คำถามครับ
1. จะทำอย่างไรให้เรียงลำดับจากน้อยไปมาก หรือมากไปน้อยครับ
2. ใน List จะมีข้อมูลที่เป็นช่องว่างมาด้วยเนื่องจากเป็นข้อมูลของสูตร ทำอย่างไรให้ใน List เป็นข้อมูล Value อย่างเดียวโดยไม่มีช่องว่างครับ

- ใน Sheet Database Column K ผมใช้สูตรเพื่อเรียงลำดับข้อมูลใหม่แต่มันไม่สามารถเรียงลำดับได้ครับ หาสาเหตุไม่เจอ

ขอบคุณครับ

[snasui]

ลองตามนี้ครับ

1. H2 ปรับสูตรเป็นด้านล่างเพื่อนำ Space ในข้อความออกไป
   =IF(ROWS(H$2:H2)>$I$2,"",INDEX(SUBSTITUTE(Dept," ",""),SMALL(IF(FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1),ROW(Dept)-MIN(ROW(Dept))+1),ROWS(H2:$H$2))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. J2 คีย์สูตรเพื่อให้ลำดับ
   =IF(H2="","",SUMPRODUCT(--(H2>$H$2:$H$15))+COUNTIF(H$2:H2,H2))
   Enter > Copy ลงด้านล่าง
3. K2 คีย์สูตรเพื่อเรียงใหม่
   =IF(H2="","",INDEX($H$2:$H$15,MATCH(ROWS(K$2:K2),$J$2:$J$15,0)))
   Enter > Copy ลงด้านล่าง

Note: เมื่อนำ Space ออกไปจากข้อความแล้วใน Validation จะนำค่านั้นมาค้นหาข้อมูล ตามตัวอย่างนี้จะค้นหาไม่เจอเพราะว่าไม่มีข้อมูลที่ตรงกัน สำหรับข้อมูลที่ตรงกันจะต้องมี Space หรือ วรรค อยู่ด้วย นอกจากจะเปลี่ยนจากต้นแหล่งแล้วสามาถที่จะเปลี่ยนโดยใช้เซลล์อื่นช่วย หรือเปลี่ยนโดย VBA หากต้องการจะทำเช่นนั้นให้ลองทำมาก่อน ติดตรงไหนค่อยถามกันครับ

[nattasiray]

สำหรับผมแล้ว คงหันมาใช้ Data Remove Duplicate List ครับ โดยใช้ VBA ควบคุมขั้นตอนการกระทำ ลองค้นหาใน Youtube ด้วยคำว่า date remove duplicates

[khainui]

ขอบคุณครับ เดี๋ยวขอลองไล่สูตรดูครับ

[khainui]

[*]J2 คีย์สูตรเพื่อให้ลำดับ
=IF(H2="","",SUMPRODUCT(--(H2>$H$2:$H$15))+COUNTIF(H$2:H2,H2))
Enter > Copy ลงด้านล่าง


Note: เมื่อนำ Space ออกไปจากข้อความแล้วใน Validation จะนำค่านั้นมาค้นหาข้อมูล ตามตัวอย่างนี้จะค้นหาไม่เจอเพราะว่าไม่มีข้อมูลที่ตรงกัน สำหรับข้อมูลที่ตรงกันจะต้องมี Space หรือ วรรค อยู่ด้วย นอกจากจะเปลี่ยนจากต้นแหล่งแล้วสามาถที่จะเปลี่ยนโดยใช้เซลล์อื่นช่วย หรือเปลี่ยนโดย VBA หากต้องการจะทำเช่นนั้นให้ลองทำมาก่อน ติดตรงไหนค่อยถามกันครับ

กรณีสูตร sumproduct หมายถึงนับจำนวนของข้อมูลในช่วง ของ h2 ถึง h15 ว่ามีข้อมูลกี่รายการที่น้อยกว่า h2 ใช่ไม๊ครับ แล้วจึงนำมาบวกกับสูตร countif ที่มีค่าเท่ากับ 1

กรณีลบ Space ออกไป มันไม่ได้ช่วยให้ใน validation list ไม่ดึงข้อมูลที่เป็นสูตรมาด้วยใช่ไม๊ครับ ควรใช้สูตรตัวไหนเพื่อให้สามารถนับจำนวนที่เป็นข้อมูลแต่ไม่นับเซลที่เป็นสูตรครับ

[snasui]

กรณีสูตร sumproduct หมายถึงนับจำนวนของข้อมูลในช่วง ของ h2 ถึง h15 ว่ามีข้อมูลกี่รายการที่น้อยกว่า h2 ใช่ไม๊ครับ แล้วจึงนำมาบวกกับสูตร countif ที่มีค่าเท่ากับ 1

ถูกต้องแล้วครับ

เมื่อนับไว้แล้วก็ไม่ต้องนับอีกครับ สามารถใช้สูตรที่ Dept_name เป็นด้านล่างครับ

=OFFSET(database!$H$1,1,0,database!$I$2,1)