hi!,
this is what my data looks like,

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

now i need to display all unique values of column a but that should include all repeated values of column b.

so for example, if column a has 1 it should display both 'a' and 'b'. now i know i can take unique values but that doesn't really work because for 1 the corresponding value that is displayed is only 'a'.

Please help !

On Thursday, June 4, 2015 at 3:23:29 PM UTC+5:30, Nitya Satheesh wrote:
hi!,
this is what my data looks like,

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

now i need to display all unique values of column a but that should include all repeated values of column b.

so for example, if column a has 1 it should display both 'a' and 'b'. now i know i can take unique values but that doesn't really work because for 1 the corresponding value that is displayed is only 'a'.

Please help !

So my new data should look somewhat like this
1 a
1 b
3 b
3 c
4 b
5 d
6 e
7 d

Hi Nitya,

Am Thu, 4 Jun 2015 02:53:27 -0700 (PDT) schrieb Nitya Satheesh:

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

what should the output look like?
1 a
1 b
3 b
3 c
4 b
5 d
6 e
7 d

or:
1 a, b
3 b, c
4 b
5 d
6 e
7 d

Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "UniqueValues"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Thursday, June 4, 2015 at 3:23:29 PM UTC+5:30, Nitya Satheesh wrote:
hi!,
this is what my data looks like,

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

now i need to display all unique values of column a but that should include all repeated values of column b.

so for example, if column a has 1 it should display both 'a' and 'b'. now i know i can take unique values but that doesn't really work because for 1 the corresponding value that is displayed is only 'a'.

Please help !

So my data should look like this

1 a
1 b
3 b
3 c
4 b
5 d
6 e
7 d

Please help !
Thanks in advance!

Hi,

try this:

Highlight both columns and use the "Remove Duplicates" button in the "Data" ribbon. Select both columns, and click "ok"

hope that helps,

Cameron

On Thursday, June 4, 2015 at 3:47:51 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Thu, 4 Jun 2015 02:53:27 -0700 (PDT) schrieb Nitya Satheesh:

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

what should the output look like?
1 a
1 b
3 b
3 c
4 b
5 d
6 e
7 d

or:
1 a, b
3 b, c
4 b
5 d
6 e
7 d

Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "UniqueValues"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

The output should look like the first one.
Thanks for the link.

On Thursday, June 4, 2015 at 8:53:59 PM UTC+5:30, Cameron Mitchell wrote:
Hi,

try this:

Highlight both columns and use the "Remove Duplicates" button in the "Data" ribbon. Select both columns, and click "ok"

hope that helps,

Cameron

Hi Cameron,

I tried that and got"no duplicates found". I don't know why that happened.

On Thursday, June 4, 2015 at 3:47:51 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Thu, 4 Jun 2015 02:53:27 -0700 (PDT) schrieb Nitya Satheesh:

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

what should the output look like?
1 a
1 b
3 b
3 c
4 b
5 d
6 e
7 d

or:
1 a, b
3 b, c
4 b
5 d
6 e
7 d

Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "UniqueValues"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks a lot Claus! That worked!!!

On Thursday, June 4, 2015 at 8:53:59 PM UTC+5:30, Cameron Mitchell wrote:
Hi,

try this:

Highlight both columns and use the "Remove Duplicates" button in the "Data" ribbon. Select both columns, and click "ok"

hope that helps,

Cameron

Hi !
Sorry about the first reply, I tried that again and it worked, Thanks a lot !!!

On Friday, June 5, 2015 at 11:10:16 AM UTC+5:30, Nitya Satheesh wrote:
On Thursday, June 4, 2015 at 8:53:59 PM UTC+5:30, Cameron Mitchell wrote:
Hi,

try this:

Highlight both columns and use the "Remove Duplicates" button in the "Data" ribbon. Select both columns, and click "ok"

hope that helps,

Cameron

Hi Cameron,

I tried that and got"no duplicates found". I don't know why that happened.

Hi Cameron!
I tried that again and it worked! Thanks a lot !

On Thursday, June 4, 2015 at 3:23:29 PM UTC+5:30, Nitya Satheesh wrote:
hi!,
this is what my data looks like,

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

now i need to display all unique values of column a but that should include all repeated values of column b.

so for example, if column a has 1 it should display both 'a' and 'b'. now i know i can take unique values but that doesn't really work because for 1 the corresponding value that is displayed is only 'a'.

Please help !

Hi !

I have one more column of data that I need to add.

so my original data looked like this :

A B
1 a
1 b
1 b
3 b
3 b
3 c
4 b
4 b
5 d
6 e
7 d

And thanks to you guys I could get rid of the duplicates. Now I have an additional dates column so it looks somewhat like this

A B C
1 a 1/5/2012
1 a 1/5/2012
1 a 2/5/2012
1 a 3/5/2012
1 b 2/4/2012
1 b 2/4/2012
1 b 3/4/2012
2 a 3/2/2012
2 a 3/2/2012
2 a 4/2/2012
2 c 5/2/2012
2 c 6/4/2012
3 c 7/4/2012
3 c 8/4/2012
3 c 9/4/2012
3 c 10/4/2012
4 d 3/3/2012
4 d 3/3/2012
4 d 4/3/2012
so for each set of data I need the first date For e.g :
A B C
1 a 1/5/2012
1 b 2/4/2012
2 a 3/2/2012

