Any excel wizards here?

Heylel_sl said:

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.

Click to expand...

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.

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.

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.

I can google the shit out of some excel.

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?

Just adding another Column works fine.
How do I do that Index/ Match ?
Thanks a lot.

ToeMissile_sl said:

=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

Click to expand...

Oh my fucking God, thank you, it worked !
I struggled a bit with the syntax and I got some funky results until I figured out the need to use absolute cell values, but I freaking works. Thank you !

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.