Automatically translated.View original post

ClearData, repeat, extract code and sort

2025/8/21 Edited to

... Read moreถ้าเจอ “CODE สินค้า” ยาวเป็นหางว่าว แถมมีซ้ำอีก งานที่คนมักค้นหาคือ “สูตรนับจำนวนซ้ำ excel” — อันนี้เป็นวิธีที่ผมใช้บ่อยเวลาอยากรู้ว่าโค้ดไหนซ้ำกี่ครั้ง และอยากได้ลิสต์โค้ดไม่ซ้ำที่เรียงให้พร้อมใช้งานเลย 1) นับจำนวนที่ซ้ำของแต่ละ CODE ด้วย COUNTIF - สมมติ CODE สินค้าอยู่คอลัมน์ A เริ่มที่ A4 ถึง A157 - ที่คอลัมน์ “จำนวนที่ซ้ำ” (เช่น B4) ใส่สูตร: =COUNTIF($A$4:$A$157,A4) แล้วลากลงทั้งคอลัมน์ ความหมายคือ: ให้ Excel ไปนับว่า “ค่าที่อยู่ใน A4” ปรากฏกี่ครั้งในช่วงข้อมูลทั้งหมด ทิปจากที่เคยพลาดเอง: ถ้าข้อมูลมีช่องว่างหัวท้าย (เช่น “ABC ”) ผลนับอาจเพี้ยน แนะนำทำคอลัมน์ช่วย (เช่น C4) ใช้ =TRIM(A4) แล้วค่อยนับจากคอลัมน์ที่ TRIM แล้ว จะชัวร์กว่าเวลาข้อมูลมาจากการก็อปปี้/ระบบอื่น 2) แยก CODE ที่ไม่ซ้ำ (เอาเฉพาะตัวแรกของแต่ละกลุ่ม) ขั้นนี้จะง่ายมากถ้า “เรียง Code ที่ได้” ก่อน - เลือกตาราง > Data > Sort > เลือกเรียง CODE A→Z จากนั้นสร้างคอลัมน์ “เลือก Code ไม่ซ้ำ” (เช่น C4) แล้วใส่: =IF(A4=A3,"",A4) ลากลง ไอเดียคือ: ถ้าแถวนี้เหมือนแถวบน (เพราะเรา sort แล้ว) ให้เว้นว่าง ไม่ต้องโชว์ซ้ำ แต่ถ้าไม่เหมือน ให้แสดง CODE 3) ดึงรายการ CODE ที่ไม่ซ้ำออกมาเป็นลิสต์ต่อเนื่อง (ไม่ใช่มีช่องว่าง) ถ้าคุณอยากได้ผลลัพธ์เป็นคอลัมน์รายชื่อ CODE ไม่ซ้ำเรียงลงมาแบบสวยๆ (เช่นคอลัมน์ E) ผมมักใช้สูตร SMALL ช่วยดึง “ตำแหน่งแถว” ที่มีค่า - ให้คอลัมน์ D เป็นตัวช่วยเก็บเลขแถวเมื่อ C มีค่า (ไม่ว่าง) เช่น D4: =IF(C4="","",ROW()) - แล้วที่คอลัมน์ E4 ดึงรายการไม่ซ้ำ: =IFERROR(INDEX($A:$A,SMALL($D$4:$D$157,ROW()-3)),"") หมายเหตุ: ROW()-3 ปรับตามแถวเริ่มต้นของคุณ (ถ้าเริ่มที่แถว 4 ก็ใช้ -3) 4) อยากเห็นเฉพาะตัวที่ “ซ้ำ” จริงๆ หลังทำ COUNTIF แล้ว คุณสามารถเปิด Filter ที่หัวตาราง แล้วกรองคอลัมน์ “จำนวนที่ซ้ำ” ให้มากกว่า 1 จะเห็นเฉพาะ CODE ที่ซ้ำทันที (ช่วยเคลียร์ data เร็วมาก) สรุปเวิร์กโฟลว์ที่ผมใช้: Sort CODE → COUNTIF นับซ้ำ → IF แยกไม่ซ้ำ → (ถ้าต้องการ) INDEX+SMALL ดึงลิสต์ไม่ซ้ำออกมาเป็นคอลัมน์ใช้งานต่อได้เลย

Related posts

Data analyst - what to study? 📚
Becoming a data analyst requires mastering a mix of technical, analytical, and communication skills. Here are five essential areas to study: 1. Data Analysis Tools and Software - Excel: Learn advanced features like pivot tables, VLOOKUP, and data visualization. - SQL: Master database querying t
vedha | career tips (tech) 👩‍

