How to delete pairs

  • Thread starter Thread starter Salman
  • Start date Start date
S

Salman

Please help. If I have few numbers in a column and I want to delete pairs eg
following should only show 5+4 = 9 (ie delete/cancel off what makes a pair):
5
5
5
5
5
4
4
4

Thanks in advance.
 
If your numbers are in Col A then enter this in B1 and copy down
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

This will leave only one instance of each number... Copy and past special as
values and then filter out the blank rows...

You can also use Data->Filter->Advanced Filter and choose 'Unique Values
only'....
 
Thanks for the reply. Is there a way to delete all the numbers forming a
pair. ie from the list below I can only pick 4 + 7 = 11 (and ignore/delete
pair of 5, 3, 8 & 1)


5
5
3
3
4
8
8
1
1
7

Thanks.
 
Use this in B1 and copy down after adjusting 10 for your range...
=IF(COUNTIF($A$1:$A$20,A1)=1,A1,"")

You can then sum col B
 
Thank you so very much. I am actually trying to convert detailed balance data
into summarised version and your help has been very useful. To advise on
another issue I just noticed – could you also please help re:
Each item has got many values but I just need to pick the last value for
each item eg:
From the list below; I only need to sum/calculate 4 for Plant, 8 for
Furniture & 3 for Motor Vehicle (ie last value for each item one by one)

ITEM VALUE

Plant 5
Plant 5
Plant 4
Plant 4

Furniture 8
Furniture 8

Motor Vehicle 3
Motor Vehicle 3
Motor Vehicle 3
Motor Vehicle 3
Motor Vehicle 3

Your help would be highly appreciated.
Thanks
 
Use this in B1 and copy down after adjusting 10 for your range...
=IF(COUNTIF(A1:$A$20,A1)=1,A1,"")

This will give you the last value in Col B... all other cells will be
blank/empty.
 
Hi, Thank you very much for the help but unfortunately it didnt work. Could
you please let me have your email address so i can attach the spreadsheet
(after adding the formula you kindly advised).
 
(e-mail address removed)

remove X

Salman said:
Hi, Thank you very much for the help but unfortunately it didnt work. Could
you please let me have your email address so i can attach the spreadsheet
(after adding the formula you kindly advised).
Click to expand...
 
Please see below after formula:
ITEM Value Value with Formula

Plant 5
Plant 5 5
Plant 4
Plant 4 4
Furniture 8
Furniture 8 8
Motor vehicle 3
Motor vehicle 3
Motor vehicle 3
Motor vehicle 3
Motor vehicle 3 3

I am converting detailed version to a summary report and on the summary
report I merely need the last line for each of the relevant item(s) – ie
Plant should only show 4 (ie the last value for ‘Plant’)
eg if I will do sumif for Plant (after using above formula), I will get
5+4=9 while I just need the last number for ‘Plant’ ie 4.

Thanks
 
In that case enter this in B1
=MID(A1,1,LEN(A1)-2)
and use the following formula in C1
=IF(COUNTIF(B1:$B$20,B1)=1,B1,"")
assuming you don't have something like Plant 10... Mid function removes the
last two characters...

You may send the file to me.
 
Back
Top