Duplicate data

Solved/Closed
Stormdronk - Aug 1, 2011 at 10:36 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Aug 3, 2011 at 05:43 AM
Hello, all

Please can you help???

I have duplicate data, lets say,

Ben
Ben
Paul
Paul
Paul
Ben
Alan

I want to lookup the Range and then give the result with no duplicates as below,

Ben
Paul
Alan

Is this posible, are there a formula for this?

Thanks for the help!!!!!!!!!!!



4 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 1, 2011 at 09:42 PM
Why not use Advance filter option to get unique record ?
0
Hi, yes I can, but the resuld shows up in diferant sheets and are used with other formulas. Thus I need a formula to do this if posible, Thanks for help!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 2, 2011 at 06:17 AM
I would think it will depends on how you plan to use. One of the most easy way and one size fit all way that I can think of is that the you have a hidden sheet, and use change_event to keep updating sheet to store unique records

How exactly you plan to use those unique values in formula ?
0
Hi, I have a sheet with,

Paul 10
Paul 5
Paul 10
Ben 3
Ben 5
Alan 2

The result must show,

Paul 25
Ben 8
Alan 2

I have 50 arays in 1 sheet so sorting them manualy wont work.

Hope this helps, thanks for the help!!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 2, 2011 at 08:08 AM
I am not saying that you manually update. What I am trying to say is that, there is some place where data is written or updated. When you update that area/range you would like to get a unique items from that area. Taking your example lets say name are in column A and values are in column B. What I am saying is that have a code that update the list of unique items in column A when ever a cell in Column A is changed. This can be done via change event of worksheet. That event will fire off when there is a change in column A and will but the latest set of unique values on a hidden sheets / or a visible sheet depending on your call. Then you can use that updated sheet to do what you want to do
0
Hi, this is the result I was looking for.

In cell B1 =IF(OR(ISBLANK(A1),A1="0"),"",ROW())
In cell C1=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$10),"",INDEX(A:A,SMALL(B$1:B$10,1+ROW(A1)-ROW(A$1))))

Coulm A Start and C end result

A B C
1 MARIO 1 MARIO
2 LUIGI 2 LUIGI
3 TOAD
4 TOAD 4 YOSHI
5
6 YOSHI 6

Just one problem, I have a formula in my blank cells, do you know how to change the formula to ignore the formula and see it as blank??

Thanks again for your help
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 2, 2011 at 01:31 PM
instead of ISBLANK(A1)
try
A1=""
presuming that your formula is putting ""
0
Thanks, it worked! Changed it to =IF(OR(A1=""),"",ROW())

You can mark this as solved, dont know where to.

Thanks for the help!!!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 3, 2011 at 05:43 AM
you are welcome. solve is under title of issue on top of page
0