How to delete pairs
 

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.

How to delete pairs
 

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'....

"Salman" wrote:

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.

How to delete pairs
 

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.

"Salman" wrote:

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.

How to delete pairs
 

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

"Salman" wrote:

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.

"Salman" wrote:

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.

How to delete pairs
 

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
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


"Salman" wrote:

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.

How to delete pairs
 

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.

"Salman" wrote:

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
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


"Salman" wrote:

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.

How to delete pairs
 

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).

"Sheeloo" wrote:

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.

"Salman" wrote:

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
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


"Salman" wrote:

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.

How to delete pairs
 

How to delete pairs
 

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

"Sheeloo" wrote:

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.

"Salman" wrote:

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
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


"Salman" wrote:

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.

How to delete pairs
 

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.

"Salman" wrote:

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

"Sheeloo" wrote:

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.

"Salman" wrote:

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
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


"Salman" wrote:

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.