and so on... I tried a vlookup but it didn't work and neither did a pivot table.
Please help!

!

Hi Nitya,

Am Thu, 4 Jun 2015 23:20:18 -0700 (PDT) schrieb Nitya Satheesh:

A B C
1 a 1/5/2012
1 a 1/5/2012
1 a 2/5/2012
1 a 3/5/2012
1 b 2/4/2012
1 b 2/4/2012
1 b 3/4/2012
2 a 3/2/2012
2 a 3/2/2012
2 a 4/2/2012
2 c 5/2/2012
2 c 6/4/2012
3 c 7/4/2012
3 c 8/4/2012
3 c 9/4/2012
3 c 10/4/2012
4 d 3/3/2012
4 d 3/3/2012
4 d 4/3/2012
so for each set of data I need the first date For e.g :
A B C
1 a 1/5/2012
1 b 2/4/2012
2 a 3/2/2012

select one cell of your table = Data = Delete Duplicates and look in
A, B and C.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Here is a cell formula approach
A names the range of data in A (numerics), and likewise B (assuming strings not containing the | character of lengths up to 200 in this example)
Array Enter (CtrlShiftEnter) the following formula in C2 (or any available cell not starting in row 1)
=IFERROR(INDEX(A&"|"&B,MATCH(,COUNTIF(O$1:O1,""&IF (A&"|"&B=0,"",A&"|"&B)),)),"")
and fill down
This will produce
={"1|a";"1|b";"3|b";"3|c";"4|b";"5|d";"6|e";"7|d "}
Then to split into two columns D and E
D2=VALUE(TRIM(LEFT(SUBSTITUTE($C2,"|",REPT(" ",200)),200)))
E2=TRIM(RIGHT(SUBSTITUTE(K2,"|",REPT(" ",200)),200))
Fill D2:E2 Down
This produces
={1,"a";1,"b";3,"b";3,"c";4,"b";5,"d";6,"e";7,"d"}
Having these formulas will allow automatic duplicate removal whenever new data is entered in range A and range B.
Using the RibbonDataData ToolsRemove Duplicates is a manual process that must be repeated with new data. However, to another user of the workbook (or even yourself months later), that the Remove Duplicates functionality has been used, and is to be used with new data, is not apparent without substantial documentation.
Regards
Brian

To deal with the dates, use my formula method and then use the A|B column to lookup the dates.
Suppose you have, in A2:C20
{1,"a",40913,"1|a";1,"a",40913,"1|b";1,"a",40944," 2|a";1,"a",40973,"2|c";1,"b",40943,"3|c";1,"b",409 43,"4|d";1,"b",40972,"";2,"a",40970,"";2,"a",40970 ,"";2,"a",41001,"";2,"c",41031,"";2,"c",41064,"";3 ,"c",41094,"";3,"c",41125,"";3,"c",41156,"";3,"c", 41186,"";4,"d",40971,"";4,"d",40971,"";4,"d",41002 ,""}
Then naming the result of my first formula C_
in E2 array enter and fill down
=IFERROR(INDEX(C_,MATCH(D2,A&"|"&B,)),"")
This produces the required dates
={40913;40943;40970;41031;41094;40971}
Let me know if you would like a workbook showing the example. The above may look dauting for you to interpret and construct in your workbook.
Regards
Brian

Should read name the Dates _C
s/a dauting
s/b daunting
Regards
Brian

On Tuesday, June 16, 2015 at 10:19:05 AM UTC+5:30, Brian Canes wrote:
Here is a cell formula approach
A names the range of data in A (numerics), and likewise B (assuming
strings not containing the | character of lengths up to 200 in this
example)
Array Enter (CtrlShiftEnter) the following formula in C2 (or any
available cell not starting in row 1)
=IFERROR(INDEX(A&"|"&B,MATCH(,COUNTIF(O$1:O1,""&IF (A&"|"&B=0,"",A&"|"&B)),)),"")
and fill down
This will produce
={"1|a";"1|b";"3|b";"3|c";"4|b";"5|d";"6|e";"7|d "}
Then to split into two columns D and E
D2=VALUE(TRIM(LEFT(SUBSTITUTE($C2,"|",REPT(" ",200)),200)))
E2=TRIM(RIGHT(SUBSTITUTE(K2,"|",REPT(" ",200)),200))
Fill D2:E2 Down
This produces
={1,"a";1,"b";3,"b";3,"c";4,"b";5,"d";6,"e";7,"d"}
Having these formulas will allow automatic duplicate removal whenever
new data is entered in range A and range B.
Using the RibbonDataData ToolsRemove Duplicates is a manual process
that must be repeated with new data. However, to another user of the
workbook (or even yourself months later), that the Remove Duplicates
functionality has been used, and is to be used with new data, is not
apparent without substantial documentation.
Regards
Brian




--
Brian Canes

Thanks Brian! This is great for future use of the worksheet!