Qiimaha nuqul^(Duplicate) ka mid ah xaashidaada Excel waxay khalkhal galin kartaa xogtaada. Haddi nuquladu yihiin kuwo ula kac ah mooyaane, waxay keeni karaan khaladaad iyo warbixin qalloocan.

Tababarkan, waxaan ku tusi doonaa sida loo helo oo looga saaro nuqul ka mid ah dukumeentigaaga Excel .

Sida loo Helo Saf ama Xog Kooban

Waa lagama maarmaan in marka hore la hubiyo safafka (ama tiirarka) ee leh macluumaad isku mid ah. Markaa ka hor intaanan ku tusin sida loo saaro nuqullada Excel , aan ku dhex marno habka aad xaashidaada u hubinayso xogta nuqul ka mid ah.

Habka 1: Baadh Warqadda Shaqada oo dhan^{(Method 1: Search Entire Worksheet)}

Excel waxa uu leeyahay qalab qaabayn shuruudaysan^{(Conditional Formatting tool)} kaas oo ka caawinaya in la aqoonsado, sawiro, oo laga soo saaro gabagabada xogta. Waa kuwan sida loo isticmaalo aaladda si loo muujiyo qiyamka nuqul ka mid ah dukumeentigaaga Excel .

1. Riix Control + A si aad u doorato liiskaaga ama miiskaaga. Haddii kale, dhagsii calaamadda Xulo All^{(Select All)} ee ku yaal geeska bidix ee sare ee miiska si aad u doorato dhammaan xaashida shaqada.

1. Tag Home tab oo dhagsii badhanka qaabaynta shuruuda^{(Conditional Formatting)} ah.

1. Ku dhaqaaq cursorkaaga kor u qaad Shuruucda Unugyada^{(Highlight Cell Rules)} oo dooro Qiimaha Kooban^{(Duplicate Values)} .

1. Hubi^(Make) in sanduuqa hoos-u-dhaca u horreeya uu akhriyo "Koobi." Waxa kale oo aad ka dooran kartaa midabka iftiiminta ee aad door bidayso liiska hoos-u-dhaca labaad.

1. Dooro OK si aad u sii wadato.

Excel wuxuu isla markiiba muujin doonaa safafka iyo tiirarka leh qiyamka nuqul ka mid ah.

Habka 2: Isku-darka Safka^{(Method 2: By Combining Rows)}

Habkani waxa uu ku fiican yahay in la helo safafka leh qiyamka koobabka ee dhammaan tiirarka ama unugyada. Marka hore^(First) , waxaad u baahan doontaa inaad isticmaasho shaqada "Concatenate" ee Excel si aad isugu geyso nuxurka saf kasta. Kadib, dooro tiirka meesha aad rabto in lagu kaydiyo qiimaha isku dhafan oo raac tillaabooyinka hoose. Waxaan kugula talineynaa in la isku daro qiyamka tiirka ku xiga qiimihii ugu dambeeyay ee safka koowaad.