vedha | career tips (tech) 👩‍

1509 likes

Big Data Analytics techniques for Data Analysts
Hello everyone! I wanted to share why strategic steps in big data analytics are essential for extracting valuable insights. Comprehensive data collection ensures a complete dataset, while effectively cleaning and preprocessing data enhances accuracy. Moreover, advanced analytics techniques like mac
Yun Jung

Yun Jung

14 likes

A flat lay image featuring a pink notebook, gold heart-shaped dish with paperclips, and a gold pen, alongside a keyboard and pink flowers. The text overlay reads "5 FREE ONLINE CERTIFICATIONS to Boost YOUR RESUME."
The image displays the logo for "Learning How to Learn" with a brain and sun icon. Text describes this Coursera course from UC San Diego, focusing on improving learning techniques, memory, and study strategies.
The image shows the "Learn SQL" logo. Text describes Codecademy's free SQL course, emphasizing its importance for data analysis, database management, querying, creating tables, and manipulating data.
5 Free Online Certifications to Boost Your Resume
Hi, lemons! In today's competitive job market, having a standout resume is crucial. Employers seek candidates with not only relevant experience but also a commitment to continuous learning. Fortunately, the digital age offers a wealth of free online certifications that can enhance your skill se
Lifestyle Babe

Lifestyle Babe

744 likes

Four vanilla-scented fragrance products are displayed on a wooden surface, including a body oil, two perfumes, and a perfume oil. The image also features vanilla extract, vanilla beans, and ice cream, with text overlay "HOW TO SMELL LIKE Vanilla on steroids."
A bottle of SugarMama Fragrances White Corset Body Oil is shown, with text indicating it smells like Victoria's Secret Vanilla Lace. A small bottle of Victoria's Secret Vanilla Lace is also visible.
A small rollerball bottle of Lucky #9 Prr-hota Perfume Oil is displayed. Text indicates it smells like vanilla, accompanied by an image of vanilla extract and a vanilla flower.
How to smell like vanilla extract 🍨🤍
This fragrance combo is just vanilla on vanilla on vanilla and I love it😍😫 White Corset: This dry body oil is from an Etsy seller called SugarMama Fragrances and when I say she makes the best gourmand scents ever for super affordable I mean it. I love White Corset so much; it’s one of my favorit
Dani🦋✨

Dani🦋✨

1102 likes

100 Excel Tips
100 Excel Tips The best Shortcuts, Formulas, Formatting & Advanced Tips Bookmark this, you'll save 1,000 of hours! A list of tips that all professionals need. Here is a preview: ⌨️ Shortcuts 1. How to hide a column? Ctrl + 0 2. How to hide a row? Ctrl + 9 3. How to
ExpressoSolut

ExpressoSolut

1452 likes

Different paths for data analysts
Hello everyone! I think one of the merits of being a data analyst is that there are many different paths you can pursue! Data Analysts can specialize in different areas such as business intelligence, machine learning, or data visualization, allowing for diverse career trajectories and opportunities
Yun Jung

Yun Jung

177 likes

A desk setup with a laptop, lamp, and plant, featuring text overlay "DATA ANALYST 5 essential tools." A black cat sticker is on the laptop screen. This image serves as the title card for a list of data analysis tools.
A list detailing the first three essential data analyst tools: SQL, Excel, and Python. Each tool includes its purpose, a course recommendation, and platform, set against a background of white buildings with blue accents.
A list detailing the remaining two essential data analyst tools: Tableau and Power BI. Each tool includes its purpose, a course recommendation, and platform, set against a background of white buildings with blue domes.
Top five tools every data analyst should master
1. SQL 🐘 - Why: SQL is the backbone of data querying, helping you extract, manipulate, and analyze data directly from databases. It's fundamental for any data analysis workflow. - Course: SQL Fundamentals - Platform: DataCamp 2. Excel 📈 - Why: Excel is still invalua
vedha | career tips (tech) 👩‍

vedha | career tips (tech) 👩‍

133 likes

A Dollar Tree aisle displays various body wash bottles. Text overlays read 'Dollar Tree High-End Body Care Dupes Olay, Lush, Cetaphil and More,' with a money bag emoji, indicating budget-friendly alternatives.
A hand holds a white and gold bottle of b.pure Vitamin C Body Wash, labeled with 'B3 Vitamins' and 'Cleansing and Illuminating.' The caption suggests it's a gentle, fragrance-free option similar to Cetaphil.
Four body wash bottles are displayed: two 'Oil de Vie' 2-in-1 body washes for sensitive and dry skin, and two 'Pro Silk Body' lavender and chamomile body washes. The caption advises checking for moisturizing body washes like Olay.
Dollar tree dupes to save you money!! 🤑
Dollar Tree Body Care Dupes: A Budget-Friendly Option Dollar Tree often carries a variety of body care products that are similar to higher-end brands. While the exact availability can vary by location, here are some popular dupes: Body Wash Cetaphil: Look for gentle, fragrance-free options from
Ty 😍✨✌🏽

