I'm not sure about comparing different rows, but I use VLOOKUP to compare different columns (same rows) for duplicates.
I've got a sheet I need to analyze for duplicates and I'm having difficulty tackling it. Been too many years since I had to work with excel formulas.
Basically I have two distinct lists of values split into two columns each. I need to compare each list based on duplication of BOTH values. So if Row A3 and B3 have the same paired values as rows C47 and D47, I need to highlight them or otherwise dig that out.
Anyone? Halps.
If the porn isn't high res you can't get horny, but you can hate a guy at 1000 paces without a scope. That's human nature.
I'm not sure about comparing different rows, but I use VLOOKUP to compare different columns (same rows) for duplicates.
Is it numerical values? If so, you could just do a 3rd column using formula SUM=(A1-B1) and anything that is a 0 would be a duplicate.
So you have 4 columns of data with column A paired to B, and C paired to D. Does data in column A only need to be checked against column C? (same question for B compared against D as well)
If yes, you could concatenate AB and CD together each into new columns E and F. Add another column G adjacent to them that is just filled with numbers 1 to row count of F (this will be used to return a row if a dupe is found). Then add a new column H and put in this formula in the first cell:
=VLOOKUP(E1,$F$1:$G$99,FALSE)
Drag this formula down using the lower right hand of the cell grip that looks like a cross (I forget the stupid name of this simple technique) until you get to the bottom of the E column. Also the second argument in above I just used an arbitrary value for the row count for column F, you'll need to look at your data and change it from 99 to whatever the rowcount is. The $ signs are just use to make the range lookup against the F and G columns into an absolute reference so they won't change as you drag formula down (only the first argument E1 should increment up E2, E3, etc...).
If the formula returns a number then it found a duplicate in the F column and that number is the row it appears on (supplied by the G column). If it just prints #N/A then it found nothing.
Read the documentation for VLOOKUP if you have problems as this is just a simple solution I could think up off the top of my head.
What you're trying to do is definitely possible, and there's a few different ways to go about it. VLOOKUP should still work for checking multiple columns on different rows. The trick would be combining like soygen said. Even if they aren't numerical values, you should be able to combine them as strings. You could also run a couple different vlookups to check for duplicates from Column A against Columns B-Z, Column B against A, C-Z, etc.
You didn't even really describe the problem very well unfortunately.
Is A paired to B or A to C?
If it's A -> C, B -> D then the more 'GUI' way to accomplish this would be to concatenate A + B and C + D and then copy/paste C + D under A + B and use conditional formatting, find duplicates.
I second all the other questions, we need an example. Though I did go as far as creating an interpretation/solution this morning at work. Will post later.
If using conditional formatting just highlight your range and use a simple formula for the condition. Seelect your range, conditional formatting > Use a formula >
=(A1&b2)=(c1&d1)
This should highlight the A&B cells down the applied range.
Can also get more complicated and use VBA to delete the dupes, but that's probably the easiest way to FIND them. You can also then sort by formatting so all your highlighted rows move up to the top for easy deletion.
if you still need this pm me the file
I have one problem too, It might be simple, but I am stupid and I dont know how to do it. And it's something that I encounter at work very often and it is driving me crazy. Here it is.
I have one xls file.
Column A has numbers, no decimals. The numbers correspond to some movie character names, and Colum B has some text, the lines of those characters in the movie.
A B
2 Text Text Text
4 Text Text Text
8 Text Text Text
12 Text Text Text
8 Text Text Text
Column C has all the numbers that appear in column A listed once, and column D has a text that is the names of those characters
C D
1 Gandalf
2 Legolas
3 Samwise
4 Gimly
5 Aragon
Now i Need something like IF A1 = C1 Then A1 = D1
But i dont know how to make it recurrent, how Can i Use a formula or something to put the names from the D column in their proper places on the A collumn.
Thanks in advance.
Do the values need to change in Column A? Or would you be open to just adding another Column and doing an Index/Match?
Ended up concatenating and then sorting off that. It wasn't a perfect fix, but it gave me what I needed.
Next up, fighting with Tableau.
If the porn isn't high res you can't get horny, but you can hate a guy at 1000 paces without a scope. That's human nature.
Just adding another Column works fine.
How do I do that Index/ Match ?
Thanks a lot.
=index(range containing desired data, match(value to match, range to find value to match, 0))
Excel INDEX / MATCH function instead of VLOOKUP – formula examples
yah, index/match is pretty great, and even better when your data is in tables.
CP052: Book Review - M is for Data Monkey by Ken & Miguel | Chandoo.org - Learn Microsoft Excel Online is a great site for all things excel
wamphyr one thing I'd like to reiterate based on the article that ToeMissle originally linked: Make sure that after you've entered your Index/Match formula, don't just press enter. Make sure you are pressing Ctrl + Shift + Enter to create an array formula so it checks each cell in the array. TLDR: It's important.
Index/Match is God. I use it all the time.
There are currently 1 users browsing this thread. (0 members and 1 guests)