Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What is an Excel formula to search a column for a match, and then beginning at that row, find a another match within the same column. For example, imagine a column containing 1000's of letters.

A B C B D ...

I want to find the index, ideally absolute, of B that follows C.

share|improve this question
1  
If letters are in, say, cells A1 to A1000, then use formula =MATCH("C",OFFSET(A1:A1000,MATCH("C",A1:A1000,0),0),0)+MATCH("C",A1:A1000,0). From AllExperts.Com. –  chuff Jan 19 '13 at 7:00
    
=MATCH("B",OFFSET(A1:A1000000,MATCH("C",A:A,0),0),0)+MATCH("C",A:A,0) ? If so, please repost as an answer so you can receive well deserved credit. Is there a way to specify bottom instead of A1000000? –  BSalita Jan 19 '13 at 9:53
add comment

3 Answers 3

up vote 0 down vote accepted

chuff's answer works for me and might well be the best approach but you could also use this "array" version that includes the whole column

=MATCH("B",IF(ROW(A:A)>MATCH("C",A:A,0),A:A),0)

confirmed with CTRL+SHIFT+ENTER

works in Excel 2007 or later

share|improve this answer
    
Wouldn't this run into trouble if there were no "C" in the column? –  chuff Jan 19 '13 at 14:12
    
@chuff I think Bsalita wants to find the first "B" that comes after a "C" - don't know what should happen if there's no "C" - my version will give #N/A if there's no "C"....or no "B" after that "C" –  barry houdini Jan 19 '13 at 15:08
    
The spreadsheet is computer generated and guaranteed to have C and guaranteed to have B following C. –  BSalita Jan 19 '13 at 19:48
    
Will the array formula work in LibreOffice? The spreadsheet needs to be cross-platform. –  BSalita Jan 19 '13 at 19:49
    
I don't usually use LibreOffice but when I tried the formula works but not with the whole column so it would be back to =MATCH("B",IF(ROW(A1:A1000)>MATCH("C",A1:A1000,0),A1:A1000),0) or similar –  barry houdini Jan 19 '13 at 21:35
show 1 more comment

If the column of letters is in, say, cells A1 to A1000, then to find "B" use formula:

=MATCH("B",OFFSET(A1:A1000,MATCH("B",A1:A1000,0),0),0)+MATCH("B",A1:A1000,0)

Will get back to you on how to change to remove hard reference to 1,000th row.

share|improve this answer
add comment

Select the column which has the entries that shouldn't be identified twice. Then under Data tab click on the filter icon under Advanced. Select unique entries only. Any entries entered more than once will not be shown!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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