Is there a way to "flip" when pasting? (copy ABC and paste as CBA)

  • Thread starter Thread starter shadestreet
  • Start date Start date
S

shadestreet

I have an array that I need to reverse. What is the easiest way to d
this?

(A1 = X, B1 = Y, C1 = Z and I want to have A2 = Z, B2 = Y, C2 = Z)?

Was hoping there was a paste special command, similar to the transpos
function, but what is another method
 
Bump?
Click to expand...

Dump!

No, there's no @#$%&* way to flip when pasting. You'll need to use INDEX or
OFFSET formulas to handle flipping, then copy and paste special as values.
 
Well obviously there is no way to do this by pasting, that is why
asked for another method....

Never used those functions, I will give it a shot
 
Hi!

Hang on a moment. If the values in the cells concerned are numbers
there's matrix multiplication (MMULT).

If you have 3 values (your A,B,C) then multiplying by this matrix:

0...0...1
0...1...0
1...0...0

will reverse the values leaving C,B,A in consecutive cells.

If you are not dealing with numbers, forget it!
If you are and need to tell us more and ask us more: OK.
But I wouldn't know how it would respond to 1000 x 1000 matrices...

Al
 
The values are in fact numbers, but it is not a Square matrice (don'
know if that matters).

The numbers are in an array that is 7 rows by 64 columns and anothe
array that is 22 rows by 64 columns.
 
Hi!

No: the input matrix doesn't have to be square. Only the transformatio
matrix (the one with all the 0's and 1's has to be :with 1's across th
top-right to bottom left diagonal).

So in principle it is possible. As to the practice: I've no idea, 'co
I've never been there. But it might be interesting to try. email
sample if you wish: alfreddearnley - at - btinternet -d o t - com.

Al
 
By an amazing coincidence, the current tip of the hour explains exactl
what you want to do

http://www.exceltip.com/st/TipOfTheHour/313.html

The tip is below if the link doesn't work

***********************************
Create and Add a Function for Reversed Text Characters in Microsof
Excel
--------------------------------------------------------------------------------

Click Alt+F11 to open Visual Basic Editor (VBE).
From the Insert menu, select Module (to add a module).
Enter the following lines of code into the module:

Function ReverseText(text) As String

Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
ReverseText = ReverseText & Mid(text, i, 1)
Next i

End Function

Testing the function:
To test the function, open Paste Function (Shift+F3).
From the User Defined category, select the ReverseText function.
In the function box, select any cell that contains text.
Click OK.
I copied this down column B and all the text in column A flipped. Jus
like you want

Joh
 
Awesome....someone upstairs must like me.

2nd time this week the tip of the hour coincides with my question
though last time it was timed perfectly, just as I was about to post
saw the question
 
By an amazing coincidence, the current tip of the hour explains exactly
what you want to do ...
***********************************
Create and Add a Function for Reversed Text Characters in Microsoft
Excel ...
Function ReverseText(text) As String

Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
ReverseText = ReverseText & Mid(text, i, 1)
Next i

End Function
Click to expand...
...

Pathetic doing this by concatenation, so Len(text) concatenation ops. An
alternative.


Function rs(s As String) As String
Dim k As Long, n As Long

k = 1
n = Len(s)
rs = s

Do While k < n

Mid(rs, k, 1) = Mid(s, n, 1)
Mid(rs, n, 1) = Mid(s, k, 1)
k = k + 1
n = n - 1

Loop

End Function


AND THIS IS ONLY NEEDED IN EXCEL 5/7/8! In more recent versions, VBA includes a
StrReverse function, so the intelligent udf for those versions would be


Function strrev(s As String) As String
strrev = StrReverse(s)
End Function
 
The values are in fact numbers, but it is not a Square matrice (don't
know if that matters).

The numbers are in an array that is 7 rows by 64 columns and another
array that is 22 rows by 64 columns.
Click to expand...

Flipping horizontally, so

1 2 3
4 5 6

becomes

3 2 1
6 5 4

may be done as follows. Select a blank range of the same size and shape as Rng,
and enter the array formula

=MMULT(Rng,--(COLUMN(Rng)+TRANSPOSE(COLUMN(Rng))=COLUMNS(Rng)+1))

Alternatively, if the top-left result cell were named TLRC, enter the following
formula in it.

=OFFSET(Rng,ROW()-ROW(TLRC),COLUMNS(Rng)-COLUMN()+COLUMN(TLRC)-1,1,1)

Then fill right and fill down. This works for anything other than blank cells in
Rng.
 
Back
Top