Office ID First Name LAST NAME POSITION COUNTRY Income Product 120102 Tom Zhou Sales Manager AU 14,971.00 PAPER 120102 Tom Zhou Sales Rep. II UK 14,971.00 PAPER 120102 Tom Zhou Sales Manager AU 14,971.00 TISSUE 120122 Kimiko Ngan Sales Rep. II AU (523.00) TISSUE 120123 Kimiko Hotstone Sales Rep. I AU 3,193.00 TISSUE 120124 Lucian Daymond Sales Rep. I UK 1,228.00 PEN 120124 Lucian Daymond Sales Rep. IV UK 1,228.00 PEN 120126 Satyakam Denny Sales Rep. II US 11,951.00 PEN 120127 Sharryn Clarkson Sales Rep. II AU 8,404.00 PAPER As shown in the table above, i would like to remove the row where the first name, last name & Income field appear the same info more than one times. How can i create a query and ask for "distinct" value as some of the fields consist of identical or distinct values.? For instance, after i sorted the data, there're three rows of Office IDs (120102) with the same Name & Income, but different values for Product, Country and Position fields. Thus, I would like to remove the rest of two rows although the values are different in Product, Country and Position fields. P/s Product, Country and Position fields should be remained in the table. I have tried the solution below advised by you, but this will remove all the duplicate values included the original value after I filter the summarized data " Step 1 ====== Build a new query on your dataset. Drag Employee_ID into the "Select Data" box, and then drag it in again. You'll have two columns, probably "Employee_ID" and "Employee_ID1". On the second one, in the "Summary" column, select "Count". This will cause EG to create a variable which is the count of that value of Employee_ID. It turns it into a "Computed column". In the "Filter Data" tab, drag this variable into the "Filter the summarized data" area, and set the filter to > 1. Run the query. You should get a list of all of the Employee_ID values that appear more than once in your input file. Step 2 ====== This depends on what you need to do next. The general principle is to join this new table back to your original table, and get some kind of information from the join. Here's a simple example: Build a new query on your original dataset. Drag all of the columns into the "Select Data" tab. Click the "Add Tables" button, and select the table you created in Step 1. Drag the "COUNT" field into Select Data. Click the "Join Tables" button, and in the join window make sure the tables are joined on Employee_ID. Double click the "Join" picture, and change it to a Left Join. Close the Join window. Run the query. You should see the count variable added to the results, it will have the count for cases where there are more than one value for the ID, otherwise it will be missing."
... View more
