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.

I need to produce a method of moving the B and C columns to match up with A so:

enter image description here

with a macro or formula it should produce:

enter image description here

Column 1(A) should be unchanged but 2(B) and 3(C) moved; 2 and 3 will also need to be linked.

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

Adjust the range represented by B1:C11 in your top screen capture to reflect the range of B and C columns. Move the B & C column content to another location such as G1:H11

Then in B1

=IF(ISNA(VLOOKUP(A1,$G$1:$H$11,1,FALSE))=TRUE,"",VLOOKUP(A1,$G$1:$H$11,1,FALSE))

and in C1

=IF(ISNA(VLOOKUP(A1,$G$1:$H$11,2,FALSE))=TRUE,"",VLOOKUP(A1,$G$1:$H$11,2,FALSE))

you can always insert name define the range $G$1:$H$11 (or whatever it is) and use the namedRange in the formulas instead which would look like

=IF(ISNA(VLOOKUP(A1,namedRange,1,FALSE))=TRUE,"",VLOOKUP(A1,namedRange,1,FALSE))

and

 =IF(ISNA(VLOOKUP(A1,namedRange,2,FALSE))=TRUE,"",VLOOKUP(A1,namedRange,2,FALSE))

of course drag the formulas in B1 and C1 down adjacent to column A.

share|improve this answer
    
Brillant! Thank you very much, you saved my keyboard from a window excursion. –  Chris Stein Jun 9 '11 at 7:39
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.