Question regarding optimal excel function implementation

Question

Hi everyone, I have a question about Excel! I hope that isn't too unconventional for this site...

So I have an Excel table with several thousand rows. It is kind of setup like a db in that the first three of my four columns have numerical values identifying the sequence or order that the content or fourth row contains.

I am running into some possible duplication issues, and I am remembering back to my college days something about there being a function for the type of test I need to do. I need to verify that there are no two rows that have the same values for column 1-3. There should never be a time where all three columns' values match exactly that of another row.

Is VLookUp the function I need? Any excel experts out there that know of a function I could look into? Thanks so much!

Answer 1

You could create another column that concatenates the first 3, then do a countif on that. Let's say the concatenation column is D and your data begins in the second row:

=countif(D:D,D2)

Copy the formula down, then filter on >1.

Answer 2

the quick one-off solution I employ for this kind of quest is the following

1. create a single key in one temporary column - say F "=A2 & B2 & C2 ..." if combined key - I copy this formula all the way down
2. create a group counter for that single key - say G "=IF(F2=F1,G1+1,1)" - I can safely include the header row here because it will move the formula into the false part
3. This formula in G numerates all identical keys from 1 to N and starts by 1 for a new key - I copy this formula all the way down
4. Important: convert G formulae into values (copy / paste special onto itself)
5. sort descending by G and delete/manipulate all rows where counter <> 1 - or use autofilter
6. later on I delete F & G columns

this may sound a bit complicated, but especially in large tables VLOOKUP, COUNTIF's etc can be very time consuming.

Hope that helps