A new line is also known as a Line Feed. Cú pháp. Clear searchREGEXEXTRACT function exact match. 8 million professionals use CFI to learn accounting, financial analysis, modeling and more. 0. Clear searchTake the 0 from the 2nd column of the second array and multiply it by each row of the 2nd column of the 1st array. But you can't see it. Kombinerer teksten fra flere strenge og/eller matrixer med et specificerbart afgrænsningstegn mellem de respektive tekster. I want to summarize all their names exclude the device to a cell on a new table. This help content & information General Help Center experience. Can this regex to find numbered lines of text in an Excel cell be improved to avoid false matches?1. Du kan selv angi hvilket skilletegn som skal brukes mellom tekstene. One such function is TEXTJOIN, which, as the name implies, allows you to join text using a separator. Hello there Sheets =TEXTJOIN(" ", TRUE, A2:A6) 10: Hello there Sheets. TEXTJOIN (delimiter, ignore_empty, text1, [text2,. Explanation. This help content & information General Help Center experience. Hàm TEXTJOIN, giống như hàm VLOOKUP với nhiều kết quả phù hợp - Hướng dẫn sử dụng Google Trang tínhTrong Google Sheet, bảng dữ liệu sẽ được tách thành. Using Column Letter as Identifier. 46. if you want to join stuff in google sheets use. Select the cell where you want to insert the symbol. There are two types of cell references: relative and absolute. BAHTTEXT. 2. John F. Connect and share knowledge within a single location that is structured and easy to search. To generate the child inventory, we need to create a cartesian join in Google Sheets of 6 fields, 5 of them have comma separated lists which need to be split and then joined together again within the cartesian join. Google Sheets function list; 2 of 43. Sample Usage TEXTJOIN(“ “, TRUE, “hello”, “world”) TEXTJOIN(“, ”Google Sheets: Textjoin from another sheet not working. Detecting Duplicates in a Google Sheet with ARRAYFORMULA. Use apostrophe at the start of a formula to turn it into a text string, which won’t execute. If empty, text will be simply concatenated. 10, 8. Use the TEXTJOIN function to merge two or more cells in google sheet. 1. TEXTJOIN; 43 de 43. This decimal value can be used in the CHAR (. Step 3: Now enter the delimiter in “”. For instance, a business could use Textjoin to concatenate detailed customer feedback captured using a survey form and later print or present the data in a single document. Sorry for the late reply. Note: Google Sheets users can use the TEXTJOIN Function, but have a slightly different way of entering array formulas. Attach your screenshot, Excel file, or Google Sheets link. TEXTJOIN (delimitador; ignorar_vazias; texto1; [texot2;. g. Related. Both of the below formulas return the same outputs. 35 6 6 bronze badges. Use the below formula in cell B2. This tutorial will demonstrate how to combine cells with a comma in Excel and Google Sheets. g. Google Sheets trying to match multiple columns but the results make no sense. The above steps would rotate up the text vertically and. Multiple IF, ARRAYFORMULA, TEXTJOIN Returning FALSE. In order to execute this function, I copy the formula, go to a cell, press "="+ Paste + Enter and it will be executed. The TEXTJOIN function takes three required arguments : delimiter , ignore_empty, and text1. Clear searchI have added two new sheets: "Erik Help" and "Authors". google-sheets; spreadsheet; transpose; textjoin; Share. Improve this question. JOIN results of VLOOKUP into one string in Google Sheets. IMPORTXML. I'd suggest you to split your data into several cells. 2. TEKST (BAHTTEXT) Odwiedź Centrum edukacji. Those names also appear in D, with the matches appearing in E-J. Merge Sheets Function by Color Remove Duplicates Compare Sheets Substring Batch Toolkit Table Styles Advanced Find and Replace See all products. To get the output you want: 03:00 text 1. Trying to get rows on Master sheet to combine into single cells on 2nd Sheet based on which date range they fall into. Search. Yes, by FILTER ing the array: = JOIN ( " vs " ; FILTER (C10:C14; NOT (C10:C14 = "") )) Thus, the JOIN method will only operate on non-empty cells. And in this tutorial, you are going through some of the most important ways to do this. . I have several tables that represent rooms in our office, each table includes a column of few cells contain workers names and a device named like the following "Device A", "device B" etc. This help content & information General Help Center experience. See syntax, examples, and tips for different scenarios, such as including blanks, ignoring blank cells, using conditions, and using different delimiters. 1. TEXTJOIN Function. To add a line break in a: Cell: use Ctrl + Enter (for Windows) or Ctrl + Return (for Mac) Formula: concatenate with the CHAR function (e. Multiple IF, ARRAYFORMULA. Tap the cells in the order you want to combine the cells. Forgot to say, the IFERROR is optional in this formula. Here we are using the AVERAGE function. I want to have my TEXTJOIN formula filled when i add new rows. Using Byrow, one of the Lambda helper functions, we can expand the result of the text functions mentioned at the beginning of this tutorial. Search. =ARRAYFORMULA (REGEXEXTRACT (SPLIT (B2, " "),TEXTJOIN ("|", TRUE, A2:A. TEXTJOIN is useful when consolidating data from multiple cells into one. TEXTO. 2. Inserting CONCATENATE and IF Functions. So far so good. The reusable LAMBDA can be passed either as a LAMBDA function or a named function. Therefore, the decimal value of a Line Feed is 10. The practical application is to list and count the scenes in a movie that a particular character appears in. Then, formulas within the current spreadsheet can simply refer to the data in the "Author" page, instead of using more IMPORTRANGE references. In the cell where you want the result of the lookup function, type the IFERROR function. Use the TEXTJOIN function to merge two or more cells in google sheet. To merge cells in Google Sheets, please follow the steps below: Select the cells you want to merge. Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. 0. Using Google products, like Google Docs, at work or school? Try powerful. 0. Right now, it lists 1 image per line: SKU001 Image001_1. Google Sheets - Query - Concatenate text to returned data. If you want more than 50,000 characters in a single cell, you can use QUERY's header clause. TextJoin has a possibility to set a delimiter - but this delimiter is set after every value. =query (A1:C,"Select * where not A matches '"&TEXTJOIN ("|",true,E1:E)&"'") You must prefix the NOT operator to the column identifier, not a suffix. Clear searchThis help content & information General Help Center experience. In this case a “space”. Hello there Sheets =TEXTJOIN(" ", TRUE, A2:A6) 10:. This step by step tutorial will assist all levels of Excel users in concatenating values in the table with a defined delimiter. Select the range of cells that you want to concatenate with line break, and then click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot: 2. Follow asked Aug 15, 2017 at 2:33. we finish using an ARRAYFORMULA which actually is necessary for the TRIM function. If it were a tshirt, it would show xs,s,m,l,xl with colours in a list in an other field b,r,g,y (blue, red, green, yellow). Hot Network Questions Sci-fi novel with alternate reality internet technology called 'Weave' What is the origin of the Vorpal Sword?. 3. To create the whole result table in one go without the need to fill down, use the recently. Some of you may wish to Combine cells to eliminate duplicate strings in Sheets. 2. Use the shortcut: Alt + Enter (Windows) or Cmd + Enter (Mac). Lyber Lyber. google sheets regextract nth occurence on new line. This help content & information General Help Center experience. Clear searchSTACKARRAY() for Google Sheets 𝗦𝗜𝗠𝗣𝗟𝗘 𝗕𝗨𝗧 𝗨𝗦𝗘𝗙𝗨𝗟 𝗙𝗨𝗡𝗖𝗧𝗜𝗢𝗡 𝗧𝗢 𝗧𝗥𝗔𝗡𝗦𝗙𝗢𝗥𝗠 𝗔 𝗠𝗨𝗟𝗧𝗜-𝗖𝗢𝗟𝗨𝗠𝗡 𝗔𝗥𝗥𝗔𝗬 𝗜𝗡𝗧𝗢 𝗔 𝗦𝗜𝗡𝗚𝗟𝗘-𝗖𝗢𝗟𝗨𝗠𝗡. =query (A1:M5,"Select A, B, C") Using Column Numbers as Identifier. Funktionen UNICHAR. The formula in cell. 2. After that I just fed that to your JOINTEXT formula. And so on. This post explores the Google Sheets REGEX formulas with a series of examples to illustrate how they work. EQ; 6 of 18. Hello there Sheets =TEXTJOIN(" ", TRUE, A2:A6) 10: Hello there Sheets. Reference: Files you can store in Google Drive Google Sheets - TEXTJOIN with multiple Criteria. . Harun24hr Harun24hr. g. In a Google Sheets file, I have a column contain text and numbers. Google Sheet Query - WHERE clause to result from another query. We can use the Ampersand Operator (&) to concatenate text and formulas in Google Sheets. TEXTJOIN(“ “, TRUE, “hello”, “world”) TEXTJOIN(“, ”, FALSE, A1:A5) Syntax. Here the pipe character (which means 'or' in regex) is used. JOIN. Learn how to use TEXTJOIN Function to return multiple matching results, which is something you wouldn't be able to do using VLOOKUP function. Lista de funções do Google Sheets; 2 de 43. The Replace with input will be left blank as this will cause the commas to be replaced with a blank!I have 2 cells with data inside of them. The resulting array needs to both INCLUDE commas in the first half, as well as EXCLUDE commas towards the end of the same formula. Is it possible to get some type. function, which returns an address in text format. 您有在公司或學校使用 Google 文件等 Google 產品嗎？ 快來試試實用的秘訣、教學課程和範本，瞭解如何在不安裝 Office 的情況下處理 Office 檔案、建立動態專案計劃和小組日曆、自動整理收件匣及進行其他作業。 You can use the following basic formula to concatenate a range of cells with a comma in Google Sheets: =TEXTJOIN(", ", TRUE, A2:C2) This particular example concatenates each cell in the range A2:C2 with a comma in between the characters of each cell. Esempi di utilizzo TEXTJOIN(“ “; VERO; “ciao”; “mondo”) TEXTJOIN(“; ”; FALSO; A1:A5) S. =TEXTJOIN("",TRUE,A1) Done. Simply put a NOT (NOT logical operator) before the column to search. At this moment, it match anyting similar. Bantuan Editor Google Dokumen. I noticed that another community member has already answered your other question. Clear search STACKARRAY() for Google Sheets 𝗦𝗜𝗠𝗣𝗟𝗘 𝗕𝗨𝗧 𝗨𝗦𝗘𝗙𝗨𝗟 𝗙𝗨𝗡𝗖𝗧𝗜𝗢𝗡 𝗧𝗢 𝗧𝗥𝗔𝗡𝗦𝗙𝗢𝗥𝗠 𝗔 𝗠𝗨𝗟𝗧𝗜-𝗖𝗢𝗟𝗨𝗠𝗡 𝗔𝗥𝗥𝗔𝗬 𝗜𝗡𝗧𝗢 𝗔 𝗦𝗜𝗡𝗚𝗟𝗘-𝗖𝗢𝗟𝗨𝗠𝗡. answered Dec 15. in a merged cell of A1:B1, A1. BLANKE. google-sheets; filter; google-sheets-formula; textjoin; Share. If you need to combine many cells into a single cell, then using TEXTJOIN could be a huge timesaver. Q&A for work. 0. Editor Google Dokumen. In one column (B) I have cells occupied with different themes. 0. Query Formula without Nesting: =query (I3:J,"Select I where J matches 'V1|V3'") Assume “V1” is in cell L1 and “V3” is in cell M1. Kombinerer teksten fra flere strenge og/eller matrixer med et specificerbart afgrænsningstegn mellem de respektive tekster. Search. I did it on a new Sheet on the Spreadsheet that i took the liberty to create (hope it's ok) For getting the colors I used QUERY: so it came out as QUERY (Data!B:G,"select G where B = '"&A2&"'". Featured on Meta Update: New Colors Launched. The user can specify the choice of a delimiter. I used ARRAYFORMULA but it doesn't work as expected. 3. 1. If the delimiter is an empty text string, this function will effectively concatenate. 2. Reference data on the same Sheet when possible. NUMERO. Learn more about Teams I've attempted textjoin, join, and ifs functions, but I can't find the right way to structure a formula to accomplish this because I'm not aware of a way to make the argument alternate the values in two rows until the end of the data. Using a Query and IF (the two key. e. Kebijakan Privasi. Search. To lookup and retrieve multiple matches in a comma separated list (in a single cell) you can use the IF function with the TEXTJOIN function. The only difference is in the use of delimiter. I have 2 columns that are a different color font to the. we want to take each pair of values and combine theme like this: ColB line break ColD. Search. 1. . Clear search Use Google Sheets to create and edit online spreadsheets. The delimiter for this function is set an empty string (“”) and the ignore_empty argument’s value is entered TRUE. In Excel 2019 and higher, you can use the TEXTJOIN function to merge strings from multiple cells with any delimiter that you specify. To split text over several lines within a cell, place your cursor at the point where you want to insert the new line. 1. CODE;. 2. both collate and separate the query results and output to a single cell). It concatenates the text values provided as arguments into one continuous text, separated by the specified delimiter. The former can include separators (delimiters) but the latter can’t. Example: 01 - ABC. TEXTJOIN (“ “, TRUE, “hello”, “world”) TEX. TEXTJOIN Syntax. 1. Steps to Join Double Quotes with Text in Google Sheets. ]) delimiter - A string, possibly empty,. Using Byrow, one of the Lambda helper functions, we can expand the result of the text functions mentioned at the beginning of this tutorial. GROSS (UPPER) WERT (VALUE) BAHTTEXT. I am building a function - an ArrayFormula that filters data from multiple sheets. Here I am selecting Textjoin, as it can omit blank cells. IF & IFS Function: most useful functions i. Google Sheet use ArrayFormula with textjoin. 1. It works by specifying the text strings you want to join, along with optional delimiters, and returns the combined result in a single cell. To do this, you need to write a regular expression that matches any numbers. It concatenates the text values. Exemplos de utilização TEXTJOIN (“ “, TRUE, “hello”, “world. Find answers and tips from other users and experts in the Google Docs community forum. Concatenate non empty cells in each row with arrayformula. Guida di Editor di documenti Google. A1&CHAR (10)&A2) Adding a line break (or new line, or carriage return) in Google Sheets isn't obvious as just pressing Enter exits the cell. Click on the Rotate Up option. =ARRAYFORMULA (REGEXEXTRACT (SPLIT (B2, " "),TEXTJOIN ("|",. =char (34)&A1&char (34) This formula will prefix and suffix double quotations marks with the text Hello. Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web More. Note: Dez's answer does not work for cell formulas. We’ve also been treated to a new feature-packed function to use. DOLLARGoogle Sheets: Conditional Formatting Based Upon Past, Today, or Future Article • 1 min read This question is in a collective: a subcommunity defined by tags with relevant content and experts. Reverse order of TEXTJOIN range of cells in a row. TEXTJOIN (“ “; TRUE; “halo”; “semua”) Daftar fungsi Google Spreadsheet. Relative references change when a formula is copied to another cell. Google sheet query - combining two sets of data. ) Select the combine type under the To combine selected cells according to following. How can I create a serial number and text in Google Sheets by dragging or another better solution (arrayformula)?=TEXTJOIN("<br>",1,FILTER(Data!A:D,A2)) Google Sheets example ready for copy/fiddle. To paste horizontal data vertically in Google Sheets, follow these steps: – Copy the horizontal data that you want to transpose. Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets. UNICHAR 함수. e. Given some sheet at cell A1 if I write ="foo bar" then foo bar will be rendered as shown:. The syntax of the function is: =TEXTJOIN (delimiter, ignore_empty, text1, [text2,. 用法示例 TEXTJOIN(“ “, TRUE, “hello”, “world”) TEXTJOIN(“, ”, FALSE, A1:A5) 语. Type the range that contains the data you want to remove duplicates from, like this: A1:A15. Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets. 125k 12 12 gold badges 70 70 silver badges 129 129 bronze badges. Functions to help deal with formula parse errors in Google Sheets. Follow edited Oct 8, 2019 at 10:26. In this article, we will discuss the TEXTJOIN function in detail and provide example cases that demonstrate how to use TEXTJOIN in your Excel sheets. Use the shortcut: Alt + Enter (Windows) or Cmd + Enter (Mac). 0. Syntax. In this case, that is the range C2:C. Reference your data on the same spreadsheet you work on. Remove Non-Numeric Characters Note: The functions mentioned in this article were introduced in Excel 2016. Note: Dez's answer does not work for cell formulas. Solution guaranteed. TEXTJOIN combines the text from the range B2:B, with a specifiable delimiter separating the different texts. 0. If so, we can rewrite the above query as;This tutorial will demonstrate you how to remove non-numeric characters from cells in Excel and Google Sheet. In this video, I show how to use the TEXTJOIN function in Google Sheets. Star. TEXTJOIN(delimiter, ignore_empty, text1, [text2,. Let’s use the below Query in the cell J8. The syntax for the TEXTJOIN formula in Google Sheets is as follows: =TEXTJOIN (delimiter, ignore_empty, text1, [text2,. Teams. This help content & information General Help Center experience. Improve this question. TEXTJOIN; 43 of 43. XLOOKUP Google Sheets – 4 Best Alternatives! Dashboards in Excel Using Pivot Tables, Pivot Charts and. 0. If Intern not found on Title (C), then should show nothing. 1. 31. 1. Saiba como trabalhar nos arquivos do Office sem instalar o Office, criar planos de projetos dinâmicos e agendas de equipe, organizar. Every cell with textjoin =TEXTJOIN("",TRUE,BE117:CT117) that is literally it, wants to send an email when quick clicking the cell. CONCAT is also the standard function used by Google Sheets. In the example shown, the formula in F5 is: {=TEXTJOIN(", ",TRUE,IF(group=E5,name,""))} This is an array formula and must be entered with control + shift + enter. Kirim masukan. TEXTJOIN(delimiter, ignore_empty, text1, [text2,. The TEXTJOIN function combines text together with a specific delimiter. There are four main ways to join text in Google Sheets: using JOIN, TEXTJOIN, CONCATENATE, and ampersand ( &) functions. Modified 1 month ago. ignorar_vazias - Um valor booleano; se for TRUE, as células vazias selecionadas nos argumentos do texto não serão incluídas no resultado. Learn to work on Office files without installing Office, create. A perhaps simpler way is to use filter (), like this: =textjoin ( ", ", true, sort ( unique ( flatten ( filter (B$2:D, A$2:A = J2) ) ) ) ) Like the accepted answer, the above formula needs to be filled down. I need a formula that joins all the cells in the row but skips the cells that are empty. This help content & information General Help Center experience. Go to the formula bar and enter the formula below: =CHAR (10148) Press Enter. I'm looking to list and count unique values from multiple cells. ) function in Google Sheets to return the desired character. Without nesting, the formula would be like this which is also an example of SQL similar IN operator use in Google Sheets. Follow edited Apr 23, 2022 at 8:50. Follow edited Nov 14, 2018 at 20:14. It refers to: =REPLACE (GET. I've tried changing my QUERY from MATCHES to = and also CONTAINS and none of those seem to work either. IMPORTHTML. 0 "Multiply" (Concatenate) Text Columns in Google Sheets. In this tutorial we'll cover how to use Google Sheets Join function, TEXTJOIN function, & operator and. This is sometimes useful for seeing your whole formula for debugging, keeping a copy of your formula so you can copy and paste bits of it elsewhere for testing. ]) delimiter: This is the character or string that will be used to separate the text values being joined. 1: The TEXTJOIN() Function. Google Sheets: Textjoin from another sheet not working. This operator simply connects the numerical value obtained by any formula with texts. You can add an ArrayFormula to existing functions. The TEXTJOIN Function Users of Excel 2019+ have access to the TEXTJOIN Function, which merges multiple strings of text together, separating each string with a. Text 2: B2. – Right-click on the cell and choose “Paste special”. Observe que não é possível exportar fórmulas de matriz. GTE; 8 of 18. . 1. If you need to combine many cells into a single cell, then using TEXTJOIN could be a huge timesaver. Clear search Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts. player0. Improve this answer. Combine two or more cells with line break in Excel and Google Sheets =CONCATENATE(A2,CHAR(10), A3) =TEXTJOIN(CHAR(10), TRUE, A2:A3) A2, A3 = cells which you want to combine. This help content & information General Help Center experience. It's simple and dynamic. 1. . The hexadecimal Unicode Control Code for a Line Feed is U+000A. For example: =C2&”_”&D2. We can TextJoin the above Regexreplace output or if you have skipped step 1 simply use A1:A. I want to values to all be listed in the same cell at the end of a given row. You end up with: {0,0,1,0; 1,0,0,0; 0,0,0,0; 0,0,0,0} Then the IF looks at all 16. TEXTJOIN lets you select both individual cells or an Array of cells. A Sheets cell cannot have more than 50,000 characters: When you convert a document from Excel to Google Sheets, any cell with more than 50,000 characters will be removed in Sheets. The TEXTJOIN() function in Google Sheets does the job similar to the JOIN() function, but with the use of the TEXTJOIN() function, we can symmetrically get the result by ignoring the empty cells in. The formula to remove numbers from the text works exactly the same in Google Sheets as in Excel: Excel Practice Worksheet. Hi, using simple textjoin for the first time in a spreadsheet that was converted from a google sheet. =textjoin(", ",true,A:A) Share. The syntax for TEXTJOIN is as follows:Google Sheets - Get unique values from a column and return the values a single column 0 How to split imported table data from web page to Headers in first row, and linked data in second rowI have a table, and list of indexes, I want google sheets return a list of matched value. But it doesn’t work when I use: "number - text" . Type an ampersand (&) Type char (10) Type an ampersand (&) Type the second formula. In this video, I show how to use the TEXTJOIN function in Google Sheets. I have a Google sheet where I have 2 sheets To Do and the Config respectively. ADOGG4155 ADOGG4155. Step 2: Press “=” and type JOIN. The syntax for the TEXTJOIN function is as follows: TEXTJOIN (delimiter, ignore. Learn to work on. You use a formula like this in column C: =MAP (A1:A,B1:B,LAMBDA (a,b,TEXTJOIN (" ",TRUE, {a,b})) The lambda will be called for each row, with the value of column A and column B for the row passed as the first two params of the lambda. ★. 1, 8. The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. It works by specifying the text strings you want to join, along with optional delimiters, and returns the combined result in a single cell. Most common you will be using either a space or a comma here. How to Fix Formula Parse Errors in Google Sheets - IFERROR Function. Sie nutzen Google-Produkte wie Google Docs bei der Arbeit oder in einer Bildungseinrichtung? Wir helfen Ihnen mit praktischen Tipps, Lernprogrammen und Vorlagen. Select the range that. google-sheets; filter; google-sheets-formula; textjoin; Share. ARRAYFORMULA with repetition. This is a true array, insofar as Array. asked Oct 8, 2019 at 9:00. This formula is especially useful when you have a large amount of data that needs to be combined into a single cell, such as a list of names or addresses. Functions to help deal with formula parse errors in Google Sheets. This help content & information General Help Center experience. After selecting the cell, type the TextJoin Function by typing “=TEXTJOIN(” Step 3 – Type the Argument of the function. 3k 4 4 gold badges 22 22 silver badges 36 36 bronze badges. 💡 Tip: You can also use the Ctrl + H keyboard shortcut to open the Find and replace command. concat (range2); } And then simply:I'm glad it was useful! "matches" allows you to use regular expressions - then with TEXTJOIN you can concatenate all the values with the corresponding delimiters implies beginning of a word and | is "or" - other way without regular expressions with be to use TEXTJOIN with the phrase OR not B = for exampleeEksempler på bruk TEXTJOIN(" "; TRUE; "hei"; "verden") TEXTJOIN(", "Gå videre til hovedinnholdet. TEXTJOIN function. Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Cú pháp. I have one Google Sheet that contains ID-values, together with corresponding Names and Attack power. Clear searchFollowing the steps you'll end up in another sheet in your spreadsheet that will have the row with your desired columns. Follow these steps to merge columns in Google Sheets: Type a formula to merge the first cells in each column (most likely, these will be cells from row 2 unless your dataset doesn’t have a header row). google-sheets-formula; sumifs; textjoin; or ask your own question. =query ( {A1:M5},"Select Col1,Col2,Col3") To make a dynamic Column reference in Google Sheets Query, you should follow the just above formula where I’ve used Column. Click the tab of the first worksheet. Again, click Remove duplicates. Check out some of my most recent discoveries about spreadsheets. g.