Ty 😍✨✌🏽

1226 likes

Free game
#gettoknowme #digitalproducttipsandtricks #freegame #marketaffiliating
sammismotivation

sammismotivation

85 likes

A tablet on a wooden stand displays the SciSpace website, with text overlay 'THESE AI WEBSITES DO WHAT CHATGPT CAN'T'. The desk includes a water bottle, timer, and decorative items.
The Yoodli AI interface is shown on a laptop and phone screens, highlighting features like real-time feedback for interviews, metrics on filler words, and public speaking practice.
Seseam AI's interface is displayed on a laptop and phone screens, emphasizing its human-like conversational voice, ability to pause, laugh, tell jokes, and help with language practice.
📌 ChatGPT but Better? Try These AI Sites
Yoodli 🎙️🧠 Yoodli is like having a speaking coach in your laptop. It gives real-time feedback on your tone, filler words, and pace as you practice interviews or speeches. I use it to prep for job interviews and even presentations, and it helps me track progress with data. Bonus: It shows your eye
emilie.studygram

emilie.studygram

1129 likes

Make Essays and research papers Less Painful
1. 📚 Zotero Zotero lowkey changed my life. I used to screenshot everything or open 20+ tabs and never go back to them. Now I just save what I need while I’m browsing—quotes, PDFs, web pages, everything. Plus, it handles citations for me in one click. No more stressing last-minute over formatti
emilie.studygram

emilie.studygram

25 likes

Importance of ETL in Data Analyst Role
Hello everyone! Implementing these SQL tips for ETL processes ensures efficient and reliable data management. Efficient extraction minimizes system load, while robust transformation ensures accuracy. Incremental loading reduces processing time, and error handling improves troubleshooting. Performan
Yun Jung

Yun Jung

21 likes

tips to maintain clear skin + summer makeup inspo
Achieve a radiant summer look with acne-fighting ingredients tips and summer makeup inspo 🫶 1. Salicylic Acid: Unclogs pores and reduces acne with its exfoliating properties. 2. Benzoyl Peroxide: Kills acne-causing bacteria and helps clear up existing breakouts. 3. Niacinamide: Reduces inf
anika🍒

anika🍒

315 likes

A close-up side profile of a person's face with clear skin, featuring text about acne-fighting ingredients for clear skin and new beauty products.
Three skincare products, a pink moisturizing gel-cream, a light blue brightening serum, and a dark blue hydrating serum, with text describing salicylic acid and benzoyl peroxide benefits.
Two skincare products, a light blue brightening serum and a dark blue hydrating serum, with text describing the benefits of niacinamide, tea tree oil, and green tea extract.
Acne-Fighting Ingredients for Clear Skin✨
Byoma’s new beauty products have beneficial ingredients for your skin! Here are some ingredients that have transformed my skin and kept acne away! 1. Salicylic Acid: Unclogs pores and reduces acne with its exfoliating properties. 2. Benzoyl Peroxide: Kills acne-causing bacteria and helps clea
anika🍒

anika🍒

66 likes

My Clear Skin Routine✨
I’ve struggled with acne for YEARS and i finally found my holy grail of skin care products! This is my skincare routine in order :)) lmk if you try/have tried them and tell me your thoughts! 🫶🏼 (all of these products are SUPER affordable!) #skincare #skincareroutine #skincareproducts #le
Ria Schuyler:)

Ria Schuyler:)

1380 likes

A woman with curly hair and glasses, holding a microphone, with text overlays 'SQL Windows Function Explained' and 'Mad Key'.
A bookshelf background with 'SQL Windows Function Explained' and a quote defining a window function as viewing a 'slice' of data while seeing the bigger picture.
A SQL code example using a window function (RANK() OVER) to rank sales, showing output with individual sales records and their ranks, maintaining detail.
SQL: Windows Function Explained
One thing I've discovered is that when working with data, Normal SQL (GROUP BY) helps summarize data but sacrifices row-level detail in the process. On the other hand, Window Functions allow you to perform calculations across rows while maintaining each row’s detail. This key difference m
NeLo

NeLo

58 likes

