How to delete duplicate data with the resulting data collection in the Excel program
EXAMPLE :
AAA 111 BBB 555 AAA 111 CCC 222 TO
AAA 222 BBB 555 CCC 222 
- how is this data laid out? Is this in a string in one cell? Is that across columns? Also, what have you tried?QHarr– QHarr2018-03-18 08:18:09 +00:00Commented Mar 18, 2018 at 8:18
- See image below - select both columns when deletingQHarr– QHarr2018-03-18 08:34:11 +00:00Commented Mar 18, 2018 at 8:34
- You can use Pivot table for this. See my answer below.shrivallabha.redij– shrivallabha.redij2018-03-18 10:32:09 +00:00Commented Mar 18, 2018 at 10:32
- HI.HUST NOW I HAVE POSTED NEW QUSTION FORM WITH MORE EXPLAIN ABOUT THIS QUASTIONR3DHA– R3DHA2018-03-19 11:59:43 +00:00Commented Mar 19, 2018 at 11:59
Add a comment |
2 Answers
Excel 2007 and above
Bring your data in column, select column, and go to data > remove duplicates, that should do.
Excel 2003
Bring data in column, sort it in any order (ascending or descending). Use the formula as shown in picture. Delete all rows with zero.
In case you also want to sum the amount as well, there are two ways -
With Pivot
Create a Pivot on your data (You must have named columns for that, I have named them var1 and var2) - select the data, goto insert > pivot table.
It will automatically select range, and press OK, this will by default generate a pivot in new sheet
In new pivot fields, drag var1 to rows and var2 to values. By default (if all values are numeric), it will give you sum
Without Pivot
Create unique list of items by one of the methods above, and sum using sumif formula (as shown below) -
1 Comment
R3DHA
I ALSO WANT TO COLLECT THE AMOUNT IN THE SECOND COLUMN
What you really want is consolidation. The process which you are describing can be achieved by using Pivot Tables.
Assign suitable headers to your data if they are not there.
Select Data as you have now.
Choose >Insert>Pivot Tables.
In "Row Labels" put the column having
AAA, BBBetc.In "Values" section put the other column of which you need
SUM.