Excel expert advice needed.?

Question

I'll try to explain the question... I want to have two rows of letters... say A1:A5 and B1:B5 as follows:
ABECD
QWCFS

Elsewhere will be a set of letters from B1-5 in cells (1 per cell) like QCFFQS.... I need to have a mirror of those cells that reflect the values in A, but actively change if you slide the values in A1-5.
so QCFFQS would return AECCAD but if I slide the values in A left or right would return the new values... (for 1 position left slide would return BCDDB(and whatever happened to be to the right of that...).. anyone know an easy way to do something like this??

Answer 1

Assuming your data is in columns a, b and c, you could put this formula into column d:

=CONCATENATE(
MID(A1,FIND(MID(C1,1,1),B1,1), 1 ),
MID(A1,FIND(MID(C1,2,1),B1,1), 1 ),
MID(A1,FIND(MID(C1,3,1),B1,1), 1 ),
MID(A1,FIND(MID(C1,4,1),B1,1), 1 ),
MID(A1,FIND(MID(C1,5,1),B1,1), 1 ),
MID(A1,FIND(MID(C1,6,1),B1,1), 1) )

Note that if the data in column c (QCFFQS in your example) is not exactly 6 characters, this won't work correctly. Also note that if column C contains letters that are not in column B or column B contains letters not in column A, you'll get #VALUE errors.

Finally, if you need more than 6 characters, I would suggest turning this into a macro where you can add iterative structures and other logic items.

Answer 2

if you need reflection of any cell in any where in the sheet use cell reference like this

you need reflection of A1 in C1 type +A1 in C1
you need reflection of A2 in C2 type +A2 in C2

if this is not the answer elaborate more your question ?