The Full Roadmap to Becoming a Data Analyst
If you’re starting completely from scratch and wondering how to become a data analyst, here’s the exact roadmap that actually works. It’s not about learning everything at once. It’s about learning the right things in the right order. Your journey begins with Excel. Excel isn’t ‘just a spreadshee
Analytical_Amos

Analytical_Amos

1 like

How I achieved Clear skin!
I am a person who has struggled with acne since I was 12! For the longest time, I thought it would never improve, but here I am, 7 years later, and my skin has been thriving lately! Disclaimer: I do take things to help with the Internal health of my skin!! Sometimes the problem is not the produ
Ady 🪩🪩🪩

Ady 🪩🪩🪩

490 likes

for clear skin
i’ve struggled with clear skin most my early teenage years . went through thousands of products ! these seem to be the ones that work the best for my skin type 🫧🤗. affordable and great results quick ! #clear skin #clearskinproducts #skincareroutine try it and lmk how it works for you !
Chaniya Madison

Chaniya Madison

201 likes

This image displays a "KETO MACROS" guide, detailing net carbs, fat, protein, and calories for various food categories. It includes condiments, fats, oils, nuts, seeds, meats, eggs, fruits, vegetables, seafood, and dairy, providing a comprehensive nutritional overview for a keto diet.
This image presents "KETO SUBSTITUTIONS" for common foods like bread and pasta, offering keto-friendly alternatives. It also features a "DRINKS & SAUCES" section, listing their serving size, weight, calories, fats, protein, and net carbs for keto diet planning.
This image provides detailed macro information for "DAIRY, CHEESES & CURDS" and "NUTS, NUT BUTTERS & OILS." It lists serving size, weight, calories, fats, protein, and net carbs for various items, guiding keto food choices.
KETO Diet
#ketotransformation #ketodiet #ketoweightloss #2025glowup #summerbod
M A R I A H

M A R I A H

105 likes

Any tips on using data to enhance your chance?
Hey everyone! I got laid off in February and started my new role 3 weeks ago, I turned 30+ interviews into an offer fast: nailing my niche and getting hyper-specific. 1. Ditch the “everything” approach The day I was let go, I listed every task I’ve ever done and asked: What made me excited? Wha
Zoe💌

Zoe💌

148 likes

The first image is a title slide on a laptop, asking 'Business Analyst vs Data Scientist What's are the differences?' with a 'Swipe' instruction, introducing a career comparison.
This image compares Business Analyst and Data Scientist roles, detailing 'Tools used' (Excel, SQL, Tableau for BA; problem-dependent for DS) and 'Stakeholders' (Executives for BA; Project managers for DS).
This image compares Business Analyst and Data Scientist roles, outlining their 'Focus' (business needs for BA; insights/predictions for DS) and 'Skillset' (data analysis for BA; programming, ML, statistics for DS).
Business Analyst vs Data Scientist: Key difference
Hello everyone! While both roles involve working with data, there are distinct differences in their key responsibilities. Business analysts focus on understanding business needs, gathering requirements, and translating them into actionable insights to drive decision making. Data scientists, on the
Yun Jung

Yun Jung

18 likes

Recipe for a clear,Healthy and Hydrated skin
Anizabelle

Anizabelle

6 likes

An infographic titled "7 Essential Data Analyst Skills" listing SQL and NoSQL, R, Python, MATLAB, Data Visualization, Microsoft Excel, Critical Thinking, Math and Statistics, and Communication, each with an illustrative icon.
Data Analysis
To become a data analyst, you'll need skills in data analysis and visualization tools like Python, R, or SQL. Additionally, proficiency in statistical analysis, critical thinking, problem-solving, and strong communication skills are essential. Familiarity with machine learning techniques and da
Randy

Randy

12 likes

A woman in a yellow top smiles while holding a tablet, with text overlay "AI Recommendations To Simplify Your Life." The background shows a modern interior with shelves. The Lemon8 logo and username are visible at the bottom.
A list of seven AI tools for data analysis is displayed, including Dabot.ai for Data Ops and Remus for Predictive Analytics. A woman is partially visible in the bottom left corner. The Lemon8 logo and username are also present.
A woman smiles and gestures with her hands, with text overlay advising on selecting data analysis tools based on specific needs and understanding data. Another text overlay encourages liking, following, and sharing for more content. The Lemon8 logo and username are at the bottom.
How to use AI to analyze data
We are in the Al Era and of course the way we collect, store and analyze data is going to change with Al. I've got a cheat code for current Data Aspirants and Enthusiats. I am all for Al adoption so if you are interested in exploring Al tools for data analysis, Here are 7 tools that you can
NeLo

NeLo

4 likes

See more