I'm comparing two large datasets containing strings in excel. Column A contains the numbers 1-1,000,000. Column B contains 1,000,000 strings, neatly organized in the desired order. Column C contains 100,000 randomly organized strings, that have identical values somewhere in column B.

Example:

A        B         C          D
1     String1   String642
2     String2   String11
3     String3   String8000
4     String4   String78

What I'd like to do is find duplicate values in columns B and C then output the Column A value that corresponds with the string in Column C into Column D.

Desired Output:

A        B         C          D
1     String1   String642    642
2     String2   String11     11
3     String3   String8000   8000
4     String4   String78     78
  • I don't think this can be done without writing a macro for it, or I'm misunderstanding the question. – LPChip Aug 21 '14 at 21:30
  • A macro solution is also fine! – overtime Aug 21 '14 at 21:31
  • We're not a scripting service site though. We can help you find errors in your script, but you should write the script yourself and tell us where you need help. To get you on the way, you'll need to place a button and use ALT-F11 to get into the VBA editor. The language is using the VBScript (or Visual Basic) language, with additional ways to get info, such as Cells(x,y) where x and y are numbers to read/write celldata, and a for loop to go through each cell. – LPChip Aug 21 '14 at 21:42
up vote 1 down vote accepted

Perhaps I’m misunderstanding the question, but this seems like it can be done with very simple function(s).  To find the row in Column B where the value in C1 appears, use

=MATCH(C1,B$1:B$1000000,0)

If cell A1 contains 1, A2 contains 2, etc, then you’re done.  But, if you need to actually retrieve the value from A642, use

=OFFSET(A$1, (the above)-1, 0)

i.e.,

=OFFSET(A$1, MATCH(C1,B$1:B$1000000,0)-1, 0)
  • Yes this works perfectly! Thank you so much. I knew there had to be a simple formula for this. – overtime Aug 22 '14 at 13:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.