1. Nooca ama paste ^(Enter)=CONCAT( CONCAT _ _ _^(Afterward) _

Xaashiyaha muunadda ee shaqada (fiiri sawirka hoose), unugyada kowaad iyo kan dambe ee safka kowaad waxay leeyihiin tixraaca A2 iyo D2, siday u kala horreeyaan. Haddaba^(Hence) , qaacidadu waxay noqon doontaa foomkan: =CONCAT(A2:D2) .

Xusuusnow^(Remember) , tixraacyada unugga way kala duwanaan doonaan iyadoo ku xiran tirada safafka iyo tiirarka miiska saaran.

1. Ku koobbi qaacidada hoos tiirka ilaa aad ka gaadhayso safka u dambeeya. Si aad tan u samayso, dooro unuga qaaciidada leh, u dhaqaaq curcurkaaga jiirka dhinaca geeska bidix ee hoose ee unugga, hay Buuxi sumadda gacanta^{(Fill handle)} (ie summada lagu daray), oo hoos u jiid tiirka.

1. Dooro tiirka oo dhan-ku dhufo xarafka sare ee tiirka ama dhagsii unugga tiirka oo tabo Control + Space .

1. Tag tabka Guriga oo dooro ^(Home)Qaabaynta Shuruuda^{(Conditional Formatting)} ah .

1. Ku dul rog curcurkaaga mouse-ka qaanuunka Unugyada^{(Highlight Cell Rules)} oo dooro qiimayaal nuqul^{(Duplicate Values)} ah.

1. Dooro OK si aad u sii wadato.

Excel wuxuu muujin doonaa tiirka leh qiimayaal nuqul ah. Taasi waxay kuu sheegaysaa unugyada safkaas gaarka ah ee leh qiimayaal nuqul ah sida saf kale oo xaashida shaqada ah.

Haddii aad si dhow u eegto sawirka kore, waxaad ogaan doontaa in Qalabka Qaabaynta Shuruuda^{(Conditional Formatting)} ah uusan muujin Safka 4^{(Row 4)} iyo Safka 13^{(Row 13)} . Labada safba waxay leeyihiin qiimayaal nuqul ah oo ku jira Magacyada^(Names) , Dhibcaha^(Scores) , iyo tiirarka aqoonsiga^(ID) , laakiin qiimayaal kala duwan ayaa ku jira tiirka Maalinta .^(Day)

Kaliya 3 ka mid ah 4 tiirar ee labada saf ayaa haysta macluumaad nuqul ah. Taasi waxay sharraxaysaa sababta Aaladda Qaabaynta Shuruuda^{(Conditional Formatting)} ah u muujin wayday qiimayaasha isku xidhan ama la isku daray ee labada saf. Labada saf ( Safka 4^{(Row 4)} iyo Safka 13^{(Row 13)} ) waa kuwo gaar ah sababtoo ah waxaa jira macluumaad kala soocida tiirka "Maalinta"

Sida Looga Saaro Safka Kooban^{(Duplicate Rows)} Ee Excel

Waxaad ka heshay safaf badan oo ay ku jiraan macluumaadka nuqul ka mid ah xaashidaada Excel . Aan ku tusinno sida looga saaro safafkan nuqul ka mid ah iyadoo la adeegsanayo laba qalab oo Excel ah.

1. Isticmaal qalabka "Ka saar Nuqullada"^{(1. Use the “Remove Duplicates” Tool)}

Qalabkani waxa uu leeyahay hal shaqo oo kaliya: si loo hubiyo in aad haysato xog nadiif ah xaashida shaqada ee Excel . Waxay tan ku gaadhaysaa isbarbardhigga tiirarka la doortay ee xaashidaada shaqada oo ka saarta safafka qiyamka nuqul ka mid ah. Waa kuwan sida loo isticmaalo qalabka:

• Dooro unug miiska saaran oo taabo Control + A ee kumbuyuutarkaaga si aad u muujiso miiska.

• Tag xogta^(Data) tab oo guji ka saar^{(Remove Duplicates)} calaamada nuqul ka mid ah qaybta "Data Tools".

Haddii kombuyutarkaaga uu leeyahay shaashad yar ama daaqadda Excel la yareeyey, dhagsii badhanka hoos u dhaca Qalabka Xogta oo dooro ^{(Data Tools)}Ka saar Nuqullada^{(Remove Duplicates)} .

• Dhex^(Columns) mara qaybta tiirarka oo dooro dhammaan tiirarka. Haddii miiskaagu leeyahay madax, calaamadi sanduuqa akhriya "My data has headers." Taasi waxay meesha ka saaraysaa safka madaxa ama safka kowaad ee xaashida. Guji ^(Click) OK si aad u sii wado

Talo degdeg ah:^{(Quick Tip:)} Si aad safka ugu horreeya ee xaashida shaqada ee Excel ka dhigto madax, tag View tab, dooro barafyada qaboojiyaha^{(Freeze Panes)} , oo dooro Barafka Sare ee Safka^{(Freeze Top Row)} .

• Excel waxa uu soo bandhigi doonaa isla markiiba ku ogaysiinaya wadarta qiyamka nuqul ee la helay lagana saaray xaashida. Guji ^(Click) OK si aad ugu noqoto xaashida shaqada.

2. Isticmaal Qalabka Filterka Sare^{(2. Use the Advanced Filter Tool)}

"Filter Advanced" waa qalab kale oo qurux badan oo kaa caawinaya inaad ku nadiifiso xogtaada gudaha Excel^{(clean your data in Excel)} . Qalabku wuxuu kuu ogolaanayaa inaad aragto, wax ka beddesho, kooxayso oo aad kala soocdo xogta xaashidaada shaqo^{(group and sort data on your worksheet)} . Raac tillaabooyinka hoose si aad u barato sida loo isticmaalo qalabkan si aad uga saarto safafka nuqul ka mid ah xaashidaada Excel .

• Dooro^(Select) unug kasta oo miiska saaran oo taabo Control + A si aad u muujiso miiska oo dhan.

• Tag xogta^(Data) tab oo dooro Advanced qaybta "Sort & Filter".

• Calaamadee sanduuqa kaliya ee diiwaanada gaarka ah^{(Unique records only)} oo guji OK .

Haddii shaxda ama xaashida shaqadu ay ka kooban tahay safaf badan oo leh macluumaad isku mid ah ama qiimayaal, Excel ayaa meesha ka saaraysa dhammaan marka laga reebo dhacdada ugu horreysa ee nuqullada.

Fiiro gaar ah:^(Note:) Qalabka Filter^{(Advanced Filter)} -ka Sare wuxuu si toos ah ula dhaqmaa safka koowaad sidii madax. Tani waxay ka dhigan tahay in qalabku aanu ka saari doonin safka hore, xitaa haddii ay ku jiraan macluumaad nuqul ah. Tusaale ahaan, shaxda hoose, socodsiinta " Diiwaanka Gaarka^(Unique) ah oo kaliya" ee qalabka Filterka Sare^{(Advanced Filter)} kama saarin safafka hore iyo kan u dambeeya-in kasta oo ay labadooduba leeyihiin qiimayaal nuqul ah dhammaan tiirarka.

Markaa, haddii xaashida shaqada ee Excel ama miiskaagu leeyahay madax, waxa fiican inaad isticmaasho aaladda “Ka saar Nuqullada” si aad u tirtirto safafka nuqulka ah.

Talo degdeg ah:^{(Quick Tip:)} Ma laga saaray safafka nuqulka ah ama qiyamka shil? Riix Control + Z si aad u soo celiso isbeddelka oo aad u soo celiso xogta nuqul ka mid ah.

Ka saarida nuqullada Excel : Xaddidaadaha

Waa inaan sheegnaa inaadan ka saari karin safafka ama qiyamka xaashida shaqada oo ka kooban xog la qeexay ama la kooxeeyay^{(outlined or grouped data)} . Markaa haddii aad isu geysay safafka iyo tiirarka xaashidaada Excel^{(grouped the rows and columns in your Excel worksheet)} , laga yaabee inaad isu geyso Totals and Subtotals, waa inaad kala saartaa xogta ka hor inta aadan hubin nuqullada. Tixraac dukumentigan rasmiga ah ee Microsoft^{(this official documentation from Microsoft)} si aad wax badan uga ogaato ka saarida nuqullada Excel iyo shaandhaynta qiyamka gaarka ah.

How to Remove Duplicate Rows in Excel

Duрlicate values in your Exсel worksheet can mess up your data. Unless duplicates are іntentional, they cаn cause inaccuracies and skewed reporting.

In this tutorial, we’ll show you how to find and remove duplicates in your Excel document.

How to Find Duplicate Row or Data

It’s essential to first check which rows (or columns) have identical information. So before we show you how to remove duplicates in Excel, let’s walk you through the process of checking your sheet for duplicate data.

Method 1: Search Entire Worksheet

Excel has a Conditional Formatting tool that helps to identify, visualize, and draw conclusions from data. Here’s how to use the tool to highlight duplicate values in your Excel document.

1. Press Control + A to select your list or table. Alternatively, click the Select All icon at the top-left corner of the table to select the entire worksheet.

1. Go to the Home tab and click the Conditional Formatting drop-down button.

1. Move your cursor over Highlight Cell Rules and select Duplicate Values.

1. Make sure the first drop-down box reads “Duplicate.” You can also select your preferred highlight color in the second drop-down menu.

1. Select OK to proceed.

Excel will immediately highlight rows and columns with duplicate values.

Method 2: By Combining Rows

This method is perfect for finding rows with duplicate values across all columns or cells. First, you’ll need to use Excel’s “Concatenate” function to combine the content of each row. Then, select the column where you want the combined values stored and follow the steps below. We recommend combining the values in a column next to the last value on the first row.

1. Type or paste =CONCAT( in the cell, select the first cell on row, type a column (:), and select the last cell on the row. Afterward, close the formula with a parenthesis and press Enter.

In our sample worksheet (see image below), the first and last cells on the first row have the reference A2 and D2, respectively. Hence, the formula will be this form: =CONCAT(A2:D2).

Remember, the cell references will vary depending on the number of rows and columns on the table.

1. Copy the formula down the column until you get to the last row. To do this, select the cell with the formula, move your mouse cursor to the bottom-left corner of the cell, hold the Fill handle icon (i.e. the plus icon), and drag it down the column.

1. Select the entire column—click the letter at the top of the column or click on a cell in the column and press Control + Space.

1. Go to the Home tab and select Conditional Formatting.

1. Hover your mouse cursor on Highlight Cell Rules and select Duplicate Values.

1. Select OK to proceed.

Excel will highlight the column with duplicates values. That tells you to the cells in that particular row that have duplicate values as another row on the worksheet.

If you look closely at the image above, you’ll notice that the Conditional Formatting tool did not highlight Row 4 and Row 13. Both rows have duplicate values in the Names, Scores, and ID columns, but different values in the Day column.

Only 3 out of 4 columns in both rows have duplicate information. That explains why the Conditional Formatting tool didn’t highlight the concatenated or combined values for both rows. Both rows (Row 4 and Row 13) are unique because there’s distinguishing information in the “Day” column.

How to Remove Duplicate Rows in Excel

You’ve found multiple rows containing duplicate information in your Excel worksheet. Let’s show you how to remove these duplicate rows using two Excel tools.

1. Use the “Remove Duplicates” Tool

This tool has only one job: to ensure you have clean data in your Excel worksheet. It achieves this by comparing selected columns in your worksheet and removing rows with duplicate values. Here’s how to use the tool:

• Select a cell on the table and press Control + A on your keyboard to highlight the table.

• Go to the Data tab and click the Remove Duplicates icon in the “Data Tools” section.

If your PC has a small screen or the Excel window is minimized, click the Data Tools drop-down button and select Remove Duplicates.

• Go through the Columns section and select all columns. If your table has a header, check the box that reads “My data has headers.” That’ll deselect the header row or the first row on the sheet. Click OK to proceed.

Quick Tip: To make the first row of an Excel worksheet a header, go to the View tab, select Freeze Panes, and select Freeze Top Row.

• Excel will display a prompt notifying you of the total duplicate values found and removed from the sheet. Click OK to return to the worksheet.

2. Use the Advanced Filter Tool

“Advanced Filter” is another brilliant tool that helps you clean your data in Excel. The tool lets you view, edit, group and sort data on your worksheet. Follow the steps below to learn how to use this tool to remove duplicate rows from your Excel worksheet.

• Select any cell on the table and press Control + A to highlight the entire table.

• Go to the Data tab and select Advanced in the “Sort & Filter” section.

• Check the Unique records only box and click OK.

If the table or worksheet contains multiple rows with similar information or values, Excel will remove all but the first occurrence of the duplicates.

Note: The Advanced Filter tool automatically treats the first row as a header. This means that the tool won’t remove the first row, even if it contains duplicate information. For instance, in the table below, running the “Unique records only” feature of the Advanced Filter tool did not remove the first and last rows—even though they both have duplicate values across all columns.

So, if your Excel worksheet or table has a header, it’s best to use the “Remove Duplicates” tool to eliminate duplicate rows.

Quick Tip: Removed duplicate rows or values by accident? Press Control + Z to revert the change and get back the duplicate data.

Removing Duplicates in Excel: Limitations

We should mention that you cannot remove duplicate rows or values from a worksheet containing outlined or grouped data. So if you grouped the rows and columns in your Excel worksheet, perhaps into Totals and Subtotals, you’ll have to ungroup the data before you can check for duplicates. Refer to this official documentation from Microsoft to learn more about removing duplicates in Excel and filtering unique values.

Related posts

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• Sida Taariikh ahaan loogu kala saaro Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida Loo Raadiyo Excel

• Farqiga u dhexeeya Microsoft Excel Online iyo Excel ee Desktop

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida Loo Isticmaalo Shaqada PMT ee Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida loo Furo Word iyo Excel si Badbaado ah

• Sida loo isticmaalo Flash Fill ee Excel

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• Hagaha VBA Sare ee MS Excel

• Waa maxay VBA Array ee Excel iyo Sida loo Barnaamijyo Mid