- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel question: How do you compare values in two columns?
Posted on 1/26/17 at 4:22 pm
Posted on 1/26/17 at 4:22 pm
Let's say I have two Columns A and B with about 200 rows of entries. Some of the entiries could be in Column A alone, some in Column B alone and some in both.
What is the best way to come up with a list of entries that are in both columns? Is there a function that can do that?
What is the best way to come up with a list of entries that are in both columns? Is there a function that can do that?
19
Posted on 1/26/17 at 4:23 pm to euphemus
quote:
Let's say I have two Columns A and B with about 200 rows of entries. Some of the entiries could be in Column A alone, some in Column B alone and some in both.
What is the best way to come up with a list of entries that are in both columns?
Just look at them and you can see where there are doubles.
Lazy arse.
Posted on 1/26/17 at 4:25 pm to euphemus
yeah I don't have anything useful to add either.
Just wanted to reply so you would get your hopes up.
Just wanted to reply so you would get your hopes up.
Posted on 1/26/17 at 4:25 pm to spaceranger
Yeah, you can use vlookup to do it.
Eta: The lookup value in 1 column the data array starting in the other, and if it returns an error or nothing then there isn't a duplicate.
Eta: The lookup value in 1 column the data array starting in the other, and if it returns an error or nothing then there isn't a duplicate.
This post was edited on 1/26/17 at 4:27 pm
Posted on 1/26/17 at 4:26 pm to euphemus
Conditional formatting highlight duplicates? Assuming no duplicate entries in A or B themselves. That's a quick way to do it.
This post was edited on 1/26/17 at 4:28 pm
Posted on 1/26/17 at 4:28 pm to Paul Redeker
That probably would be a cleaner solution.
Posted on 1/26/17 at 4:29 pm to euphemus
You'll be the first one to kiss their shiny metal asses when the robot overlords assume power.
Robot arse kisser.
Do it manually, it's less than 200 col-umes.
Robot arse kisser.
Do it manually, it's less than 200 col-umes.
Posted on 1/26/17 at 4:29 pm to Peazey
Actually you could also just use a simple IF function. If column B=column A then duplicate. Would that work with text? I think so.
Posted on 1/26/17 at 4:31 pm to euphemus
Add a column in between so data is in A and C Label them Data 1 and Data 2
In column D put a 1 next to every data in Column C Label column D Lookup
In Column b on b2
=vlookup(a2,$c$2:$c$300,2,false)
Copy Formula all the way down column b
For every match in Column a there will be a 1
This post was edited on 1/26/17 at 4:32 pm
Posted on 1/26/17 at 4:37 pm to euphemus
A really simple way if you suck at excel would be to sort each column A to Z then check it. If it's only 200 rows it won't take long.
Posted on 1/26/17 at 4:38 pm to euphemus
vlookup wouldn't really work
What you need is probably something like MATCH or INDEX nested in an IF statement.
Some help here:
LINK
so something like:
In column C:
MATCH(A1,$B$1:$B$200,0) {copied down to every cell so A1:A200 are captured} <-- This would return the row of each duplicate in the B column ...you could nest it in an IF statement to change it to a simple true or false...or now you can use VLookup to get the list of duplicates.
What you need is probably something like MATCH or INDEX nested in an IF statement.
Some help here:
LINK
so something like:
In column C:
MATCH(A1,$B$1:$B$200,0) {copied down to every cell so A1:A200 are captured} <-- This would return the row of each duplicate in the B column ...you could nest it in an IF statement to change it to a simple true or false...or now you can use VLookup to get the list of duplicates.
This post was edited on 1/26/17 at 4:45 pm
Posted on 1/26/17 at 4:41 pm to euphemus
Add column to each one stating "in a" "in b".
Then copy the data in the second set below the first set, now you have one continuous set.
Do a pivot table where the entry is the row, and your data will tell you how many sets it is in.
Hard to explain, but the easiest way.
Then copy the data in the second set below the first set, now you have one continuous set.
Do a pivot table where the entry is the row, and your data will tell you how many sets it is in.
Hard to explain, but the easiest way.
Posted on 1/26/17 at 4:43 pm to euphemus
=iferror(if(vlookup(a1,B:B,1,false)=a1,"In both columns","Not in both columns"),"Not in both columns")
Actually, this might be better:
=iferror(vlookup(a1,B:B,1,false),"")
That would give you the list of only the values in both columns.
Actually, this might be better:
=iferror(vlookup(a1,B:B,1,false),"")
That would give you the list of only the values in both columns.
This post was edited on 1/26/17 at 4:50 pm
Posted on 1/26/17 at 4:45 pm to euphemus
In c1, =IF(a1=b1,a1," "). Pivot table on column C for unique values or paste values of C into another column then Data>remove duplicates.
Nvm, I read it as matching rows.
Nvm, I read it as matching rows.
This post was edited on 1/26/17 at 4:48 pm
Posted on 1/26/17 at 4:46 pm to GOON
Why can't you run filters?
Posted on 1/26/17 at 4:47 pm to pjab
quote:
In c1, =IF(a1=b1,a1," "). Pivot table on column C for unique values or paste values of C into another column then Data>remove duplicates.
That would only work if the values were all in the same rows.
A B
1 1
2 4
3 3
4 5
6 6
the first row would match with your formula, but row 2 wouldn't, even though "4" is in both columns.
Posted on 1/26/17 at 4:51 pm to euphemus
You get the people who work underneath you to do it.
Posted on 1/26/17 at 6:57 pm to euphemus
Copy both columns into a third column to make one long column and then remove duplicates. This will give you all of the unique values.
Page 1 of 2
Popular
Back to top
Follow TigerDroppings for LSU Football News