Forums >Off the Beaten Path>microsoft excel question
"run" "2" "eat"
what is the best way to find the shared members between two sets, using excel?
i have a list of 323 items with columns -- item number, product name, product creator, price. i have another list of 800+ items which can be formatted to include the same columns but the data beyond item number may not match because the lists are from different sources.
what i want to know -- do any of the 323 appear on the 800+ list?
i find the sunshine beckons me to open up the gate and dream and dream ~~robbie williams
vlookup?
"Famous last words" ~Bhearn
Probably your best bet. Which data would you expect to be in both data sets (item number)?
And you can quote me as saying I was mis-quoted. Groucho Marx
Rob
i can't figure out how that works. it looks like i would still have to feed 323 individual numbers in. i can't tell how it is any different than ctrl+f.
what am i missing about vlookup?
yes, item number is the same in both.
okay, actually, it's part of item number. one list has ISBN13 and one has ISBN10, but there is a unique string in each to match on.
yes, item number is the same in both. okay, actually, it's part of item number. one list has ISBN13 and one has ISBN10, but there is a unique string in each to match on.
As long as they are exact matches -- vlookup should work. However, if they don't match exactly, you could have trouble and have to do some excel judo to make things work...
okay.
how the hell does vlookup work???????????????
i have googled it and read stuff, but it is not penetrating my brain.
okay, i copied my 323 item numbers to a new sheet, column A. copied my 830 item numbers to the same sheet column J.
put this into D1 --
=VLOOKUP(A1,J:J,1,FALSE)
and copied it down the D column. now the D column is showing me a lot of item numbers and some N/A. if i am interpreting this correctly, it means that if i am seeing an item number, then there is a match, and if not, no match.
right?
Right. And now if you sort the calculated column you are left with a group that is shared, and a group that is not.
The process is the goal.
Men heap together the mistakes of their lives, and create a monster they call Destiny.
"what i want to know -- do any of the 323 appear on the 800+ list? "
I think you want to put the formula in column K, and refer to the data in columns A-D.... Within Colunn D, for fun, place "YES" within each row of observations #1 - #323.
so...
vlookup(j1,a1:d800,4,false)
where A1 could equal J1
where column D (4th column) contains information that you want to show within column K.
So, Column K would show "YES" every time that any of the 323 appear on the 800 list.
Life Goals:
#1: Do what I can do
#2: Enjoy life
okay. this seems to be working in the scenario where i have copied the columns into the new sheet.
what if i want to do this in the original sheets?
i have added a column to the 323 sheet, to hold the vlookup. i copied the working formula from the setup sheet to the real sheet and replaced the range with the range from the 830 sheet -- and got an entire column of N/A.
i switched it back -- left vlookup in the 323 sheet, but pointed it at the fake sheet where i only have the item number colums. boom. works. point it at the real sheet, doesn't work.
i don't mind copying the two columns to a set up sheet, but should it work in the real environment?
oops -- wait, wait -- in the setup sheet i changed the numbers from "number stored as text" to real actual numbers. that is why it worked in the setup sheet.
the 830 sheet has numbers stored as text.
without changing the content of the 830 sheet, how do i get this to be a number? then i could point at the original sheet.
'[status list 012612 (2).xls]Sheet 1'!$B$6:$B$835
while you're at it.... what about this?
=IF(VLOOKUP(B2,[Book1]Sheet1!$J:$J,1,FALSE), yes, no)
when i try this, i get an error. how can i use vlookup and the IF function to get a yes or no to show in the column if there is a match?
looks like i am talking to myself again.
i got this far --
=IF(ISNA(VLOOKUP(B3,[Book1]Sheet1!$J:$J,1,FALSE)) = TRUE, "no", "yes")
now alls i need it to make the original sheet values into numbers. but, i don't think i am going to bother with that -- i will simply copy the column to the far side of the sheet of 323 and point at ti there.
thanks, y'all!