Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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 !
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default column comparison

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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!




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default column comparison

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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!!!
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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 !!!
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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 !


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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!

!
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default column comparison

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
  #13   Report Post  
Junior Member
 
Posts: 4
Default

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
  #14   Report Post  
Junior Member
 
Posts: 4
Default

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
  #15   Report Post  
Junior Member
 
Posts: 4
Default

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


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default column comparison

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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Column comparison Dave T New Users to Excel 4 May 24th 09 06:06 PM
Date Comparison in the same column Lizz45ie Excel Worksheet Functions 6 December 17th 08 02:11 AM
Column Comparison and Like Rows Tom Moffatt[_2_] Excel Discussion (Misc queries) 1 June 20th 08 02:52 PM
Column Comparison Trouble RH Excel Worksheet Functions 2 November 1st 07 02:55 AM
Excel Chart - 2 column stack /w 1 comparison column wclairmont Charts and Charting in Excel 1 December 1st 06 02:54 AM


All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"